CREATE OR REPLACE PACKAGE BODY xxbe_so_interface_pkg AS
-- Defining global variables
   x_org_id         NUMBER;         -- := apps.fnd_profile.VALUE ('ORG_ID');
   x_user_id        NUMBER;         -- := apps.fnd_profile.VALUE ('USER_ID');
   x_resp_id        NUMBER;
   x_resp_appl_id   NUMBER;

   PROCEDURE process_order (
      x_message_id   IN       VARCHAR2,
      x_error_flag   IN OUT   VARCHAR2,       -- Returns 'Y' if there is error
      x_error_msg    IN OUT   VARCHAR2        -- Returns error message
   )
   IS
      l_index                        NUMBER   := 0;
      l_debug_file                   VARCHAR2 (200);
      v_header_rec                   oe_order_pub.header_rec_type;
      v_line_tbl                     oe_order_pub.line_tbl_type;
      v_action_request_tbl           oe_order_pub.request_tbl_type;
      l_inv_item_id                  mtl_system_items_b.inventory_item_id%TYPE;
      l_ord_typ_id                   oe_transaction_types.transaction_type_id%TYPE;
      l_price_list_id                qp_list_headers_tl.list_header_id%TYPE;
      l_cust_account_id              hz_cust_accounts.cust_account_id%TYPE;
      l_billto_site_use_id           hz_cust_site_uses_all.site_use_id%TYPE;
      l_shipto_site_use_id           hz_cust_site_uses_all.site_use_id%TYPE;
      l_salesrep_id                  jtf_rs_salesreps.salesrep_id%TYPE;
      l_warehouse_id                 mtl_parameters.organization_id%TYPE;
      x_uom_code		     varchar2(10);
      l_ord_hdr_exists               VARCHAR2 (10);
      l_ord_line_exists              VARCHAR2 (10);
      l_hdr_operation_code           VARCHAR2 (100);
      l_line_operation_code          VARCHAR2 (100);
      l_line_num                     NUMBER;
      l_count                        NUMBER;
	  l_line_id						 NUMBER;
