create or replace
PROCEDURE Uploadrcvingproc AS

	rcv_transaction_id		NUMBER;
	rcv_GROUP_id			NUMBER;
	rcv_Header_id			NUMBER;
	xsError					VARCHAR2(255);
	xdProDate				DATE;

	CURSOR	cur_grn_new
	IS
	SELECT 	a.GRN_HEADER_ID,
			a.SUPPLIER,
			B.DESCRIPTION,
			a.PO_NO,
			b.UOM,
			b.RCV_QTY,
			b.PO_LINE_ID,
			b.ORG_ID,
			a.DC_NO,
			a.DC_DATE,
			a.NO_OF_PACKS,
			a.NET_WT,
			a.INVOICE_NO,
			a.INVOICE_DATE ,
			a.INVOICE_CURRENCY,
			a.INVOICE_AMOUNT,
			a.RCV_DATE arrival_dt,
			a.DELIVERED_DATE grn_signed_date,
			a.DELIVERED_DATE fin_date,
			DECODE(a.ATTRIBUTE2,1,'Local','Imported'),
			a.STORAGE_LOC Location,
			c.CHA_NAME,
			a.CT3_NO,
			a.DUTY_FORGONE,
			a.BOE_NO,
			a.BOE_DATE,
			d.TYPE_DESCIPTION,
			a.CIF_VALUE,
			a.DOCS_RCV_DATE,
			a.CLEARED_DATE,
			a.LANDING_DATE   ,
			e.AGENT_NAME,
			a.BOND_NO   ,
			a.BOND_DATE      ,
			a.BOND_EXP_DATE,
			a.MODE_ID,
			a.BOE_TYPE
	FROM 	RCV_GRN_HEADER a,
			RCV_GRN_LINE b,
			RCV_CHA c,
			RCV_GOODS_TYPE D,
			RCV_GRN_STEAMER_AGENT E
	WHERE 	a.ATTRIBUTE1 		= 2
	AND 	a.DELIVERED_DATE 	IS NOT NULL
	AND 	a.GRN_STATUS 		<> 'Cancelled'
	AND 	a.GRN_HEADER_ID 	= b.GRN_NO
	AND 	a.CHA_ID 			= c.CHA_ID(+)
	AND 	a.UPLOAD_FLAG 		= 1
	AND 	d.TYPE_ID(+) 		= a.TYPE_OF_GOODS
	AND 	e.AGENT_NO(+) 	= a.STAEMER_AGENT_NO
	AND     a.location    = '1'
	AND		b.RCV_QTY			<> 0
--  AND   a.grn_no_part2=17288
  ;


	xnItemId			NUMBER;
	xnCat				NUMBER;
	xnUnitPrice			NUMBER;
	xsUOM				VARCHAR2(255);
	xnLocId				NUMBER;
	xnlocShip			NUMBER;
	xnVen				NUMBER;
	xsVenSite			VARCHAR2(255);
	xnShpId				NUMBER;
	xnFlag				NUMBER:= 0;
	xnPONum				NUMBER;
	xnError				NUMBER:= 0;
	xnPOLocId			NUMBER;
	xnShipLocId		NUMBER;
        xsGlPeriod              VARCHAR2(255); 
  
