CREATE OR REPLACE PACKAGE BODY xx_iappz_po_import_pkg AS
/************************************************************************************************
*   Program Name:  xx_iappz_po_import_pkg 									*
*   Short Name  :														*
*   File Name   :  	       											*
*   Developer   :  Sreekanth - Sivananda                                                        *
*   Description :  This package  is to load standard purchase order from flat file 			*	
*																*
*   Parameters  :                                                                               *
*   Return value:                                                                               *
*   Restart     :                                                                               *
*                                                                                               *
*  																*
*************************************************************************************************/
PROCEDURE xx_iappz_po_import(errbuf out VARCHAR2,retcode out NUMBER) AS
/* LOCAL VARIABLE DECLARATION*/
l_vendor_id 	    NUMBER;
l_vendor_site_id 	NUMBER;
l_err_status 	VARCHAR2(1) := 'N';
l_err_stat 		VARCHAR2(1) := 'N';
l_person_id 	NUMBER;
l_org_code 		VARCHAR2(10);
l_item_id 		NUMBER;
l_sqlerrm 		VARCHAR2(500);
l_sqlcode 		VARCHAR2(100);
l_uom 		    VARCHAR2(10);
l_error_msg 	VARCHAR2(500);
error_source_c  CONSTANT VARCHAR2 (50) := 'xx_iappz_po_import_pkg';
/*cursor for headers*/
CURSOR  po_h is
   SELECT rowid,batch_id
            ,action
            ,ORG_ID
            ,DOCUMENT_TYPE_CODE
            ,CURRENCY_CODE
            ,AGENT_NAME
            ,VENDOR_NAME
            ,VENDOR_SITE_CODE
            ,SHIP_TO_LOCATION
            ,BILL_TO_LOCATION
            ,APPROVAL_STATUS
            ,FREIGHT_CARRIER
            ,FOB
            ,FREIGHT_TERMS
	    ,ORIGINAL_PO_HEADER_ID
    FROM xx_iappz_po_headers_stg;
/*cursor for lines*/
 CURSOR po_l(p_orig_sys_num NUMBER)  is
   SELECT  LINE_NUM
             ,SHIPMENT_NUM
             ,LINE_TYPE
             ,ITEM
             ,ITEM_DESCRIPTION
             ,ITEM_ID
             ,UOM_CODE
             ,QUANTITY
             ,UNIT_PRICE
             ,SHIP_TO_ORGANIZATION_CODE
             ,SHIP_TO_LOCATION
             ,LIST_PRICE_PER_UNIT
	     ,ORIGINAL_PO_HEADER_ID
    FROM xx_iappz_po_lines_stg xxl 
	where xxl.ORIGINAL_PO_HEADER_ID=p_orig_sys_num;
 BEGIN /* MAIN PROCEDURE xx_iappz_po_import*/
 /* PO HEAD LOOP STARTS */
   FOR po_head IN po_h LOOP ---Loop starts for header (1.1)
/*validation FOR vendor*/
	BEGIN
		SELECT vendor_id INTO l_vendor_id 
		FROM po_vendors
		WHERE VENDOR_NAME=po_head.VENDOR_NAME;
	EXCEPTION
     	WHEN NO_DATA_FOUND THEN
         	retcode:= 1;
         	l_err_stat := 'Y';
			l_error_msg:='Error: VENDOR_NAME  specified is invalid';
			Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
		  	UPDATE xx_iappz_po_headers_stg
           	SET process_flag = 'E',
               error_mesg = 'vendor id does not exists'
         	WHERE rowid = po_head.rowid;
		 	  l_err_status := 'Y';
  	END;
