[code]CREATE OR REPLACE PROCEDURE SP_LOAD_ORDER
IS
l_ord_number  STAGE_NA_SHIPMENT.ORDER_NUM_COS%TYPE;
l_ds_ds_id    DATA_SOURCE.DS_ID%TYPE;
--l_erlst_ship_dt STAGE_NA_SHIPMENT.SHIP_DATE%TYPE;
l_carrier  STAGE_NA_SHIPMENT.HAULIER_NAME%TYPE;
l_vehicle_number STAGE_NA_SHIPMENT.HAULIER_NUM%TYPE;
--l_ord_date  STAGE_NA_SHIPMENT.SHIP_DATE%TYPE;
l_source_key  STAGE_NA_SHIPMENT.ORDER_NUM_COS%TYPE;
l_ship_from STAGE_NA_SHIPMENT.SHIP_FROM%TYPE;
l_client STAGE_NA_SHIPMENT.CLIENT%TYPE;
l_ord_id ORDR.ORD_ID%TYPE;
l_sf_sf_id  SHIP_FROM.SF_ID%TYPE;
l_cust_cust_id  CUSTOMER.CUST_CUST_ID%TYPE;
l_del_point STAGE_NA_SHIPMENT.DEL_POINT%TYPE;
l_ord_stat_ord_stat_id ORDR.STATUS.ORD_STAT_ID%TYPE;
l_le_le_id LEGAL_ENTITY.LE_ID%TYPE;
l_mkt_mkt_id MARKET.MKT_ID%TYPE;
l_affco_mkt_id MARKET.MKT_ID%TYPE;
l_code BILL_TYPE.CODE%TYPE;
l_ord_id ORDR.ORD_ID%TYPE; 
l_po_number  STAGE_NA_SHIPMENT.PO_NUMBER%TYPE;
l_expct_ship_date STAGE_NA_SHIPMENT.SHIP_DATE%TYPE;
l_bt_bt_id BILL_TYPE.BT_ID%TYPE;
l_order_process_date STAGE_NA_SHIPMENT.SHIP_DATE%TYPE;
l_storage_loc  STAGE_NA_SHIPMENT.STOR_LOC%TYPE;

BEGIN


SELECT DS_ID INTO l_ds_ds_id
FROM DATA_SOURCE 
WHERE DS_ID=1;



SELECT ORD_STAT_ID INTO l_ord_stat_ord_stat_id
FROM ORDR_STATUS
WHERE ORD_STAT_ID=2;

SELECT LE_ID INTO l_le_le_id
FROM LEGAL_ENTITY
WHERE LE_ID=1;

SELECT MKT_ID INTO l_mkt_mkt_id
FROM MARKET
WHERE CODE=DECODE(SUBSTR(l_client,1,4),'US23','MM','US99','AFFCO');


SELECT QUANTITY INTO l_quantity from STAGE_NA_SHIPMENT;

IF l_quantity >= 0 THEN
l_code:='O';
ELSE
l_code:='C';

SELECT BT_ID INTO l_bt_bt_id
FROM BILL_TYPE
WHERE CODE=l_code
AND DS_DS_ID=1;



FOR i in (SELECT ORDER_NUM_COS,SHIP_DATE,HAULIER_NAME,HAULIER_NUM,PO_NUMBER,STOR_LOC,SHIP_UNIT_CODE,SO_ITEM,SHIP_FROM,DEL_POINT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
            FROM STAGE_NA_SHIPMENT) 
            
LOOP

BEGIN
            SELECT prod_id
              INTO l_prod_prod_id
              FROM product
             WHERE secondary_source_key = i.ship_unit_code;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               Sp_Log_Entry (SYSDATE,
                  'SP_LOAD_ORDER',NULL,'The product id 
                   not found  :',NULL);
          
         END;
         



l_carrier :=i.haulier_name;
l_vehicle_number:=i.haulier_num;
--l_ord_date:=i.ship_date;
l_source_key:=i.order_num_cos;
l_source_key_1:=i.order_num_cos||i.so_item;
--l_ctrl_date:=i.ship_date;
l_po_number:=i.po_number;
l_storage_loc:=i.stor_loc;


BEGIN


SELECT SF_ID INTO l_sf_sf_id 
FROM SHIP_FROM WHERE SOURCE_KEY=i.ship_from;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   Sp_Log_Entry(SYSDATE, 'Sp_Load_Ordr',
               NULL, 'The sf_id  not be found in  table',NULL
          	  );

 END;

BEGIN
  SELECT cust_id
  INTO   l_cust_cust_id
  FROM   customer
  WHERE  true_gcdb_source_key = i.del_point;


  EXCEPTION
  WHEN NO_DATA_FOUND THEN
   Sp_Log_Entry(SYSDATE, 'Sp_Load_Ordr',
               NULL, 'The Customerid  not be found in table',NULL
          	  );

 END;



BEGIN


IF i.order_num_cos NOT IN (SELECT ORD_NUMBER FROM ORDR) THEN

INSERT INTO ORDR (ORD_NUMBER,DS_DS_ID,ERLST_SHIP_DATE,CARRIER,VEHICLE_NUMBER,ORD_DT,SOURCE_KEY,SF_SF_ID,CUST_CUST_ID,ORD_STAT_ORD_STAT_ID,
                        LE_LE_ID,MKT_MKT_ID,ORD_ID,CTRL_DT,PO_NUMBER,EXPCT_SHIP_DATE,BT_BT_ID,ORDER_PROCESS_DT,STORAGE_LOC)
                        
                        WHERE ORDER_NUM_COS NOT IN (SELECT ORDER_NUM)
                        
VALUES
(l_source_key,l_ds_ds_id,i.ship_date,l_carrier,l_vehicle_number,i.ship_date,l_source_key,l_sf_sf_id,l_cust_cust_id,l_ord_stat_ord_stat_id,
l_le_le_id,l_mkt_mkt_id,l_ord_id,i.ship_date,l_po_number,i.ship_date,l_bt_bt_id,i.ship_date,l_storage_loc);

END IF;


  EXCEPTION
      WHEN OTHERS THEN
       l_error_msg := SQLERRM;
	   Sp_Log_Entry( SYSDATE, 'Sp_Load_Ordr',
	      NULL, l_error_msg,NULL);
          
          END;



--SELECT ORD_ID INTO l_ord_id FROM ORDR WHERE 



    BEGIN  

INSERT INTO ORDR_PRODUCT(VOLUME,SOURCE_KEY,ORD_ORD_ID,PROD_PROD_ID,ORD_PROD_ID,LOCAL_CURRENCY) VALUES

(i.ship_unit_code,l_source_key_1,null,l_prod_prod_id,ORD_PROD_SEQ.CURRVAL,i.currency);


  EXCEPTION
      WHEN OTHERS THEN
       l_error_msg := SQLERRM;
	   Sp_Log_Entry( SYSDATE, 'Sp_Load_Ordr',
	      NULL, l_error_msg,NULL,NULL);
          
          END;
          
          
   END LOOP;       
           Sp_Log_Entry( SYSDATE, 'Sp_Load_Ordr',
  	          'COMPLETE', NULL,
   	          NULL,NULL
	     );



/*EXCEPTION
      WHEN OTHERS THEN
       l_error_msg := SQLERRM;
	   Sp_Log_Entry( SYSDATE, 'Sp_Load_Ordr',
	      NULL, l_error_msg,NULL,NULL);
          
          END;*/
END SP_LOAD_ORDER;
/
show errors;[/code]