Infolinks

Thursday 21 June 2012

XXFTC_OM_ORDER_RLS_HOLDS

create or replace
PACKAGE XXFTC_OM_ORDER_RLS_HOLDS AS
/************************************************************************************************/
/***     FILE NAME    :           XXFTC_OM_ORDER_HOLDS.pks                                  ***/
/***     AUTHOR         :           Prasad Potluri                                            ***/
/***     VERSION        :           1.0                                                       ***/
/***     PURPOSE        :           procedure to apply  hold for order                        ***/
/***                                                                              ***/
/***     PARAMETERS     :           order header id                                           ***/
/***     DATE           :           03/June/2009                                              ***/
/**************************************************************************************************/

PROCEDURE XXFTC_ORDER_RLS_HOLD (
            itemtype  in varchar2,
            itemkey   in varchar2,
            actid     in number,
            funcmode  in varchar2,
            resultout in out varchar2
            );

PROCEDURE XXFTC_OM_APL_HLD(p_hold_id  IN NUMBER,
                             p_header_id IN NUMBER,
                 p_line_id   IN NUMBER);



END XXFTC_OM_ORDER_RLS_HOLDS;
/
create or replace
PACKAGE BODY      XXFTC_OM_ORDER_RLS_HOLDS AS
/************************************************************************************************/
/***     FILE NAME    :           XXFTC_OM_ORDER_HOLDS.pkb                                  ***/
/***     AUTHOR         :           Prasad Potluri                                            ***/
/***     VERSION        :           1.0                                                       ***/
/***     PURPOSE        :           procedure to apply  hold for an order                     ***/
/***                                                                              ***/
/***     PARAMETERS     :           order header id                                           ***/
/***     DATE           :           03/June/2009                                              ***/
/**************************************************************************************************/



--+----------------------------------------------------------+
--+ Function Used to get the weight for the open order line  +
--+----------------------------------------------------------+

FUNCTION open_orders_rls_weight (p_line_id IN NUMBER) RETURN NUMBER IS
        v_open_orders_weight NUMBER;
    BEGIN
          SELECT NVL(SUM(mtr.reservation_quantity*nvl(mtl.UNIT_WEIGHT,0)),0) INTO v_open_orders_weight
                    FROM oe_order_lines oel,mtl_system_items_b mtl,mtl_reservations mtr
                   WHERE oel.inventory_item_id=mtl.inventory_item_id
                 AND oel.FLOW_STATUS_CODE not in ('CANCELLED', 'SHIPPED','CLOSED','AWAITING_RETURN','AWAITING_FULFILLMENT', 'AWAITING_RECEIPT')
                 AND mtl.organization_id=oel.SHIP_FROM_ORG_ID
                 AND mtl.organization_id=mtr.organization_id
                 AND mtl.inventory_item_id=mtr.inventory_item_id
                 AND mtr.demand_source_line_id=oel.line_id
                 AND oel.line_id=p_line_id;

        RETURN v_open_orders_weight;
END open_orders_rls_weight;

--+----------------------------------------------------------+
--+ Main Procedure used to apply the hold for an order       +
--+----------------------------------------------------------+


PROCEDURE XXFTC_ORDER_RLS_HOLD (
            itemtype  in varchar2,
            itemkey   in varchar2,
            actid     in number,
            funcmode  in varchar2,
            resultout in out varchar2
            )
IS