/* validation for vendor_site */
	BEGIN
		SELECT 
		     pvs.VENDOR_SITE_id INTO l_vendor_site_id
		FROM PO_VENDOR_SITES_ALL pvs,
		     po_vendors pv
 		WHERE pvs.vendor_id=pv.vendor_id
 		AND VENDOR_SITE_code=po_head.VENDOR_SITE_CODE
 		AND org_id=204
 		AND pv.vendor_id=l_vendor_id ;
	EXCEPTION
      WHEN NO_DATA_FOUND THEN
         retcode:= 1;
         l_err_stat := 'Y';
		 l_error_msg:='Error: VENDOR_SITE  specified is invalid';
		 Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
	WHEN OTHERS THEN
		l_error_msg:=SQLERRM;
		UPDATE xx_iappz_po_headers_stg
            SET process_flag = 'E',
               error_mesg = 'vendor site id does not exists'||l_error_msg
         	WHERE rowid = po_head.rowid;
 		l_err_status := 'Y';
 	END;
   /*validation FOR buyers*/
	BEGIN
		SELECT 
		     person_id  INTO l_person_id  
		FROM Per_all_people_f
 		WHERE full_name =po_head.agent_name
		AND EFFECTIVE_END_DATE>sysdate;
	dbms_output.put_line('agent id is '||l_person_id   );
	EXCEPTION
      WHEN NO_DATA_FOUND THEN
         retcode:= 1;
         l_err_stat := 'Y';
		 l_error_msg:='Error: BUYER specified is invalid';
		 Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
	WHEN OTHERS THEN
		l_error_msg:=SQLERRM;
		UPDATE xx_iappz_po_headers_stg
            SET process_flag = 'E',
               error_mesg = 'Agent id does not exists'||l_error_msg
         WHERE rowid = po_head.rowid;
 	   l_err_status := 'Y';
 	END;
/* validating FOR the organization*/
	BEGIN
		SELECT 
			organization_code INTO l_org_code
            FROM mtl_parameters
       	WHERE organization_id = po_head.org_id;
	dbms_output.put_line('org codeis'||l_org_code);
	EXCEPTION
      WHEN NO_DATA_FOUND THEN
         retcode:= 1;
         l_err_stat := 'Y';
		 l_error_msg:='Error: ORG_ID specified is invalid';
		 Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
	WHEN OTHERS THEN
		l_error_msg:=SQLERRM;
		UPDATE xx_iappz_po_headers_stg
           	SET process_flag = 'E',
               error_mesg = 'org does not exists'||L_ERROR_MSG
         WHERE rowid = po_head.rowid;
 	   l_err_status := 'Y';
	END;
/* INSERTING PO HEADER INFORMATION TO INTERFACE TABLE*/
IF l_err_status = 'N' THEN
BEGIN
  dbms_output.put_line(' INSERTING PO HEADER INFORMATION');
      INSERT INTO po_headers_interface
                     (INTERFACE_HEADER_ID
                     ,batch_id
                     ,ACTION
			   ,PROCESS_CODE
                     ,ORG_ID
                     ,DOCUMENT_TYPE_CODE
                     ,CURRENCY_CODE
                     ,AGENT_NAME
                     ,VENDOR_NAME
                     ,VENDOR_SITE_CODE
                     ,SHIP_TO_LOCATION
                     ,BILL_TO_LOCATION
		     	   ,CREATION_DATE
                     ,FREIGHT_CARRIER
                     ,FOB
                     ,FREIGHT_TERMS
			    )
                    values
                    ( po_headers_interface_s.nextval
                     ,po_head.batch_id
                     ,po_head.ACTION
			   ,'PENDING'
                     ,po_head.ORG_ID
			   ,po_head.DOCUMENT_TYPE_CODE
                     ,po_head.CURRENCY_CODE
                     ,po_head.AGENT_NAME
                     ,po_head.VENDOR_NAME
                     ,po_head.VENDOR_SITE_CODE
                     ,po_head.SHIP_TO_LOCATION
                     ,po_head.BILL_TO_LOCATION
			   ,SYSDATE
                     ,po_head.FREIGHT_CARRIER
                     ,po_head.FOB
                     ,po_head.FREIGHT_TERMS
					 );
   END;
         UPDATE xx_iappz_po_headers_stg
            SET process_flag = 'S',
               error_mesg = 'NULL'
         	WHERE rowid = po_head.rowid;
   END IF;