BEGIN
  
 dbms_output.put_line('Started Proc 11  ERROR_No = '||xnError);
  
	UPDATE 	RCV_UPLOAD_ERROR
	SET 	ERROR_MSG = NULL;

	FOR crec_grn IN cur_grn_new
	LOOP

		IF xnFlag = 0
		THEN

			SELECT  apps.RCV_HEADERS_INTERFACE_S.NEXTVAL,
					apps.RCV_INTERFACE_GROUPS_S.NEXTVAL
			INTO	rcv_Header_id,
					rcv_GROUP_id
			FROM 	dual;

			BEGIN
				SELECT	a.vendor_id, VENDOR_SITE_CODE, a.segment1
				INTO	xnVen,	xsVenSite, xnPONum
				FROM	apps.po_headers_all a, apps.po_vendor_sites_all b
				WHERE	a.vendor_id 		= b.vendor_id
				AND		a.VENDOR_SITE_ID 	= b.VENDOR_SITE_ID
				AND		a.po_header_id 		= crec_grn.PO_NO
				AND     a.org_id            = 1
				AND     b.org_id            = 1;
			EXCEPTION
				WHEN OTHERS THEN
					xsError := SUBSTR(SQLERRM,1,254);
					UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
					xnError	:= 1;
			END;
                        /*Checking Gl Period Status*/
                        BEGIN
				SELECT 	A.CLOSING_STATUS INTO xsGlPeriod
                                FROM  	APPS.GL_PERIOD_STATUSES  A
                                WHERE 	TRUNC(SYSDATE) BETWEEN TRUNC(A.START_DATE) AND TRUNC(A.END_DATE) AND
                                SET_OF_BOOKS_ID=1 AND APPLICATION_ID=101;
                                IF	xsGlPeriod <> 'O'
                                THEN
                                  xsError := 'GL Period not open for '||TO_CHAR(SYSDATE,'MONTH')||' Please open the same and continue operations.';
					UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
					xnError	:= 1;
                                END IF;
			EXCEPTION
				WHEN OTHERS THEN
					xsError := 'GL Period not open for '||TO_CHAR(SYSDATE,'MONTH')||' Please open the same and continue operations.';
					UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
					xnError	:= 1;
			END;
                       /* END OF GL PERIOD CHECKING */

			BEGIN
				SELECT	LINE_LOCATION_ID, SHIP_TO_LOCATION_ID,promised_date
				INTO	xnPOLocId, xnShipLocId,xdProDate
				FROM 	apps.po_line_locations_all
				WHERE	PO_LINE_ID 				= crec_grn.PO_LINE_ID
				AND		SHIP_TO_ORGANIZATION_ID = crec_grn.ORG_ID
				AND     org_id                = 1;

			EXCEPTION
				WHEN OTHERS THEN
					xsError := SUBSTR(SQLERRM,1,254);
					UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
					xnError	:= 1;
			END;
      
     dbms_output.put_line('Started Proc 22  ERROR_No = '||xnError);
      
			BEGIN
			dbms_output.put_line('HEADER_INTERFACE_ID = '||rcv_Header_id||' GROUP_ID = '||rcv_GROUP_id ||' VENDOR_ID= '||xnVen ||' VENDOR_SITE_CODE= '||xsVenSite||' SHIP_TO_ORGANIZATION_ID= '||crec_grn.ORG_ID||' EXPECTED_RECEIPT_DATE= '||xdProDate||' PACKING_SLIP= '||SUBSTR(crec_grn.INVOICE_NO,1,25));
      
			INSERT INTO apps.RCV_HEADERS_INTERFACE
				(
				 HEADER_INTERFACE_ID		,
				 GROUP_ID			 ,
				 PROCESSING_STATUS_CODE 	 ,
				 RECEIPT_SOURCE_CODE		 ,
				 TRANSACTION_TYPE		 ,
				 LAST_UPDATE_DATE		 ,
				 LAST_UPDATED_BY		 ,
				 LAST_UPDATE_LOGIN		 ,
				 CREATION_DATE			 ,
				 CREATED_BY			 ,
				 VENDOR_ID			 ,
				 VENDOR_SITE_CODE		 ,
				 VALIDATION_FLAG		 ,
				 SHIP_TO_ORGANIZATION_ID ,
				 EXPECTED_RECEIPT_DATE,
         		 PACKING_SLIP --
				)
				VALUES
				(
				rcv_Header_id,
				rcv_GROUP_id,
				'PENDING',
				 'VENDOR',
				 'NEW',
				 SYSDATE,
				 1290,
				 1290,
				 SYSDATE,
				 1290,
				xnVen,
				xsVenSite,
				'Y',
				crec_grn.ORG_ID,
				xdProDate,
        SUBSTR(crec_grn.INVOICE_NO,1,25)
				);

			EXCEPTION
				WHEN OTHERS THEN
					xsError := SUBSTR(SQLERRM,1,254);
          UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
					xnError	:= 1;
			END;
			xnFlag := 1;
		END IF;
    
    dbms_output.put_line('Started Proc 33  ERROR_No = '||xnError);
    
		BEGIN
			xnError	:= 0;

			SELECT 	item_id
			INTO	xnItemId
			FROM	apps.po_lines_all
			WHERE 	PO_LINE_ID = crec_grn.PO_LINE_ID
			AND     org_id = 1;
		EXCEPTION
			WHEN OTHERS THEN
				UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = 'This is a service Line Item, cannot Upload this line into financials. Line Description:'||crec_grn.DESCRIPTION;
				xnError	:= 1;
		END;
    
    dbms_output.put_line('Started Proc 44  ERROR_No = '||xnError);
		
    IF (xnError = 0 )
		THEN

			IF (xnError = 0 )
			THEN
        dbms_output.put_line('Started Proc 55  ERROR_No = '||xnError);
				SELECT  apps.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
				INTO	rcv_transaction_id
				FROM 	dual;
        dbms_output.put_line('Started Proc 66  rcv_transaction_id = '||rcv_transaction_id);
				BEGIN