l_truckload_limit               VARCHAR2(100);
l_freight                       VARCHAR2(100);
l_ship_method                   VARCHAR2(100);
l_cust_id                       NUMBER;
L_ORDER_HEADER_ID               NUMBER;
l_ship_to_id                    NUMBER;
l_warehouse                     NUMBER;
l_total_weight                  NUMBER;
l_line_weight                   NUMBER;
l_oe_hold_id                    NUMBER;
l_hold_id                       NUMBER;
l_msg_count                     NUMBER;
l_freight_header_id             NUMBER;
L_ORG                           number :=fnd_profile.value('org_id');
l_msg_data                      VARCHAR2(2000);
l_return_status                 VARCHAR2(50);
l_debug_point                   VARCHAR2(100);
l_error_message                 VARCHAR2(2000);
l_program_name                  VARCHAR2(256);
cp_application_id               NUMBER;
l_application_id                NUMBER;
l_alert_id                      NUMBER;
l_type                          VARCHAR2(3);
l_err_msg                       VARCHAR2(200);
p_request_id                    NUMBER;
l_party_id                      NUMBER;
l_release_hold_flag             VARCHAR2(1);
L_CAN_LINE_ID                   NUMBER;
L_STATUS_CODE                   VARCHAR2(50);
l_party_name                    VARCHAR2(200);
l_customer_number               VARCHAR2(30);
l_customer_location             VARCHAR2(200);
l_warehouse_code                VARCHAR2(50);
l_order_weight                  NUMBER;
l_request_id                    NUMBER;
l_phase1                        VARCHAR2 (200);
l_status1                       VARCHAR2 (200);
l_dev_phase1                    VARCHAR2 (200);
l_dev_status1                   VARCHAR2 (200);
l_result1                       BOOLEAN;
l_error_mesg1                   VARCHAR2 (2000);
l_request_id1                    NUMBER;
l_phase2                        VARCHAR2 (200);
l_status2                       VARCHAR2 (200);
l_dev_phase2                    VARCHAR2 (200);
l_dev_status2                   VARCHAR2 (200);
l_result2                       BOOLEAN;
l_error_mesg2                   VARCHAR2 (2000);
l_request_date                varchar2(30);
l_current_order                 NUMBER;
l_cancel_reason                 VARCHAR2(1000);
l_book_user                     NUMBER;
l_user_email                    VARCHAR2(500);
l_send_email                    VARCHAR2(1000);
l_ship_thresold                 varchar2(200);
l_update_user_name              varchar2(100);
l_instance_name                 varchar2(100);
l_notes                         varchar2(1000);
l_report_type                   varchar2(50);
l_can_line_num                  number;
l_list_price                    number;
l_selling_price                 number;
l_diff                          number;
l_percentage                    number;
l_request1                      BOOLEAN;
v_template                      varchar2(30);
l_CANCELLED_FLAG                varchar2(10);
l_crd_oe_hold_id                number;
l_crd_oe_hold_sts               varchar2(100);
v_date                          varchar2(20):=to_char(sysdate,'MMDDYY');
l_ORDERED_QUANTITY              NUMBER;
l_reservation_quantity          NUMBER;
l_shipping_instructions         VARCHAR2(1000);

 l_result                         BOOLEAN;
 l_phase                          VARCHAR2(200);
 l_status                         VARCHAR2(200);
 l_dev_phase                      VARCHAR2(200);
 l_dev_status                     VARCHAR2(200);
 l_error_mesg                     VARCHAR2(2000);
 l_cancel_header_id               number;
 l_line_count                     number;
 l_item_number                    varchar2(100);
 l_line_number                    varchar2(50);
 l_cust_item_number               VARCHAR2(200);

l_order_tbl            Oe_Holds_Pvt.order_tbl_type;
l_hold_source_rec               OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE;

--+-----------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open order lines against current order customer,ship to location and warehouse        +
--+-----------------------------------------------------------------------------------------------------------------------+

