CREATE OR REPLACE PROCEDURE XX_IMPORT_GRN(ERRBUFF OUT VARCHAR2,RETCODE OUT NUMBER)
AS

-- TO PICK ALL THE APPROVED PO'S OF SENORA WHICH HAVE BEEN CREATED FROM THE CUSTOM PROGRAM

CURSOR C_PNDGPO
IS
SELECT
PHA.SEGMENT1 DOCUMENT_NUM,
PHA.PO_HEADER_ID,
PHA.VENDOR_ID,
PHA.VENDOR_SITE_ID,
PHA.ORG_ID,
PLA.PO_LINE_ID,
PLA.ITEM_ID,
PLA.QUANTITY,
PLA.UNIT_MEAS_LOOKUP_CODE UOM,
PLLA.LINE_LOCATION_ID,
PHA.AUTHORIZATION_STATUS,
PDA.DESTINATION_ORGANIZATION_ID,
PDA.DESTINATION_SUBINVENTORY,
PLLA.PROMISED_DATE
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PHA.ORG_ID=382                                            -- TO PICK ONLY THE SENORA PO'S
AND PHA.ATTRIBUTE2 IS NOT NULL                                -- TO PICK ONLY THE PO CREATED FROM THE AUTO PO PROGRAM
AND PHA.ATTRIBUTE1 ='CUSTOMIRN'                               -- TO PICK ONLY THE PO CREATED FROM THE AUTO PO PROGRAM
AND PHA.AUTHORIZATION_STATUS IN ('PRE-APPROVED','APPROVED')   -- TO PICK ONLY APPROVED PO'S FOR GRN CREATION
AND (PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED) <> 0;            -- TO PICK ONLY THE PENDING PO'S FOR WHICH GRN COULD NOT BE CREATED

CURSOR C_INTLINES_CNT
IS
SELECT COUNT(*)
FROM RCV_HEADERS_INTERFACE
WHERE ORG_ID=382
AND PROCESSING_STATUS_CODE <> 'SUCCESS';


V_COUNT                NUMBER;
M_USER_ID              NUMBER;
M_RESP_ID              NUMBER;
M_RESP_APPL_ID         NUMBER;
L_CHR_LOT_NUMBER       VARCHAR2 (50);
L_CHR_RETURN_STATUS    VARCHAR2 (2000);
L_NUM_MSG_COUNT        NUMBER;
L_CHR_MSG_DATA         VARCHAR2 (50);

BEGIN


    -- TO CHECK IF SOME RECORD IS STUCK IN THE INTERFACE.IF THERE IS ANY THEN WE NEED TO CLEAR THEM FIRST
    -- BEFORE WE PROCEED

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'STARTING....');
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'CHECKING THE NUMBER OF LINES  STUCK IN THE PO INTERFACE ');

    OPEN C_INTLINES_CNT;
    FETCH C_INTLINES_CNT  INTO V_COUNT;
        IF C_INTLINES_CNT%NOTFOUND THEN
           V_COUNT := 0;
        END IF;
    CLOSE C_INTLINES_CNT;

 -- IF THERE IS SOME DATA STUCK IN THE TRANSACTION INTERFACE THEN EXIT THE PROCEDURE ELSE DO FURTHER PROCESSING

   IF V_COUNT <> 0 THEN
   FND_FILE.PUT_LINE(FND_FILE.LOG, 'THERE ARE PENDING LINES IN THE PO INTERFACE.PLEASE CLEAR THEM FIRST ....EXITING ');
   RETURN;
   END IF;

   FND_FILE.PUT_LINE(FND_FILE.LOG, 'THERE ARE NO PENDING LINES IN THE TRANSACTION INTERFACE... ');


   FND_FILE.PUT_LINE(FND_FILE.LOG, 'STARTING THE ENVIRONMENT INITIALIZATION ' );

-- GET DETAILS OF THE PERSON RUNNING THE REQUEST

   M_USER_ID      := FND_GLOBAL.USER_ID;
   M_RESP_ID      := FND_PROFILE.VALUE ('RESP_ID');
   M_RESP_APPL_ID := FND_PROFILE.VALUE ('RESP_APPL_ID');



 -- INITIALIZATION REQUIRED FOR R12

   MO_GLOBAL.SET_POLICY_CONTEXT ('S',382); -- 382 FOR SENORA OU
   MO_GLOBAL.INIT ('INV');

-- INITIALIZATION FOR ORGANIZATION_ID

   INV_GLOBALS.SET_ORG_ID (381);          --  381 FOR SNF

-- INITIALIZE ENVIRONMENT

   FND_GLOBAL.APPS_INITIALIZE (USER_ID => M_USER_ID,
   RESP_ID => M_RESP_ID,
   RESP_APPL_ID => M_RESP_APPL_ID);

   FND_FILE.PUT_LINE(FND_FILE.LOG, 'ENVIRONMENT INITIALIZATION DONE ' );
   FND_FILE.PUT_LINE(FND_FILE.LOG, 'FETCHING  AND INSERTING ALL THE ELIGIBLE RECORDS  INTO TRANSACTION INTERFACE TABLE ');