/*        dbms_output.put_line('Started Proc 77  rcv_transaction_id = '||rcv_transaction_id);
        dbms_output.put_line('INTERFACE_TRANSACTION_ID = '||rcv_transaction_id||' HEADER_INTERFACE_ID = '||rcv_Header_id ||' GROUP_ID= '||rcv_GROUP_id ||' QUANTITY= '||crec_grn.RCV_QTY||' UNIT_OF_MEASURE= '||crec_grn.UOM||' DOCUMENT_NUM= '||xnPONum||' SHIP_TO_LOCATION_ID= '||xnShipLocId||' VENDOR_ID= '||xnVen||' VENDOR_SITE_CODE= '||xsVenSite||' TO_ORGANIZATION_ID= '||crec_grn.ORG_ID||' PO_LINE_LOCATION_ID= '||xnPOLocId);
        dbms_output.put_line('ITEM_ID= '||xnItemId);
        dbms_output.put_line('QUANTITY_SHIPPED= '||crec_grn.RCV_QTY|| ' PACKING_SLIP= '||SUBSTR(crec_grn.INVOICE_NO,1,25));
*/        
					INSERT INTO apps.RCV_TRANSACTIONS_INTERFACE
					(INTERFACE_TRANSACTION_ID	,
					 HEADER_INTERFACE_ID	        ,
					 GROUP_ID			,
					 LAST_UPDATE_DATE		,
					 LAST_UPDATED_BY		,
					 CREATION_DATE			,
					 CREATED_BY			,
					 LAST_UPDATE_LOGIN		,
					 TRANSACTION_TYPE		,
					 TRANSACTION_DATE		,
					 PROCESSING_STATUS_CODE 	,
					 PROCESSING_MODE_CODE		,
					 TRANSACTION_STATUS_CODE	,
					 QUANTITY			,
					 UNIT_OF_MEASURE		,
					 AUTO_TRANSACT_CODE		,
					 RECEIPT_SOURCE_CODE		,
					 SOURCE_DOCUMENT_CODE		,
					 DOCUMENT_NUM			,
					 SHIP_TO_LOCATION_ID		,
					 VENDOR_ID			,
					 VENDOR_SITE_CODE		,
					 VALIDATION_FLAG                ,
					 TO_ORGANIZATION_ID           ,
					 DESTINATION_TYPE_CODE  ,
					 PO_LINE_LOCATION_ID,
					 ITEM_ID,
					 QUANTITY_SHIPPED,
           			 PACKING_SLIP --
					)
					VALUES
					(
					rcv_transaction_id,
					rcv_Header_id,
					rcv_GROUP_id,
					SYSDATE,
					 1290,
					 SYSDATE,
					 1290,
					 1290,
					 'RECEIVE',
					 SYSDATE,
					 'PENDING',
					 'BATCH',
					 'PENDING',
					crec_grn.RCV_QTY,
					crec_grn.UOM,
					'RECEIVE',
					 'VENDOR',
					 'PO',
					 xnPONum,
					 xnShipLocId,
					 xnVen,
					 xsVenSite,
					 'Y',
					 crec_grn.ORG_ID,
					 'RECEIVING',
					 xnPOLocId,
					 xnItemId,
					 crec_grn.RCV_QTY,
           SUBSTR(crec_grn.INVOICE_NO,1,25)
					);
                    
				EXCEPTION
					WHEN OTHERS THEN
						xsError := SUBSTR(SQLERRM,1,254);
						UPDATE	RCV_UPLOAD_ERROR SET ERROR_MSG = xsError;
						xnError	:= 1;
				END;
			END IF;
		END IF;

	END LOOP;
	dbms_output.put_line('Procedure ended with out error ');

	COMMIT;
END Uploadrcvingproc;