--------------------------------------------------------------------------------

             l_header_rec oe_order_pub.Header_Rec_Type;
             l_line_tbl oe_order_pub.Line_Tbl_Type;
             l_action_request_tbl oe_order_pub.Request_Tbl_Type;
             l_header_adj_tbl oe_order_pub.Header_Adj_Tbl_Type;
             l_line_adj_tbl oe_order_pub.line_adj_tbl_Type;
             l_header_scr_tbl oe_order_pub.Header_Scredit_Tbl_Type;
             l_line_scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type;
             l_return_status varchar2(1000);
             l_msg_count number;
             l_msg_data varchar2(1000);
             p_api_version_number NUMBER :=1.0;
             p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
             p_return_values VARCHAR2(10) := FND_API.G_FALSE;
             p_action_commit VARCHAR2(10) := FND_API.G_FALSE;
             x_return_status VARCHAR2(1);
             x_msg_count NUMBER;
             x_msg_data VARCHAR2(100);
             p_header_rec oe_order_pub.Header_Rec_Type := oe_order_pub.G_MISS_HEADER_REC;
             p_old_header_rec oe_order_pub.Header_Rec_Type := oe_order_pub.G_MISS_HEADER_REC;
             p_header_val_rec oe_order_pub.Header_Val_Rec_Type := oe_order_pub.G_MISS_HEADER_VAL_REC;
             p_old_header_val_rec oe_order_pub.Header_Val_Rec_Type := oe_order_pub.G_MISS_HEADER_VAL_REC;
             p_Header_Adj_tbl oe_order_pub.Header_Adj_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_TBL;
             p_old_Header_Adj_tbl oe_order_pub.Header_Adj_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_TBL;
             p_Header_Adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_VAL_TBL;
             p_old_Header_Adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_VAL_TBL;
             p_Header_price_Att_tbl oe_order_pub.Header_Price_Att_Tbl_Type := oe_order_pub.G_MISS_HEADER_PRICE_ATT_TBL;
             p_old_Header_Price_Att_tbl oe_order_pub.Header_Price_Att_Tbl_Type := oe_order_pub.G_MISS_HEADER_PRICE_ATT_TBL;
             p_Header_Adj_Att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_ATT_TBL;
             p_old_Header_Adj_Att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_ATT_TBL;
             p_Header_Adj_Assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_ASSOC_TBL;
             p_old_Header_Adj_Assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type := oe_order_pub.G_MISS_HEADER_ADJ_ASSOC_TBL;
             p_Header_Scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type := oe_order_pub.G_MISS_HEADER_SCREDIT_TBL;
             p_old_Header_Scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type := oe_order_pub.G_MISS_HEADER_SCREDIT_TBL;
             p_Header_Scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type :=oe_order_pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
             p_old_Header_Scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type := oe_order_pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
             p_line_tbl oe_order_pub.Line_Tbl_Type := oe_order_pub.G_MISS_LINE_TBL;
             p_old_line_tbl oe_order_pub.Line_Tbl_Type := oe_order_pub.G_MISS_LINE_TBL;
             p_line_val_tbl oe_order_pub.Line_Val_Tbl_Type := oe_order_pub.G_MISS_LINE_VAL_TBL;
             p_old_line_val_tbl oe_order_pub.Line_Val_Tbl_Type
             := oe_order_pub.G_MISS_LINE_VAL_TBL;
             p_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_TBL;
             p_old_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_TBL;
             p_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_VAL_TBL;
             p_old_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_VAL_TBL;
             p_Line_price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type
             := oe_order_pub.G_MISS_LINE_PRICE_ATT_TBL;
             p_old_Line_Price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type
             := oe_order_pub.G_MISS_LINE_PRICE_ATT_TBL;
             p_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_ATT_TBL;
             p_old_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_ATT_TBL;
             p_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_ASSOC_TBL;
             p_old_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type
             := oe_order_pub.G_MISS_LINE_ADJ_ASSOC_TBL;
             p_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type
             := oe_order_pub.G_MISS_LINE_SCREDIT_TBL;
             p_old_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type
             := oe_order_pub.G_MISS_LINE_SCREDIT_TBL;
             p_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type
             := oe_order_pub.G_MISS_LINE_SCREDIT_VAL_TBL;
             p_old_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type
             := oe_order_pub.G_MISS_LINE_SCREDIT_VAL_TBL;
             p_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type
             := oe_order_pub.G_MISS_LOT_SERIAL_TBL;
             p_old_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type
             := oe_order_pub.G_MISS_LOT_SERIAL_TBL;
             p_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type
             := oe_order_pub.G_MISS_LOT_SERIAL_VAL_TBL;
             p_old_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type
             := oe_order_pub.G_MISS_LOT_SERIAL_VAL_TBL;
             p_action_request_tbl oe_order_pub.Request_Tbl_Type
             := oe_order_pub.G_MISS_REQUEST_TBL;
             x_header_val_rec Oe_Order_Pub.Header_Val_Rec_Type;
             x_Header_Adj_tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
             x_Header_Adj_val_tbl Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
             x_Header_price_Att_tbl Oe_Order_Pub.Header_Price_Att_Tbl_Type;
             x_Header_Adj_Att_tbl Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
             x_Header_Adj_Assoc_tbl Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
             x_Header_Scredit_tbl Oe_Order_Pub.Header_Scredit_Tbl_Type;
             x_Header_Scredit_val_tbl Oe_Order_Pub.Header_Scredit_Val_Tbl_Type;
             x_line_val_tbl Oe_Order_Pub.Line_Val_Tbl_Type;
             x_Line_Adj_tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
             x_Line_Adj_val_tbl Oe_Order_Pub.Line_Adj_Val_Tbl_Type;
             x_Line_price_Att_tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type;
             x_Line_Adj_Att_tbl Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
             x_Line_Adj_Assoc_tbl Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
             x_Line_Scredit_tbl Oe_Order_Pub.Line_Scredit_Tbl_Type;
             x_Line_Scredit_val_tbl Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
             x_Lot_Serial_tbl Oe_Order_Pub.Lot_Serial_Tbl_Type;
             x_Lot_Serial_val_tbl Oe_Order_Pub.Lot_Serial_Val_Tbl_Type;
             x_action_request_tbl Oe_Order_Pub.Request_Tbl_Type;
             X_DEBUG_FILE varchar2(100);
             l_msg_index_out number(10);