CURSOR c_eligible_lines(p_sold_to_org_id IN NUMBER,p_ship_to_org_id IN NUMBER,p_ship_from_org_id IN NUMBER) IS
SELECT DISTINCT oel.line_id
  FROM oe_order_lines_all oel,oe_order_headers_all oeh,fnd_lookup_values fl,WSH_CARRIER_SERVICES wc
  WHERE oel.SOLD_TO_ORG_ID=p_sold_to_org_id
    AND oel.SHIP_TO_ORG_ID=p_ship_to_org_id
    AND oel.SHIP_FROM_ORG_ID=p_ship_from_org_id
    AND oeh.header_id=oel.header_id
    AND oel.FLOW_STATUS_CODE not in ('CANCELLED', 'SHIPPED','CLOSED','AWAITING_RETURN','AWAITING_FULFILLMENT', 'AWAITING_RECEIPT')
    AND fl.LOOKUP_TYPE='FREIGHT_TERMS'
    AND fl.LOOKUP_CODE=oeh.FREIGHT_TERMS_CODE
    AND  wc.SHIP_METHOD_CODE=oeh.SHIPPING_METHOD_CODE
    AND fl.attribute1||'-'||wc.attribute3 not in('Y-Y','Y-N','N-Y');




--+----------------------------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open orders to apply holds against current order customer,ship to location and warehouse   +
--+----------------------------------------------------------------------------------------------------------------------------+


CURSOR c_hold_order(p_sold_to_org_id IN NUMBER,p_ship_to_org_id IN NUMBER,p_ship_from_org_id IN NUMBER) IS
SELECT DISTINCT oeh.header_id,oeh.order_number,oel.line_id,oel.line_number
  FROM oe_order_lines_all oel,oe_order_headers_all oeh,fnd_lookup_values fl,WSH_CARRIER_SERVICES wc
  WHERE oel.SOLD_TO_ORG_ID=p_sold_to_org_id
    AND oel.SHIP_TO_ORG_ID=p_ship_to_org_id
    AND oel.SHIP_FROM_ORG_ID=p_ship_from_org_id
    AND oeh.header_id=oel.header_id
    AND oel.FLOW_STATUS_CODE not in ('CANCELLED', 'SHIPPED','CLOSED','AWAITING_RETURN','AWAITING_FULFILLMENT', 'AWAITING_RECEIPT')
    AND fl.LOOKUP_TYPE='FREIGHT_TERMS'
    AND fl.LOOKUP_CODE=oeh.FREIGHT_TERMS_CODE
    AND  wc.SHIP_METHOD_CODE=oeh.SHIPPING_METHOD_CODE
    AND fl.attribute1||'-'||wc.attribute3 not in('Y-Y','Y-N','N-Y');


BEGIN



     --+---------------------------+
     --+ Set the org id            +
     --+---------------------------+

      MO_GLOBAL.set_policy_context('S', L_ORG);






      l_debug_point:='010';
      l_error_message:='';

      l_truckload_limit:='';
      l_freight_header_id:=null;


      l_can_line_id:=to_number(itemkey);











    BEGIN
      SELECT flow_status_code,line_number
        INTO l_status_code,l_can_line_num
    FROM oe_order_lines
    WHERE line_id=l_can_line_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
           l_status_code:='';

       l_error_message:='l_truckload_limit is null for an order header id';
       WHEN OTHERS THEN


        l_error_message:='others error for status_code'||SUBSTR(SQLERRM,1,100);
      END;