-- LOOP THROUGH THE RECORDS AND INSERT DATA IN INTERFACE TABLE

   FOR I IN C_PNDGPO

   LOOP

   INSERT INTO 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_ID,
    EXPECTED_RECEIPT_DATE,
    VALIDATION_FLAG,
    ORG_ID
    )
   VALUES
    (
    RCV_HEADERS_INTERFACE_S.NEXTVAL,
    RCV_INTERFACE_GROUPS_S.NEXTVAL,
    'PENDING',
    'VENDOR',
    'NEW',
     SYSDATE,
     M_USER_ID,
     M_USER_ID,
     SYSDATE,
     M_USER_ID,
     I.VENDOR_ID,
     I.VENDOR_SITE_ID,
     SYSDATE +1,
     'Y',
     382
     );

     INSERT INTO RCV_TRANSACTIONS_INTERFACE
     (INTERFACE_TRANSACTION_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,
      PO_HEADER_ID,
      PO_LINE_ID,
      ITEM_ID,
      QUANTITY,
      UNIT_OF_MEASURE,
      PO_LINE_LOCATION_ID,
      AUTO_TRANSACT_CODE,
      RECEIPT_SOURCE_CODE,
      TO_ORGANIZATION_CODE,
      SOURCE_DOCUMENT_CODE,
      DOCUMENT_NUM,
      DESTINATION_TYPE_CODE,
      DELIVER_TO_PERSON_ID,
      DELIVER_TO_LOCATION_ID,
      SUBINVENTORY,
      HEADER_INTERFACE_ID,
      VALIDATION_FLAG,
      ORG_ID
      )
      VALUES
      (
      RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
      RCV_INTERFACE_GROUPS_S.CURRVAL,
      SYSDATE,
      M_USER_ID,
      SYSDATE,
      M_USER_ID,
      M_USER_ID,
      'RECEIVE',
      I.PROMISED_DATE,
      'PENDING',
      'BATCH',
      'PENDING',
      I.PO_HEADER_ID,
      I.PO_LINE_ID,
      I.ITEM_ID,
      I.QUANTITY,
      I.UOM,
      I.LINE_LOCATION_ID,
      'DELIVER',
      'VENDOR',
      'SNF',
      'PO',
      I.DOCUMENT_NUM,
      'INVENTORY',
      NULL,
      NULL,
      I.DESTINATION_SUBINVENTORY,
      RCV_HEADERS_INTERFACE_S.CURRVAL,
      'Y',
      382
     );

COMMIT;

-- TO CHECK IF THE ITEM IS UNDER LOT CONTROL OR NOT.IF IT IS DO NECESSARY PROCESSING

     SELECT COUNT(*)
     INTO V_COUNT
     FROM MTL_SYSTEM_ITEMS_B B
     WHERE INVENTORY_ITEM_ID=I.ITEM_ID
     AND LOT_CONTROL_CODE = 2                            -- 2 - FULL_CONTROL, 1 - NO CONTROL
     AND ORGANIZATION_ID = I.DESTINATION_ORGANIZATION_ID;

  IF V_COUNT > 0 THEN

------API for lot contr

  L_CHR_LOT_NUMBER :=INV_LOT_API_PUB.AUTO_GEN_LOT
  (P_ORG_ID => I.DESTINATION_ORGANIZATION_ID,
   P_INVENTORY_ITEM_ID => I.ITEM_ID,
   P_PARENT_LOT_NUMBER => NULL,
   P_SUBINVENTORY_CODE => NULL,
   P_LOCATOR_ID => NULL,
   P_API_VERSION => 1.0,
   P_INIT_MSG_LIST => 'F',
   P_COMMIT => 'T',
   P_VALIDATION_LEVEL => 100,
   X_RETURN_STATUS => L_CHR_RETURN_STATUS,
   X_MSG_COUNT => L_NUM_MSG_COUNT,
   X_MSG_DATA => L_CHR_MSG_DATA
   );

   IF L_CHR_RETURN_STATUS = 'S' THEN

   COMMIT;

   ELSE

   ROLLBACK;

   END IF;


  INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
  (TRANSACTION_INTERFACE_ID,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN,
   LOT_NUMBER,
   TRANSACTION_QUANTITY,
   PRIMARY_QUANTITY,
   SERIAL_TRANSACTION_TEMP_ID,
   PRODUCT_CODE,
   PRODUCT_TRANSACTION_ID)
   VALUES
   (
   MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,        --TRANSACTION_INTERFACE_ID
   SYSDATE,                                    --LAST_UPDATE_DATE
   M_USER_ID,                                   --LAST_UPDATED_BY
   SYSDATE,                                    --CREATION_DATE
   M_USER_ID,                                   --CREATED_BY
   M_USER_ID,
   L_CHR_LOT_NUMBER,                           --LOT_NUMBER
   I.QUANTITY,                                 --TRANSACTION_QUANTITY
   I.QUANTITY,                                 --PRIMARY_QUANTITY
   NULL,                                       --SERIAL_TRANSACTION_TEMP_ID
   'RCV',                                      --PRODUCT_CODE
   RCV_TRANSACTIONS_INTERFACE_S.CURRVAL        --PRODUCT_TRANSACTION_ID
   );

END IF;

END LOOP;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'INSERTION INTO PO INTERFACE  DONE.WAIT FOR RECIEVING TRANSACTION MANAGER TO PICK THESE UP ' );

END;