/* PO LINE LOOP STARTS */   
 FOR po_line IN po_l(po_head.ORIGINAL_PO_HEADER_ID) LOOP  ---Loop starts for po detail (1.2)
/* validation FOR the item*/
     BEGIN
SELECT inventory_item_id
        INTO l_item_id
        FROM mtl_system_items
       WHERE segment1 = po_line.item
         AND organization_id = 204;
dbms_output.put_line('Item exist ');
EXCEPTION
      WHEN NO_DATA_FOUND THEN
         retcode:= 1;
         l_err_stat := 'Y';
		 l_error_msg:='Error: ITEM specified is invalid';
		 Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
	     UPDATE xx_iappz_po_headers_stg
           	SET process_flag = 'E',
               error_mesg = 'org does not exists'||l_error_msg
         WHERE rowid = po_head.rowid;
END;
/*validation FOR uom*/
BEGIN
SELECT UOM_CODE
      INTO l_uom
      FROM mtl_units_of_measure
     WHERE uom_code =po_line.UOM_CODE;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
         retcode:= 1;
         l_err_stat := 'Y';
		 l_error_msg:='Error: UOM specified is invalid';
		 Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     => l_error_msg
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
		UPDATE xx_iappz_po_headers_stg
           	SET process_flag = 'E',
               error_mesg = 'org does not exists'||l_error_msg
         WHERE rowid = po_head.rowid;
END;
IF l_err_status = 'N' THEN
BEGIN
     dbms_output.put_line(' INSERTING INTO lines info');
        INSERT INTO po_lines_interface
            ( INTERFACE_LINE_ID
		 ,INTERFACE_HEADER_ID 
	       ,LINE_NUM
             ,SHIPMENT_NUM
             ,LINE_TYPE
             ,ITEM
             ,ITEM_DESCRIPTION
             ,ITEM_ID
		 ,CATEGORY
		 ,CATEGORY_ID
             ,UOM_CODE
             ,QUANTITY
             ,UNIT_PRICE
             ,SHIP_TO_ORGANIZATION_CODE
		 ,CREATION_DATE
             ,SHIP_TO_LOCATION
             ,LIST_PRICE_PER_UNIT
		 ,PROMISED_DATE)
        VALUES
            ( po_lines_interface_s.nextval
	        ,po_headers_interface_s.currval
	        ,po_line.LINE_NUM	     
              ,po_line.SHIPMENT_NUM
              ,po_line.LINE_TYPE
              ,po_line.ITEM
              ,po_line.ITEM_DESCRIPTION
              ,po_line.ITEM_ID
		  ,'MISC.MISC'
		  ,1
              ,po_line.UOM_CODE  
              ,po_line.QUANTITY
              ,po_line.UNIT_PRICE
              ,po_line.SHIP_TO_ORGANIZATION_CODE
		  ,SYSDATE
              ,po_line.SHIP_TO_LOCATION
              ,po_line.LIST_PRICE_PER_UNIT
		  ,SYSDATE);
     END;
  END IF;
  END LOOP; ---Loop ends for po lines (1.2)
   END LOOP; ---header loop ends (1.1)
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
        l_sqlerrm := substr(sqlerrm,1,200);
        l_sqlcode := to_char(sqlcode);
        retcode:= 2;
        l_err_status := 'Y';
		Iappz_common_pkg.insert_conv_error( rec_id_p   => NULL
                                          ,error_msg_p     =>l_sqlerrm
                                          ,error_type_p    => 'PO INTERFACE'
                                          ,error_source_p  => error_source_c
                                          ,attribute1_p    => TO_CHAR(SYSDATE,'DD-MON-RR HH:MI:SS')
                                          ,attribute2_p    => NULL
                                          ,attribute3_p    =>NULL                                                          
                                           );
	END xx_iappz_po_import ;
END xx_iappz_po_import_pkg ;
/