IF l_status_code='CANCELLED' THEN




        --+-----------------------------------------------------------------------------+
                --+ Get the Customer id,ship to location is and warehouse against current order +                     +
                --+-----------------------------------------------------------------------------+



                      BEGIN
                        SELECT SOLD_TO_ORG_ID,SHIP_TO_ORG_ID,SHIP_FROM_ORG_ID
              INTO l_cust_id,l_ship_to_id,l_warehouse
                          FROM oe_order_lines
                         WHERE line_id=l_can_line_id;
                      EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                             l_cust_id:=null;
                    l_error_message:='l_cust_id is null for an order header id';

                       WHEN OTHERS THEN
                            l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);

                       END;

               --+-----------------------------------------------------------------------------+
          --+ Get the party id                                                            +
          --+-----------------------------------------------------------------------------+
            BEGIN
               SELECT hp.party_id
                 INTO l_party_id
                 FROM hz_parties hp, hz_cust_accounts hc
                WHERE hc.cust_account_id = l_cust_id
                  AND hc.party_id = hp.party_id;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  l_party_id := NULL;
                  l_error_message :=
                        'l_party_id is null for an order header id'
                     || l_order_header_id;
               WHEN OTHERS
               THEN
                  l_error_message :=
                        'others error for order l_party_id'
                     || l_order_header_id
                     || SUBSTR (SQLERRM, 1, 100);
            END;


          BEGIN
                  SELECT attribute6
                    INTO l_ship_thresold
                    FROM hz_cust_site_uses_all
                   WHERE site_use_id = l_ship_to_id
             AND site_use_code='SHIP_TO';
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     l_ship_thresold := '';
                     l_error_message :=
                           'l_release_hold_flag is null for an order header id'
                        || l_order_header_id;
                  WHEN OTHERS
                  THEN
                     l_error_message :=
                           'others error for order l_release_hold_flag'
                        || l_order_header_id
                        || SUBSTR (SQLERRM, 1, 100);
               END;

           IF l_ship_thresold IS NOT NULL  THEN

                  l_truckload_limit:=l_ship_thresold;

               ELSE



               BEGIN
                  SELECT attribute2
                    INTO l_truckload_limit
                    FROM hz_parties
                   WHERE party_id = l_party_id;
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     l_truckload_limit := '';
                     l_error_message :=
                           'l_release_hold_flag is null for an order header id'
                        || l_order_header_id;
                  WHEN OTHERS
                  THEN
                     l_error_message :=
                           'others error for order l_release_hold_flag'
                        || l_order_header_id
                        || SUBSTR (SQLERRM, 1, 100);
               END;

           END IF;   -- IF l_ship_thresold IS NOT NULL  THEN