-----------------------------------------------------------
      CURSOR order_hdr_cur
      IS
         SELECT DISTINCT message_id, order_reference, cust_reference,
                         ship_address_ref, bill_address_ref, order_type,
                         order_date, price_list_name, salesrep_name,
                         so_currency_code, cust_po_num, warehouse
                    FROM xxbe.xxbe_sales_order_interface
                   WHERE process_flag_yn = 'N' AND message_id = x_message_id;

      CURSOR order_line_cur (x_ord_ref IN VARCHAR2)
      IS
         SELECT order_reference, so_line_num, item_number, line_quantity,
                release_date, return_reason
           FROM xxbe.xxbe_sales_order_interface
          WHERE process_flag_yn = 'N' AND order_reference = x_ord_ref;
   BEGIN

   DBMS_OUTPUT.PUT_LINE('Starting the interface program');
      SAVEPOINT process_order_sp;

      -- Get Org_id.
      BEGIN
         SELECT organization_id
           INTO x_org_id
           FROM hr_operating_units
          WHERE NAME = 'Bare Escentuals Beauty Inc.'
            AND NVL (date_to, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
            AND ROWNUM < 2;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            x_error_flag := 'Y';
            x_error_msg := 'Organization to be defined in Oracle';
            RETURN;
         WHEN OTHERS
         THEN
            x_error_flag := 'Y';
            x_error_msg := 'Error: in fetching Organization Id';
            RETURN;
      END;

      APPS.fnd_client_info.set_org_context (x_org_id);        -- Initialize Org_id.

	  DBMS_OUTPUT.PUT_LINE('Intialized Ord_id');

      -- Get the User Id
      BEGIN
         SELECT user_id
           INTO x_user_id
           FROM apps.fnd_user
          WHERE user_name = 'INTERFACE';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            x_error_flag := 'Y';
            x_error_msg := 'INTERFACE User to be defined in Oracle';
            RETURN;
         WHEN OTHERS
         THEN
            x_error_flag := 'Y';
            x_error_msg := 'Error: in fetching INTERFACE User Id';
            RETURN;
      END;

      -- Get the Responsibility Id
      BEGIN
         SELECT responsibility_id, application_id
           INTO x_resp_id, x_resp_appl_id
           FROM apps.fnd_responsibility_tl
          WHERE responsibility_name = 'Order Management Super User';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            x_error_flag := 'Y';
            x_error_msg :=
                  x_error_msg
               || 'Order Management responsibility to be defined in Oracle';
            RETURN;
         WHEN OTHERS
         THEN
            x_error_flag := 'Y';
            x_error_msg :=
               x_error_msg
               || 'Error: in fetching Inventory responsibility Id';
            RETURN;
      END;

      --Setting FND global variables.
      apps.fnd_global.apps_initialize (user_id           => x_user_id,
                                       resp_id           => x_resp_id,
                                       resp_appl_id      => x_resp_appl_id
                                      );

	DBMS_OUTPUT.PUT_LINE('Initialized User_id and Responsibility_id');

      FOR hdr_cur_rec IN order_hdr_cur
      LOOP

	  DBMS_OUTPUT.PUT_LINE('Opened Header Loop');
         l_count := 0;
         check_order_hdr_exists (hdr_cur_rec.order_reference,
                                 l_ord_hdr_exists
                                );

         IF l_ord_hdr_exists = 'Y'
         THEN
            l_hdr_operation_code := oe_globals.g_opr_update;
         ELSIF l_ord_hdr_exists = 'N'
         THEN
            l_hdr_operation_code := oe_globals.g_opr_create;
         END IF;

		 DBMS_OUTPUT.PUT_LINE('Starting Validations for header');

             /*-------------------------------------
         -- Validating Order_Type_id
          ------------------------------------- */
         BEGIN
            SELECT transaction_type_id
              INTO l_ord_typ_id
              FROM oe_transaction_types_tl
             WHERE NAME = hdr_cur_rec.order_type AND ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Order Type is not defined for the Legacy Order Type'
                  || '='
                  || hdr_cur_rec.order_type;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Unable to get the Order Type for the Legacy Order Type'
                  || hdr_cur_rec.order_type;
         END;

/*-------------------------------------
 -- END Validating Order_Type_id
  ------------------------------------- */
DBMS_OUTPUT.PUT_LINE('Order Type id :'||l_ord_typ_id);
         /*-------------------------------------
         -- Validating Customer_id
         ------------------------------------- */
         BEGIN
            SELECT cust_account_id
              INTO l_cust_account_id
              FROM hz_cust_accounts
             WHERE orig_system_reference = hdr_cur_rec.cust_reference
               AND ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Customer is not defined for the Legacy customer                                    						reference'
                  || '='
                  || hdr_cur_rec.cust_reference;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Unable to get the Customer for the Legacy customer 			                                  reference'
                  || hdr_cur_rec.cust_reference;
         END;

/*----------------------------------------
 -- END Validating customer_id
 -----------------------------------------*/
DBMS_OUTPUT.PUT_LINE('Customer Id '|| l_cust_account_id);
         /*------------------------------------------
           -- Validating Customer Bill to Location_id
           ----------------------------------------- */
         BEGIN
            SELECT site_use_id
              INTO l_billto_site_use_id
              FROM hz_cust_site_uses_all hcsua,
                   hz_cust_acct_sites_all hcas,
                   hz_cust_accounts hca
             WHERE hcsua.LOCATION = hdr_cur_rec.bill_address_ref
               AND hcsua.site_use_code = 'BILL_TO'
			   AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
			   AND hcas.cust_account_id = hca.cust_account_id
               AND hca.orig_system_reference = hdr_cur_rec.cust_reference;

         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'BILL_TO site not found'
                  || '='
                  || hdr_cur_rec.bill_address_ref;
            WHEN TOO_MANY_ROWS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Duplicate BILL_TO Sites defined for '
                  || hdr_cur_rec.bill_address_ref;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Error finding the BILL_TO Sites for '
                  || hdr_cur_rec.bill_address_ref;
         END;

          /*------------------------------------------
         -- END Validating Customer Bill to Location_id
         ----------------------------------------- */
DBMS_OUTPUT.PUT_LINE('Customer Bill_to Id ' || l_billto_site_use_id);
         /*------------------------------------------
          -- Validating Customer Ship to Location_id
          ----------------------------------------- */
         BEGIN
            SELECT site_use_id
              INTO l_shipto_site_use_id
              FROM hz_cust_site_uses_all hcsua,
                   hz_cust_acct_sites_all hcas,
                   hz_cust_accounts hca
             WHERE hcsua.site_use_code = 'SHIP_TO'
AND hcsua.LOCATION = hdr_cur_rec.ship_address_ref
AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
and hca.cust_account_id = hca.ORIG_SYSTEM_REFERENCE
and hca.ORIG_SYSTEM_REFERENCE= hdr_cur_rec.cust_reference;

         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'SHIP_TO site not found'
                  || '='
                  || hdr_cur_rec.ship_address_ref;
            WHEN TOO_MANY_ROWS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Duplicate SHIP_TO Sites defined for '
                  || hdr_cur_rec.ship_address_ref;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Error finding the SHIP_TO Sites for '
                  || hdr_cur_rec.ship_address_ref;
         END;

         /*------------------------------------------
          -- END Validating Customer Ship to Location_id
          ----------------------------------------- */
DBMS_OUTPUT.PUT_LINE('Customer Ship_to '|| l_shipto_site_use_id);
         /*---------------------------------------
         -- Procedure to get price_list_id
         ----------------------------------------*/
         BEGIN
            SELECT qlht.list_header_id
              INTO l_price_list_id
              FROM qp_list_headers_tl qlht
             WHERE qlht.NAME = hdr_cur_rec.price_list_name;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Price List is not defined for the Legacy Price List 							                              Name'
                  || '='
                  || hdr_cur_rec.price_list_name;
            WHEN TOO_MANY_ROWS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Duplicate Price Lists defined for '
                  || hdr_cur_rec.price_list_name;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Unable to find Price list for '
                  || hdr_cur_rec.price_list_name;
         END;

/*---------------------------------------
 -- END Validating get price_list_id
 ----------------------------------------*/
DBMS_OUTPUT.PUT_LINE('Price List '|| l_price_list_id);
         /*---------------------------------------
         -- Validating sales_rep_id
         ----------------------------------------*/
         BEGIN
            SELECT salesrep_id
              INTO l_salesrep_id
              FROM jtf_rs_salesreps
             WHERE UPPER (NAME) = hdr_cur_rec.salesrep_name;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Sales Rep not defined in Oracle for '
                  || '='
                  || hdr_cur_rec.salesrep_name;
            WHEN TOO_MANY_ROWS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Too Many Sales Reps defined in Oracle for '
                  || '='
                  || hdr_cur_rec.salesrep_name;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Unable to find Sales Rep in Oracle for '
                  || '='
                  || hdr_cur_rec.salesrep_name;
         END;

/*---------------------------------------
 -- END Validating sales_rep_id
 ----------------------------------------*/
DBMS_OUTPUT.PUT_LINE('Salesrep Id'||l_salesrep_id);
         /*---------------------------------------
         -- Validating warehouse_id
         ----------------------------------------*/
         BEGIN
            SELECT organization_id
              INTO l_warehouse_id
              FROM mtl_parameters
             WHERE organization_code = hdr_cur_rec.warehouse -- confirm coloum
               AND ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Warehouse Not found In Oracle for '
                  || '='
                  || hdr_cur_rec.warehouse;
            WHEN OTHERS
            THEN
               x_error_flag := 'Y';
               x_error_msg :=
                     x_error_msg
                  || 'Unable to get the warehouse id for '
                  || hdr_cur_rec.warehouse;
         END;

/*---------------------------------------
 -- END Validating warehouse_id
 ----------------------------------------*/

 DBMS_OUTPUT.PUT_LINE('Warehouse Id '|| l_warehouse_id);

 DBMS_OUTPUT.PUT_LINE('Initializing Header record');

  l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

         l_header_rec.operation 		:= l_hdr_operation_code;
         l_header_rec.order_type_id 		:= l_ord_typ_id;
         l_header_rec.orig_sys_document_ref 	:= hdr_cur_rec.order_reference;
         l_header_rec.price_list_id 		:= l_price_list_id;
         l_header_rec.salesrep_id 		:= l_salesrep_id;
         l_header_rec.transactional_curr_code 	:= hdr_cur_rec.so_currency_code;
         l_header_rec.invoice_to_org_id 	:= l_billto_site_use_id;
         l_header_rec.ship_to_org_id 		:= l_shipto_site_use_id;
         l_header_rec.cust_po_number 		:= hdr_cur_rec.cust_po_num;
         l_header_rec.ship_from_org_id 		:= l_warehouse_id;
	 l_header_rec.sold_to_org_id		:= l_cust_account_id;
	 l_header_rec.BOOKED_FLAG 		:= 'Y';
	 l_header_rec.flow_status_code          := 'BOOKED';

         -- Calling the Line Cursor.
         FOR line_cur_rec IN order_line_cur (hdr_cur_rec.order_reference)
         LOOP

	    DBMS_OUTPUT.PUT_LINE('Opening Line Cursor');
            l_count := l_count + 1;
            check_order_line_exists (line_cur_rec.order_reference,
                                     line_cur_rec.so_line_num,
                                     l_ord_line_exists
									 
                                    );

            IF l_ord_line_exists = 'Y'
            THEN
               l_line_operation_code := oe_globals.g_opr_update;
			   BEGIN
			   		
					SELECT l.line_id
					INTO   l_line_id 
					FROM   oe_order_lines_ALL l, oe_order_headers_all h
					WHERE  l.header_id = h.header_id 
					AND	   l.orig_sys_line_ref = line_cur_rec.so_line_num
					AND	   h.orig_sys_document_ref = hdr_cur_rec.order_reference;
				EXCEPTION
				
						 WHEN NO_DATA_FOUND THEN
						 X_ERROR_FLAG := 'Y';
						 X_ERROR_MSG:= X_ERROR_MSG || 'LINE_ID NOT FOUND FOR UPDATING LINE QUANTITY'
						 			   			   || LINE_CUR_REC.SO_LINE_NUM;
												   
				END;

			   l_line_tbl(l_count).line_id := l_line_id;
            ELSIF l_ord_line_exists = 'N'
            THEN
               l_line_operation_code := oe_globals.g_opr_create;
            END IF;

/*-------------------------------------
 -- Validating Inventory_item_id
   ------------------------------------- */
            BEGIN
               SELECT inventory_item_id,
                      primary_uom_code
                 INTO l_inv_item_id,
                      x_uom_code
                 FROM mtl_system_items_b
                WHERE segment1 = line_cur_rec.item_number AND ROWNUM = 1;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  x_error_flag := 'Y';
                  x_error_msg :=
                        x_error_msg
                     || 'Item Not found In Apps for the Legacy Item Num'
                     || '='
                     || line_cur_rec.item_number;
               WHEN OTHERS
               THEN
                  x_error_flag := 'Y';
                  x_error_msg :=
                        x_error_msg
                     || 'Unable to get the invetory item id for the Item Num'
                     || line_cur_rec.item_number;
            END;

            /*-----------------------------------------
             -- END Validating Inventory_item_id
             ------------------------------------------*/

			 DBMS_OUTPUT.PUT_LINE('Item Id '||l_inv_item_id);

			 DBMS_OUTPUT.PUT_LINE('Intializing Line record');

			  l_line_tbl(l_count) := OE_ORDER_PUB.G_MISS_LINE_REC;

            l_line_tbl(l_count).orig_sys_line_ref 	:= line_cur_rec.so_line_num;
	    l_line_tbl(l_count).orig_sys_document_ref 	:= line_cur_rec.order_reference;
            l_line_tbl(l_count).ordered_quantity 	:= line_cur_rec.line_quantity;
            l_line_tbl(l_count).inventory_item_id       := l_inv_item_id;
            --l_line_tbl(l_count).return_reason_code 	:= line_cur_rec.return_reason;
            l_line_tbl(l_count).line_type_id 		:= 1001;
            l_line_tbl(l_count).operation 		:= l_line_operation_code;
            --l_line_tbl(l_count).order_quanity_uom       := x_uom_code;
         END LOOP;                                        -- end of lines loop

         IF x_error_flag = 'Y'
         THEN
            ROLLBACK TO SAVEPOINT process_order_sp;

            UPDATE xxbe.xxbe_sales_order_interface
               SET error_flag = x_error_flag,
                   error_message = SUBSTR (TRIM (x_error_msg), 1, 500)
             WHERE order_reference = hdr_cur_rec.order_reference;

            --ROLLBACK TO SAVEPOINT process_order_sp;
            RETURN;
         ELSIF x_error_flag = 'N'
         THEN
            -- Calling the API in the header loop.

			DBMS_OUTPUT.PUT_LINE('Calling the API');
	      oe_order_pub.process_order (p_api_version_number => 1.0
	                                 ,p_init_msg_list => fnd_api.g_false
	                                 ,p_return_values => fnd_api.g_false
	                                                               ,p_action_commit => fnd_api.g_false
	                                                               ,x_return_status => l_return_status
	                                                               ,x_msg_count => l_msg_count
	                                                               ,x_msg_data => l_msg_data
	                                                               ,p_header_rec => l_header_rec
	                                                               ,p_line_tbl => l_line_tbl
	                                                               ,p_action_request_tbl => l_action_request_tbl
	                                                               --OUT PARAMETERS
	                                                               ,x_header_rec => l_header_rec
	                                                               ,x_header_val_rec => x_header_val_rec
	                                                               ,x_Header_Adj_tbl => x_Header_Adj_tbl
	                                                               ,x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
	                                                               ,x_Header_price_Att_tbl => x_Header_price_Att_tbl
	                                                               ,x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
	                                                               ,x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
	                                                               ,x_Header_Scredit_tbl => x_Header_Scredit_tbl
	                                                               ,x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
	                                                               ,x_line_tbl => l_line_tbl
	                                                               ,x_line_val_tbl => x_line_val_tbl
	                                                               ,x_Line_Adj_tbl => x_Line_Adj_tbl
	                                                               ,x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
	                                                               ,x_Line_price_Att_tbl => x_Line_price_Att_tbl
	                                                               ,x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
	                                                               ,x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
	                                                               ,x_Line_Scredit_tbl => x_Line_Scredit_tbl
	                                                               ,x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
	                                                               ,x_Lot_Serial_tbl => x_Lot_Serial_tbl
	                                                               ,x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
	                                                               ,x_action_request_tbl => l_action_request_tbl
                                                               );
         END IF;
		 DBMS_OUTPUT.PUT_LINE('Order API Return Status:'||l_return_status);
		 DBMS_OUTPUT.PUT_LINE('Retrieveing Error Messages');

         -- Retrieve Messages
         IF l_msg_count > 0
         THEN
            FOR l_index IN 1 .. l_msg_count
            LOOP
               l_msg_data :=
                    oe_msg_pub.get (p_msg_index      => l_index,
                                    p_encoded        => 'F');
               x_error_msg := x_error_msg || l_msg_data;
            END LOOP;
         END IF;
DBMS_OUTPUT.PUT_LINE('Checking for Return Status');
         -- Check the return status
         IF l_return_status = fnd_api.g_ret_sts_success
         THEN
            dbms_output.put_line('Header_id is ' || l_header_rec.header_id);
            UPDATE xxbe.xxbe_sales_order_interface
               SET process_flag_yn = 'Y',
                   error_message = NULL,
                   error_flag    = 'N'
             WHERE order_reference = hdr_cur_rec.order_reference;
         ELSE
            ROLLBACK TO SAVEPOINT process_order_sp;
			x_error_flag := 'Y';
                        x_error_msg  := x_error_msg||'Error in API';
            UPDATE xxbe.xxbe_sales_order_interface
               SET error_flag = x_error_flag,
                   error_message = SUBSTR (TRIM (x_error_msg), 1, 4000)
             WHERE order_reference = hdr_cur_rec.order_reference;

            --ROLLBACK TO SAVEPOINT process_order_sp;
            RETURN;
         END IF;
      END LOOP;

	  DBMS_OUTPUT.PUT_LINE('END of API');
   END process_order;

-----------------------------------------------------------------------------

PROCEDURE check_order_hdr_exists (
      p_order_ref        IN       oe_order_headers_all.orig_sys_document_ref%TYPE,
      x_ord_hdr_exists   OUT      VARCHAR2
   )
   IS
      CURSOR oe_order_hdr_exists_cur
      IS
         SELECT header_id
           FROM oe_order_headers_all
          WHERE orig_sys_document_ref = p_order_ref;

      l_header_id   NUMBER;
   BEGIN
      OPEN oe_order_hdr_exists_cur;

      FETCH oe_order_hdr_exists_cur
       INTO l_header_id;

      IF oe_order_hdr_exists_cur%NOTFOUND
      THEN
         x_ord_hdr_exists := 'N';
      ELSE
         x_ord_hdr_exists := 'Y';
         DBMS_OUTPUT.put_line ('ORDER EXISTS: ' || p_order_ref);
      END IF;

      CLOSE oe_order_hdr_exists_cur;
   END check_order_hdr_exists;

-----------------------------------------------------------------------

   PROCEDURE check_order_line_exists (
      x_order_ref         IN       oe_order_headers_all.orig_sys_document_ref%TYPE,
      p_line_num          IN       oe_order_lines_all.line_number%TYPE,
      x_ord_line_exists   OUT      VARCHAR2
   	 )
   IS
      CURSOR oe_order_line_exists_cur
      IS
         SELECT line_id
           FROM oe_order_lines_all
          WHERE orig_sys_document_ref = x_order_ref
            AND line_number = p_line_num;

      l_line_id   NUMBER;
   BEGIN
      OPEN oe_order_line_exists_cur;

      FETCH oe_order_line_exists_cur
       INTO l_line_id;

      IF oe_order_line_exists_cur%NOTFOUND
      THEN
         x_ord_line_exists := 'N';
      ELSE
         x_ord_line_exists := 'Y';
		
         DBMS_OUTPUT.put_line ('ORDER LINE EXISTS: ' || p_line_num);
      END IF;

      CLOSE oe_order_line_exists_cur;
   END check_order_line_exists;

END xxbe_so_interface_pkg;
/