--+---------------------------------------------------------------------+
--+ Check the truckload weight limit IS NULL OR NOT NULL                +
--+ IF truckload weight limit IS NOT NULL then only proceed to further. +
--+---------------------------------------------------------------------+

  IF l_truckload_limit IS NOT NULL THEN

  l_debug_point:='020';





      --+---------------------------------------------------------------------+
      --+ Get the order header id to check freight/ship method combination    +
      --+---------------------------------------------------------------------+
     BEGIN
       SELECT oeh.header_id
         INTO l_freight_header_id
         FROM oe_order_headers oeh,fnd_lookup_values fl,WSH_CARRIER_SERVICES wc,oe_order_lines oel
        WHERE fl.LOOKUP_TYPE='FREIGHT_TERMS'
          AND fl.LOOKUP_CODE=oeh.FREIGHT_TERMS_CODE
          AND  wc.SHIP_METHOD_CODE=oeh.SHIPPING_METHOD_CODE
          AND fl.attribute1||'-'||wc.attribute3 not in('Y-Y','Y-N','N-Y')
      AND oeh.header_id=oel.header_id
      AND oel.line_id=l_can_line_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
                    l_freight_header_id:=null;

             l_error_message:='l_freight_header_id is null for an order header id';

               WHEN OTHERS THEN
                     l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);
       END;





              l_debug_point:='030';



         --+-----------------------------------------------------------------------------------------------------------+
             --+Check the freight term - ship method combination                                                           +
         --+IF freight term - ship method combination                                                                  +
         --+doesn't exist in('Parcel-Prepay  Add','Parcel-Third Party Billing','Parcel-Prepaid','Will Call-Prepaid'   +
         --+then proceed to further.                                                                                   +
             --+-----------------------------------------------------------------------------------------------------------+

           IF l_freight_header_id IS NOT NULL THEN



                l_debug_point:='040';





        --+-----------------------------------------------------------------------------+
                --+ Get the Current cancel order number                                         +
                --+-----------------------------------------------------------------------------+



               BEGIN
                        SELECT order_number,last_updated_by
              INTO l_current_order,l_book_user
                          FROM oe_order_headers
                         WHERE header_id=l_freight_header_id;
                      EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                             l_current_order:=null;
                             l_book_user:=null;
                    l_error_message:='l_cust_id is null for an order header id';

                       WHEN OTHERS THEN
                            l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);

                       END;




        --+-----------------------------------------------------------------------------+
                --+ Get the email address of booking order                                      +
                --+-----------------------------------------------------------------------------+



               BEGIN
                        SELECT email_address
              INTO l_user_email
                          FROM fnd_user
                         WHERE user_id=l_book_user;
                      EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                             l_user_email:='';
                    l_error_message:='l_cust_id is null for an order header id';

                       WHEN OTHERS THEN
                            l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);

                       END;


               IF l_user_email IS NOT NULL THEN




               l_send_email:=l_user_email;

               ELSE

                         -- l_send_email:='mmanier@falkentire.com';
                           NULL;



                       END IF;







               --+-----------------------------------------------------------------------------+
                       --+ Open the loop to get the eligible order lines                               +
                       --+-----------------------------------------------------------------------------+

                      l_total_weight:=0;


                      FOR r_eligible_lines IN c_eligible_lines(l_cust_id,l_ship_to_id,l_warehouse)
              LOOP

                  l_debug_point:='050';


               l_line_weight:=open_orders_rls_weight(r_eligible_lines.line_id);

               --+-----------------------------------------------------------------------------+
                       --+ Sum up the weight for all eligible open order lines                         +
                       --+-----------------------------------------------------------------------------+

                           l_total_weight:=l_total_weight+l_line_weight;

                      END LOOP;


               --+-----------------------------------------------------------------------------+
                       --+ Check the total weight reduced to truck load weight or not                  +
                       --+-----------------------------------------------------------------------------+







                   IF l_total_weight<l_truckload_limit THEN

           IF l_total_weight >0 THEN

           l_debug_point:='0100';

               --+-----------------------------------------------------------------------------+
                       --+ Open the loop to get the all eligible open orders                           +
                       --+-----------------------------------------------------------------------------+

           /*    FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)
               LOOP





                    --+-----------------------------------------------------------------------------+
                            --+ Get the hold id for an order                                                +
                            --+-----------------------------------------------------------------------------+

                            l_debug_point:='0110';

                 BEGIN
                SELECT order_hold_id
                  INTO l_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = r_hold_order.header_id
                   AND ohl.line_id=r_hold_order.line_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Trailer Load Line Hold'
                   AND ohl.released_flag = 'N';
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_oe_hold_id:=null;
                      l_error_message:='l_oe_hold_id is null for an order header id'||l_order_header_id;


                                  WHEN OTHERS THEN
                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                             END;

                --+-----------------------------------------------------------------------------+
                            --+ Check the hold is there or not for eligible order                           +
                            --+-----------------------------------------------------------------------------+

                 IF l_oe_hold_id IS NULL THEN

                 l_debug_point:='0120';



                    BEGIN
                     SELECT hold_id
                                   INTO l_hold_id
                                   FROM OE_HOLD_DEFINITIONS
                                  WHERE NAME = 'Trailer Load Line Hold';
                                EXCEPTION
                        WHEN OTHERS THEN
                              l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                            END;


                 BEGIN
                UPDATE oe_order_headers
                  SET attribute10='',
                      last_updated_by=fnd_profile.value('user_id'),
                      last_update_date=sysdate
                  WHERE header_id=l_freight_header_id;
                EXCEPTION
                     WHEN OTHERS THEN
                    l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);
                       END;

                 --+-----------------------------------------------------------------------------+
                                --+ Call the API 'Oe_Holds_Pub.apply_holds' to apply hold to order              +
                                --+-----------------------------------------------------------------------------+


                                XXFTC_OM_APL_HLD(p_hold_id  =>l_hold_id,
                                                  p_header_id =>r_hold_order.header_id,
                          p_line_id    => r_hold_order.line_id);

               END IF;  --IF l_oe_hold_id IS NULL THEN


                      END LOOP;  ----FOR r_hold_order IN c_hold_order(l_cust_id,l_ship_to_id,l_warehouse)


*/



              BEGIN
                    SELECT hp.party_name, hc.account_number,
                               hu.LOCATION, org.organization_code,oel.unit_list_price,oel.unit_selling_price
                          INTO l_party_name, l_customer_number,
                               l_customer_location, l_warehouse_code,l_list_price,l_selling_price
                          FROM hz_parties hp,
                               hz_cust_accounts hc,
                               oe_order_lines oel,
                               hz_cust_acct_sites hzc,
                               hz_cust_site_uses hu,
                               org_organization_definitions org
                         WHERE hc.party_id = hp.party_id
                           AND hc.cust_account_id = oel.sold_to_org_id
                           AND oel.line_id = l_can_line_id
                           AND hc.cust_account_id = hzc.cust_account_id
                           AND hzc.cust_acct_site_id = hu.cust_acct_site_id
                           AND hu.site_use_id = oel.ship_to_org_id
                           AND hu.site_use_code = 'SHIP_TO'
                           AND org.organization_id = oel.ship_from_org_id;
                        EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                             l_party_name:='';
                    l_error_message:='l_party_id is null for an order header id';


                       WHEN OTHERS THEN
                            l_error_message:='others error for order l_party_id'||SUBSTR(SQLERRM,1,100);


                       END;


                         BEGIN
                     SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
                          0)
            INTO l_order_weight
            FROM oe_order_lines oel, mtl_system_items_b mtl, mtl_reservations mtr
               WHERE oel.inventory_item_id = mtl.inventory_item_id
             AND oel.flow_status_code NOT IN
                ('CANCELLED',
                 'SHIPPED',
                 'CLOSED',
                 'AWAITING_RETURN',
                 'AWAITING_FULFILLMENT',
                 'AWAITING_RECEIPT'
                )
             AND mtl.organization_id = oel.ship_from_org_id
             AND mtl.organization_id = mtr.organization_id
             AND mtl.inventory_item_id = mtr.inventory_item_id
             AND mtr.demand_source_line_id = oel.line_id
             AND oel.line_id = l_can_line_id;
                    EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_order_weight := null;

                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;


              BEGIN
                  SELECT reason
                    INTO l_cancel_reason
                    FROM oe_reasons_v
                   WHERE entity_id=l_can_line_id
                     AND entity_code='LINE';
                          EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                             l_cancel_reason:='';
                     l_error_message:='l_party_id is null for an order header id';


                           WHEN OTHERS THEN
                             l_error_message:='others error for order l_party_id'||SUBSTR(SQLERRM,1,100);


                          END;

              BEGIN
                        SELECT fnd.user_name,oeh.attribute3
                          INTO l_update_user_name,l_notes
                          FROM oe_order_headers oeh,
                               fnd_user fnd
                         WHERE oeh.header_id=l_freight_header_id
               AND oeh.last_updated_by=fnd.user_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_update_user_name := '';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;


        --+-----------------------------------------------------+
               --+ get the credit check hold id for trailer load hold  +
               --+-----------------------------------------------------+

                            BEGIN
                SELECT order_hold_id
                  INTO l_crd_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = l_freight_header_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Credit Check Failure'
                   AND ohl.released_flag = 'N'
                   AND rownum=1;
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_crd_oe_hold_id:=null;

                      l_error_message:='l_oe_hold_id is null for an order header id';


                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;



                   BEGIN
                           SELECT shipping_instructions
                             INTO l_shipping_instructions
                             FROM oe_order_headers_all
                            WHERE header_id = l_freight_header_id;
                         EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                              l_shipping_instructions := '';

                              l_error_message :=
                                    'l_shipping_instructions is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;






            BEGIN
                           SELECT nvl(mtr.reservation_quantity,0)
                            INTO l_reservation_quantity
                             FROM oe_order_lines_all oel,
                                  mtl_system_items_b mtl,
                                  mtl_reservations mtr
                            WHERE oel.inventory_item_id = mtl.inventory_item_id
                              AND mtl.organization_id = oel.ship_from_org_id
                              AND mtl.organization_id = mtr.organization_id
                              AND mtl.inventory_item_id = mtr.inventory_item_id
                              AND mtr.demand_source_line_id = oel.line_id
                              AND oel.line_id = l_can_line_id;
                         EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_reservation_quantity:=0;
                                                           l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;


             BEGIN
                        SELECT nvl(ORDERED_QUANTITY,0)
                          INTO  l_ORDERED_QUANTITY
                          FROM oe_order_lines_all
                          WHERE line_id= l_can_line_id;
                      EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_ORDERED_QUANTITY:=0;
                                 l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;




                   IF l_crd_oe_hold_id IS NOT NULL THEN

                                  l_crd_oe_hold_sts:='On Credit Check Hold';

                               ELSE

                                  l_crd_oe_hold_sts:='No Credit Check Hold';

                               END IF;


             l_diff:=l_list_price-l_selling_price;


                 l_percentage:=l_diff/l_list_price*100;


              BEGIN
                        SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
              INTO l_item_number,l_line_number
                          FROM oe_order_lines_all oel,mtl_system_items_b mtl
                         WHERE oel.inventory_item_id = mtl.inventory_item_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND oel.line_id=l_can_line_id;
                EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_item_number := '';
                           l_error_message :=
                                 'l_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_item_number'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                       END;


                BEGIN
                        SELECT mci.customer_item_number
              INTO l_cust_item_number
              FROM oe_order_lines_all oel,
                   mtl_customer_item_xrefs_v mci,
                   mtl_system_items_b mtl
             WHERE mci.customer_id=oel.sold_to_org_id
               AND oel.line_id=l_can_line_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND mtl.segment1=mci.concatenated_segments
               AND mtl.inventory_item_id=oel.inventory_item_id;
             EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_cust_item_number :='';
                           l_error_message :=
                                 'l_cust_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;











               --+-----------------------------------------------------------------------------+
                           --+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert        +
                           --+-----------------------------------------------------------------------------+

                            BEGIN
                 INSERT
                   INTO xxftc_order_header_hold_tbl
                 VALUES(l_freight_header_id,to_char(l_current_order),l_line_number,l_freight_header_id,
                        l_party_name,
                    l_customer_number,
                    l_customer_location,
                    l_truckload_limit,
                                    l_order_weight,
                                    l_total_weight,
                                    l_warehouse_code,
                    l_list_price,l_selling_price,l_diff,round(l_percentage,2),
                    '',
                                    l_update_user_name,
                    l_notes,'',l_crd_oe_hold_sts,
                                    l_item_number,
                    l_ORDERED_QUANTITY,
                    l_reservation_quantity,
                    l_shipping_instructions,
                    l_cust_item_number
                 );
                           EXCEPTION
                  WHEN OTHERS THEN
                        l_error_message:='others error for inserting into xxftc_order_header_hold_tbl'||SUBSTR(SQLERRM,1,100);


                           END;


               BEGIN
                   SELECT instance_name
                 INTO l_instance_name
                 FROM v$instance
                 WHERE rownum=1;
                 EXCEPTION
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order header id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                           END;



       BEGIN
       l_report_type:='CANCEL';

       v_template:='XXFTCALRTMAIL';


                   l_request1 := fnd_request.add_layout (template_appl_name => 'XXFTC',
            template_code => v_template,
            template_language => 'en',
             template_territory => 'US',
            output_format => 'EXCEL');


              l_request_id :=   fnd_request.submit_request
                           (application      => 'XXFTC',
                            program          => 'XXFTCALRTMAIL',
                            description      => NULL,
                                start_time       => SYSDATE,
                                sub_request      => FALSE,
                                argument1        => l_freight_header_id,
                            argument2        => l_report_type);


                              COMMIT;


            l_result := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id
                            ,30
                            ,0
                            ,l_phase
                            ,l_status
                            ,l_dev_phase
                            ,l_dev_status
                            ,l_error_mesg);
            COMMIT;




               --+------------------------------------------------------------+
                           --+submit the request to send the mail through shell program   +
                           --+------------------------------------------------------------+


                          /*   SELECT to_char(sysdate+3/60/24,'DD-MON-YYYY HH24:MI:SS') INTO l_request_date FROM dual;*/





                l_request_id1 := FND_REQUEST.SUBMIT_REQUEST(APPLICATION =>  'XXFTC'
                              ,PROGRAM     => 'XXFTCXMLMAIL'
                          ,DESCRIPTION => NULL
                          ,START_TIME  => SYSDATE
                          ,SUB_REQUEST => FALSE
                          ,ARGUMENT1   => l_request_id
                          ,ARGUMENT2   => l_send_email
                              ,ARGUMENT3   => 'Threshold Limit Reduced due to Cancel an order#'||l_current_order||'-'||'With Cancel Reason :'||l_cancel_reason||'-'||l_instance_name
                          ,ARGUMENT4   => 'RELEASE_TLD_HOLD'||v_date
                          );
                          COMMIT;
                    END;





         END IF;    --IF l_total_weight >0 THEN


                      END IF;   --IF l_total_weight<l_truckload_limit THEN




                   END IF;   --- IF l_freight_header_id IS NOT NULL THEN


               END IF;  --IF l_truckload_limit IS NOT NULL THEN

     END IF;   --IF l_status_code='CANCELLED' THEN






           resultout := wf_engine.eng_completed;


EXCEPTION
WHEN OTHERS THEN
     l_error_message:='Other error in XXFTC_ORDER_APPLY_HOLD'||substr(sqlerrm,1,50);

END XXFTC_ORDER_RLS_HOLD;

PROCEDURE XXFTC_OM_APL_HLD(p_hold_id IN NUMBER, p_header_id IN NUMBER,p_line_id IN NUMBER)
   IS
          l_msg_data          VARCHAR2 (2000);
          l_return_status     VARCHAR2 (50);
          l_msg_count         NUMBER;
          l_hold_source_rec   oe_holds_pvt.hold_source_rec_type;
          l_debug_point       VARCHAR2 (100);
          l_error_message     VARCHAR2 (2000);
          l_attr              VARCHAR2(10);
          PRAGMA AUTONOMOUS_TRANSACTION;
       BEGIN



          l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
          l_hold_source_rec.hold_id := p_hold_id;                      -- hold_id
          l_hold_source_rec.hold_entity_code := 'O';          -- order level hold
          l_hold_source_rec.hold_entity_id := p_header_id;
                                -- header_id of the order
          l_hold_source_rec.header_id := p_header_id;   -- header_id of the order
          l_hold_source_rec.line_id := p_line_id;
          l_return_status := NULL;
          l_msg_data := NULL;
          l_msg_count := NULL;
          l_debug_point := '090';
         oe_holds_pub.apply_holds (p_api_version          => 1.0,
                    p_init_msg_list        => fnd_api.g_true,
                    p_commit               => fnd_api.g_false,
                    p_hold_source_rec      => l_hold_source_rec,
                    x_return_status        => l_return_status,
                    x_msg_count            => l_msg_count,
                    x_msg_data             => l_msg_data
                       );
          DBMS_OUTPUT.put_line (l_return_status);

          IF l_return_status <> fnd_api.g_ret_sts_success
          THEN
         l_error_message :=
               'error for order header id while calling holds API'
            || p_header_id
            || l_msg_data;
          END IF;          -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
               COMMIT;
EXCEPTION
WHEN OTHERS THEN
     l_error_message:='Other error in XXFTC_OM_APPLY_HLD'||substr(sqlerrm,1,50);


END XXFTC_OM_APL_HLD;


END XXFTC_OM_ORDER_RLS_HOLDS;
/

No comments:

Post a Comment