How to open the Secondary Ledger in General Ledger

How to Open a secondary Ledger

Follow the below steps to open secondary ledger for Vision Ops GL

1.Note the Secondary Leger name for Vision Operations(USA)

•    To verify this navigate to General Ledger Vision Operations(USA) Resp >Setup : Financials : Accounting Setup Manager >Accounting Setups

•     Search for Vision Operations(USA)

•     Click on update

•     Under "Secondary Ledger" section verify the ledger name as "IAS Reporting Vision Ops"

2.  Assign the GL access to Secondary ledger to any responsibility   

•    Add "General Ledger, IFRS Reporting Vision Ops"" resp to your user 

•  Now as sysadmin , Make sure "GL:Access Set" profile is set to "IAS Reporting Vision Ops" at the responsibility level
  (Assign "IAS Reporting Vision Ops" as value to "General Ledger, IFRS Reporting Vision Ops" resp for the the above profile)

3. Open the periods

•     Switch to "General Ledger, IFRS Reporting Vision Ops" resp

•     Open form "Open /Close periods and select the period to be opened.

Find AP invoice Data in AP, XLA and GL tables by using Invoice_ID

AP:

Find AP Invoice Summary Data:

SELECT ai.invoice_id,
  SUBSTR(ai.invoice_num,1,25) invoice_num,
  SUBSTR(aps.vendor_name,1,25) vendor_name,
  ai.invoice_date,
  ai.invoice_amount,
  ai.base_amount,
  SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
  SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
  SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
  ai.legal_entity_id,
  ai.org_id
FROM AP_INVOICES_ALL ai,
  AP_SUPPLIERS aps,
  AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id   = '&Invoice_ID'
AND ai.vendor_id      = aps.vendor_id(+)
and AI.VENDOR_SITE_ID = AVS.VENDOR_SITE_ID(+)

ORDER BY ai.invoice_id ASC;

Find AP Invoice Lines Summary:

SELECT LINE_NUMBER,
  LINE_TYPE_LOOKUP_CODE,
  LINE_SOURCE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  SUMMARY_TAX_LINE_ID,
  DEFERRED_ACCTG_FLAG,
  ORG_ID
FROM AP_INVOICE_LINES_ALL
where INVOICE_ID = '&Invoice_ID'
ORDER BY LINE_NUMBER ASC;

Find AP Invoice Distributions Summary:

SELECT INVOICE_ID,
  INVOICE_LINE_NUMBER,
  SUBSTR(DISTRIBUTION_LINE_NUMBER,1,8) DISTRIBUTION_LINE_NUMBER,
  SUBSTR(LINE_TYPE_LOOKUP_CODE,1,9) LINE_TYPE_LOOKUP_CODE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  BASE_AMOUNT,
  POSTED_FLAG,
  MATCH_STATUS_FLAG,
  ENCUMBERED_FLAG,
  HISTORICAL_FLAG,
  SUBSTR(DIST_CODE_COMBINATION_ID,1,15) DIST_CODE_COMBINATION_ID,
  SUBSTR(ACCOUNTING_EVENT_ID,1,15) ACCOUNTING_EVENT_ID,
  SUBSTR(BC_EVENT_ID,1,15) BC_EVENT_ID,
  SUBSTR(INVOICE_DISTRIBUTION_ID,1,15) INVOICE_DISTRIBUTION_ID,
  SUBSTR(PARENT_REVERSAL_ID,1,15) PARENT_REVERSAL_ID,
  SUBSTR(PO_DISTRIBUTION_ID,1,15) PO_DISTRIBUTION_ID,
  SUMMARY_TAX_LINE_ID,
  DETAIL_TAX_DIST_ID,
  ORG_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = '&Invoice_ID'
order by INVOICE_LINE_NUMBER,
  DISTRIBUTION_LINE_NUMBER ASC;

XLA

Find AP Invoice data in XLA_EVENTS table:

SELECT DISTINCT XE.*
FROM AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XE.ENTITY_ID
order by XE.ENTITY_ID ,
  XE.EVENT_NUMBER;

Find AP Invoice data in XLA_AE_HEADERS table:

SELECT DISTINCT XEH.*
FROM XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
order by XEH.EVENT_ID ,
  XEH.AE_HEADER_ID ASC;

Find AP Invoice data in XLA_AE_LINES table:

 select distinct XEL.*
  FROM XLA_AE_LINES XEL ,
  XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEL.APPLICATION_ID          =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
order by XEL.AE_HEADER_ID ,
  XEL.AE_LINE_NUM ASC;

General Ledger:

Find AP Invoice Data in GL_JE_BATCHES table:

SELECT DISTINCT GJB.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_BATCHES GJB,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJB.JE_BATCH_ID      = GIR.JE_BATCH_ID;

Find AP Invoice data in GL_JE_HEDAERS Table:

SELECT DISTINCT GJH.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
and AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJH.JE_HEADER_ID     = GIR.JE_HEADER_ID;

Find AP Invoice Data in GL_JE_LINES Table:

SELECT DISTINCT GLL.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_LINES GLL,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
and GLL.JE_HEADER_ID     = GIR.JE_HEADER_ID
AND GLL.JE_LINE_NUM      = GIR.JE_LINE_NUM;

Find AP Invoice Data in GL_IMPORT_REFERENCES Table:

SELECT DISTINCT GIR.*
FROM GL_IMPORT_REFERENCES GIR,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
and AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID;

Find The Account Code Combinations used for a specific AP Invoice:

SELECT DISTINCT GCC.*
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID IN
  ( SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICES_ALL AI ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEH.APPLICATION_ID          =200
  AND XEL.APPLICATION_ID          =200
  AND AI.INVOICE_ID               ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_INVOICES'
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
  AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICE_PAYMENTS_ALL AIP ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEL.APPLICATION_ID          =200
  AND XEH.APPLICATION_ID          =200
  AND AIP.INVOICE_ID              ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_PAYMENTS'
  AND XTE.LEDGER_ID               =AIP.SET_OF_BOOKS_ID
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AIP.CHECK_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT PO.CODE_COMBINATION_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID ,
    PO_DISTRIBUTIONS_ALL PO
  WHERE AID.INVOICE_ID        ='1317327'
  AND AID.PO_DISTRIBUTION_ID IS NOT NULL
  and PO.PO_DISTRIBUTION_ID   =AID.PO_DISTRIBUTION_ID
  );

Outbound interface

CREATE OR REPLACE package body APPS.cmgin097_pck AS

  Procedure cmgin097_prc(errbuf out varchar2,
                             retcode out varchar2,
                             p_date date) AS
CURSOR cmg_remit_cur IS
       SELECT
           distinct crt.remittance_id rem_id,
           nvl(cp.rda_rdp_account_number,'    ') party_number,
           substr(cp.industry_standard_number,1,7) legacy
       FROM
           cmg_parties_tbl cp,
           cmg_remittances_tbl crt,
           cmg_incentive_lookups_tbl clt
       WHERE
           cp.party_id                   = crt.party_id
           and crt.check_number IS NULL
           and crt.status_id             = clt.incentive_lookup_id
           and clt.incentive_lookup_type = 'remittance_status'
           --and clt.code                  = 'appr';
           and clt.code                    ='chk_gen'
           and trunc(remittance_date)= p_date;


CURSOR cmg_hdr_cur(p_remit_id IN NUMBER) IS
       SELECT
           distinct claim_header_id voucher
       FROM
           cmg_claim_lines_tbl
       WHERE
           remittance_id = p_remit_id;

CURSOR cmg_line_cur(p_hdr_id   IN NUMBER,
                    p_remit_id IN NUMBER) IS
       SELECT
            cph.product_number bipad,
            substr(cph.title,1,14) title,
            substr(cpl.issue_code,3,4) issue,
            cpl.cover_date cover_date,
            cp.calculation_id,
     --       decode(cp.program_based_on,'pockets',ccl.number_of_pockets,
       --            round(decode(cil.code, 'PII', draw_copies, 'NCD', draw_copies, sale_copies*NVL(map,100)/100))) sale,
         --   round(ccl.amount/(decode(cp.program_based_on,'copies',
           --                   round(decode(cil.code, 'PII', draw_copies, 'NCD', draw_copies, sale_copies*NVL(map,100)/100)),
             --                 ccl.number_of_pockets)),5) amount,
                          decode(cp.program_based_on,'pockets',ccl.number_of_pockets,
                   round(decode(cp.calculation_id,680, draw_copies, sale_copies*NVL(map,100)/100))) sale,
            round(ccl.amount/(decode(cp.program_based_on,'copies',
                              round(decode(cp.calculation_id,680, draw_copies, sale_copies*NVL(map,100)/100)),
                              ccl.number_of_pockets)),5) amount,
            cil.code trcode,
            decode(cil1.code,'est', decode(ccl.audit_adjustment_flag,'Y','AU','EP'),
                             'estrev',decode(ccl.audit_adjustment_flag,'Y','AU','ER'),
                             'estact',decode(ccl.audit_adjustment_flag,'Y','AU','RP'),
                             'adj',decode(pg.code, 'IMPACT', 'ADJ','adj'),
                             'actrev',decode(ccl.audit_adjustment_flag,'Y','AU','RR'),
                             'act',decode(pg.code, 'IMPACT', 'ACT', decode(ccl.audit_adjustment_flag,'Y','AU','RP')),
                             'RP') trxcode,-- SCR 8075 RDA AUDIT
--            decode(cil1.code,'est', 'EP','estrev','ER','estact', 'RP',
--                   'adj',decode(pg.code, 'IMPACT', 'ADJ','adj'),'actrev','RR',
--                   'act',decode(pg.code, 'IMPACT', 'ACT', 'RP'),'RP') trxcode,
            ccl.claim_line_id
       FROM
            cmg_program_groups_tbl pg,
            cmg_agreement_lines_tbl cal,
            cmg_agreement_templates_tbl cat,
            cmg_program_headers_tbl cp,
            cmg_authorized_issues_tbl cai,
            cmg_product_headers_tbl cph,
            cmg_product_lines_tbl cpl,
            cmg_claim_headers_tbl cch,
            cmg_claim_lines_tbl ccl,
            cmg_incentive_lookups_tbl cil,
            cmg_incentive_lookups_tbl cil1
       WHERE
            pg.program_group_id            = cp.program_group_id
            and cil1.incentive_lookup_type = 'transaction_codes'
            and cil1.incentive_lookup_id   = ccl.transaction_code_id
            and cil.incentive_lookup_type  = 'credit_memo'
            and cil.incentive_lookup_id    = cp.credit_memo_id
            and cp.program_header_id       = cat.program_header_id
            and cat.agreement_template_id  = cal.agreement_template_id
            and cal.agreement_line_id      = ccl.agreement_line_id
            and cph.product_header_id      = cpl.product_header_id
            and cpl.product_line_id        = cai.product_line_id
            and cai.authorized_issue_id    = ccl.authorized_issue_id
            and ccl.amount                != 0
            and ccl.remittance_id          = p_remit_id
            and ccl.claim_header_id        = cch.claim_header_id
            and cch.remittance_flag        = 'Y'
            and cch.claim_header_id        = p_hdr_id
            and cp.payment_type           ='credits';

/*       GROUP BY
            cph.product_number,
            cph.title,
            cpl.issue_code,
            cpl.on_sale_date,
            cpl.cover_date,
            ccl.amount,
            cil.code;
*/
    V_FILEHANDLE1 UTL_FILE.FILE_TYPE;
    V_FILEHANDLE2 UTL_FILE.FILE_TYPE;
    v_file        VARCHAR2(30);
    v_file_roar   VARCHAR2(250);
    v_path1       VARCHAR2(100);
    v_path2       VARCHAR2(100);
    v_header      number;
    v_pgcode      varchar2(10);
    LINE1         VARCHAR2(500);
    v_logfile     VARCHAR2(25);
    v_req_id      NUMBER DEFAULT(FND_GLOBAL.CONC_REQUEST_ID);
    v_request_id  NUMBER;
    v_userid      NUMBER DEFAULT(FND_GLOBAL.USER_ID);
    v_username    VARCHAR2(20);
    v_file_amt    number:=0;
    v_trcode      VARCHAR2(10);
    v_po_number   VARCHAR2(20);
    v_subject       VARCHAR2(100);
    v_report        VARCHAR2(100);
    v_email_address VARCHAR2(100);

BEGIN

      SELECT user_name,email_address
      INTO   v_username,v_email_address
      FROM  fnd_user
      WHERE user_id = v_userid;

 FND_PROFILE.GET('CMG_INCEN_OUTPUT_TO_ROAR_PATH',v_path1);
 FND_PROFILE.GET('CMG_OUTPUTFILE_PATH',v_path2);

 v_file := 'INCENCR'||to_char(sysdate,'YYYYMMDDHH24MISS')||'.DAT';
 v_logfile := 'o'||to_char(v_req_id)||'.out'; -- Log file

 V_FILEHANDLE1 := UTL_FILE.FOPEN(v_path1,v_file,'w');
 V_FILEHANDLE2 := UTL_FILE.FOPEN(v_path2,v_logfile,'w');

 UTL_FILE.PUT_LINE(V_FILEHANDLE2,'-----------------'
                   ||'CMG Incentive Credits to Roar ---------------');
 UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');
 UTL_FILE.PUT_LINE(V_FILEHANDLE2,'Current System Time is : '
                   ||TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'));
 UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');
 UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Incentive Credits File Name is '||v_file);
 UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');

/* UTL_FILE.PUT_LINE(V_FILEHANDLE2,'Legacy#   Voucher   Bipad  Title             '
                   ||'Issue        Amount');  */

                  -- UTL_FILE.PUT_LINE(V_FILEHANDLE2,'date'||p_date);

 FOR rmt in cmg_remit_cur LOOP   -- Start Remittance Loop
    FOR hdr in cmg_hdr_cur(rmt.rem_id) LOOP  -- Header Loop
       FOR lin in cmg_line_cur (hdr.voucher,rmt.rem_id) LOOP  -- Line Loop

        -- if lin.trcode in ('PII', 'NCD') then
         --    v_trcode := lin.trcode;
         -- else
          --   v_trcode := lin.trcode||lin.trxcode;
         -- end if;

         -- modified as part of SCR 8339

         if lin.calculation_id =680 then
             v_trcode := lin.trcode;
          else
             v_trcode := lin.trcode||lin.trxcode;
          end if;

        -- if lin.trxcode='AUD' then
        --  v_po_number := rmt.party_number||hdr.voucher||'AUD';
        -- else

         v_po_number :=rmt.party_number||hdr.voucher;
        -- end if;

          LINE1 := rmt.legacy  ||','||
                   v_trcode    ||','||
                   v_po_number ||','||
                   lin.bipad   ||','||
                   lin.issue   ||','||
                   -1*lin.sale ||','||
                   lin.amount  ||',"'||
                   lin.title   ||'",'||
                   lin.cover_date||','||
                   lin.claim_line_id;

          UTL_FILE.PUT_LINE(V_FILEHANDLE1,LINE1);
/*          UTL_FILE.PUT_LINE(V_FILEHANDLE2,rpad(rmt.legacy,10,' ')
                            ||rpad(hdr.voucher,10,' ')
                            ||rpad(lin.bipad,7,' ')
                            ||rpad(lin.title,18,' ')
                            ||rpad(lin.issue,10,' ')
                            ||to_char(lin.amount,'9999999.99'));  */

          v_file_amt := v_file_amt + round(lin.amount*lin.sale,2);

          -- Update Remittance Table

          UPDATE cmg_remittances_tbl
          SET    status_id = (select incentive_lookup_id
                        from cmg_incentive_lookups_tbl
                        where code = 'cr_procd'
                        and incentive_lookup_type = 'remittance_status')
          WHERE  remittance_id = rmt.rem_id;




       END LOOP; -- Line Loop
    END LOOP; --Header Loop

-- Update Remittance Table
--    UPDATE cmg_remittances_tbl
--    SET    status_id = (select incentive_lookup_id
--                        from cmg_incentive_lookups_tbl
--                        where code = 'cr_procd'
--                        and incentive_lookup_type = 'remittance_status')
--    WHERE  remittance_id = rmt.rem_id;

 END LOOP;  -- Remittance Loop

 UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Total Credit Amount is '||(v_file_amt*-1));
 UTL_FILE.fclose(V_FILEHANDLE1); -- Close the Credit File
 UTL_FILE.fclose(V_FILEHANDLE2); -- Close Log File

v_subject := 'Roar Interface Credit Memo Flat File';


 IF v_email_address IS NOT NULL THEN
    v_request_id := Fnd_Request.SUBMIT_REQUEST(
                           'CMGABC',
                           'CMGABC_EMAIL',
                           'Send E Mail from Server',
                           '',
                           FALSE,
                           v_path1,
                           v_file,
                           v_subject,
                           v_email_address );
    COMMIT;
 ELSE
    UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Could not Find Email Address in the User setup ,Please Contact User Setup' );
 END IF;

end cmgin097_prc;
end cmgin097_pck;
/

PO INTERFACING

Headers

INTERFACE_HEADER_ID

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

1

5

ORIGINAL

204

STANDARD

USD

Stock, Ms. Pat

3M Health Care

CORP HQ

M1- Seattle Mfg

V1- New York City

APPROVED

UPS

Origin

Due

2

5

ORIGINAL

204

STANDARD

USD

Stock, Ms. Pat

3M Health Care

CORP HQ

M1- Seattle Mfg

V1- New York City

APPROVED

UPS

Origin

Paid

Lines

INTERFACE_HEADER_ID

INTERFACE_LINE_ID

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

PROMISED_DATE

NEED_BY_DATE

ACTION

1

1

1

1

Goods

AS54999

Sentinel Standard Desktop - Rugged

2,155

Ea

1,000

10

M1

M1- Seattle Mfg

100

08/27/2017 13:52:49

09/16/2017 13:52:49

1

2

2

1

Goods

AS54999

Sentinel Standard Desktop - Rugged

2,155

Ea

500

10

M1

M1- Seattle Mfg

100

08/27/2017 13:54:08

09/16/2017 13:54:08

2

3

2

1

Goods

AS54999

Sentinel Standard Desktop - Rugged

2,155

Ea

200

10

M1

M1- Seattle Mfg

100

08/27/2017 13:56:31

09/16/2017 13:56:31

2

4

1

1

Goods

AS54999

Sentinel Standard Desktop - Rugged

2,155

Ea

500

10

M1

M1- Seattle Mfg

100

08/27/2017 13:59:48

09/16/2017 13:59:48

Distributions

INTERFACE_HEADER_ID	INTERFACE_LINE_ID	INTERFACE_DISTRIBUTION_ID	SET_OF_BOOKS_ID	DESTINATION_ORGANIZATION_ID	ORG_ID	ORDERED_QUANTITY	CHARGE_ACCOUNT_ID
1	1	1	1	207	204	1,000	13,401
1	2	2	1	207	204	500	13,401
2	3	3	1	207	204	200	13,401
2	4	4	1	207	204	500	13,401

CREATE OR REPLACE PROCEDURE PO_INTERFACE(ERRBUF  OUT VARCHAR2, RETCODE OUT VARCHAR2) AS
CURSOR C1 IS SELECT * FROM XX_PO_HEADERS;
CURSOR C2(P_INTERFACE_HEADER_ID NUMBER)  IS SELECT * FROM XX_PO_LINES WHERE INTERFACE_HEADER_ID=P_INTERFACE_HEADER_ID;
CURSOR C3(P_INTERFACE_LINE_ID NUMBER) IS SELECT * FROM XX_PO_DISTRIBUTIONS WHERE INTERFACE_LINE_ID=P_INTERFACE_LINE_ID;
L_VENDOR_ID VARCHAR2(100);
L_ITEM      VARCHAR2(150);
L_FLAG     VARCHAR2(4) DEFAULT 'A';
L_MSG      VARCHAR2(200);
L_SITE_CODE     VARCHAR2(100);
L_CURR_CODE     VARCHAR2(10);
L_ORG_ID     NUMBER(6);
L_LOCATION_CODE VARCHAR2(100);
RECORD_COUNT NUMBER(20):=0;
V_SET_OF_BOOKS_ID NUMBER(10);
L_LINE_TYPE   VARCHAR2(100);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
FOR I IN C1 LOOP
RECORD_COUNT:=RECORD_COUNT+1;
BEGIN
-- VENDOR_NAME VALIDATION
SELECT VENDOR_ID
INTO   L_VENDOR_ID
FROM APPS.PO_VENDORS
WHERE VENDOR_NAME = I.VENDOR_NAME;
EXCEPTION
WHEN OTHERS THEN
L_FLAG        := 'E';
L_MSG       := 'PLEASE CHECK THE VENDOR_ID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
 --VENDOR SITE CODE  VALIDATION
 --================================
BEGIN
SELECT  DISTINCT(VENDOR_SITE_CODE)
INTO    L_SITE_CODE
FROM    PO_VENDOR_SITES_ALL
WHERE   VENDOR_SITE_CODE =I.VENDOR_SITE_CODE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG        := 'E';
L_MSG         := 'PLEASE CHECK THE VENDOR SITE CODE';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;

--CURRENCY CODE VALIDATION
--===========================
BEGIN
SELECT CURRENCY_CODE
INTO   L_CURR_CODE
FROM   FND_CURRENCIES
WHERE  CURRENCY_CODE =I.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG        := 'E';
L_MSG         := 'CURRENCY CODE IS INVALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--OPERATING UNIT ID VALIDATION
--==============================
BEGIN
SELECT ORGANIZATION_ID
INTO   L_ORG_ID
FROM   HR_OPERATING_UNITS
WHERE  ORGANIZATION_ID = I.ORG_ID;
EXCEPTION
WHEN OTHERS THEN
L_FLAG        := 'E';
L_MSG         := 'INVALID ORGANIZATION ID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;

--SHIP TO LOCATION VALIDATION
--===============================
BEGIN
SELECT LOCATION_CODE
INTO   L_LOCATION_CODE
FROM   HR_LOCATIONS
WHERE  LOCATION_CODE=I.SHIP_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
L_FLAG        := 'E';
L_MSG         := 'INVALID SHIP TO LOCATION';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
-- IF ALL THE COLUMNS ARE GOOD THEN INSERT INTO INTERFACE TABLE
IF L_FLAG != 'E' THEN
INSERT INTO PO_HEADERS_INTERFACE
   (
   INTERFACE_HEADER_ID-- WE GET THIS VALUE FROM THE STANDARD SEQUENCE
  ,BATCH_ID-- ITS ORIGINAL
  ,ACTION--
   ,ORG_ID
  ,DOCUMENT_TYPE_CODE
  ,CURRENCY_CODE
  ,AGENT_NAME -- BUYER NAME
  ,VENDOR_NAME--SUPPLIER
  ,VENDOR_SITE_CODE-- SUPPLIER SITE CODE
  ,SHIP_TO_LOCATION
  ,BILL_TO_LOCATION
  ,CREATION_DATE
  ,APPROVAL_STATUS
  ,APPROVED_DATE
  ,FREIGHT_TERMS
)
VALUES
(PO_HEADERS_INTERFACE_S.NEXTVAL  -- HEADER_ID
  ,I.BATCH_ID    -- UNIQUE INDENTIFIER
  ,I.ACTION  
  ,L_ORG_ID                                
  ,I.DOCUMENT_TYPE_CODE   --TYPE OF DOCUMENT (STANDARD, PLANNED,BLANKET ETC)                      
  ,L_CURR_CODE             --CURRECY CODE                
  ,I.AGENT_NAME             --BUYER  
  ,I.VENDOR_NAME            --SUPPLIER
  ,L_SITE_CODE              --SUPPLIER SITE CODE
  ,I.SHIP_TO_LOCATION       --SHIP_TO_LOCATION ADDRESS
  ,I.BILL_TO_LOCATION       -- BILL TO LOCATION ADDRESS
  ,SYSDATE-10                          
  ,I.APPROVAL_STATUS
  ,SYSDATE                            
  ,I.FREIGHT_TERMS         --FREIGHT TERMS
 );
COMMIT;
END IF;

FOR J IN C2(I.INTERFACE_HEADER_ID)  LOOP
L_FLAG := 'A';
--ITEM VALIDATION
--=======================
BEGIN
SELECT DISTINCT(SEGMENT1)
INTO   L_ITEM
FROM   MTL_SYSTEM_ITEMS_B
WHERE  SEGMENT1        = J.ITEM;
--AND    ORGANIZATION_ID = FND_PROFILE.VALUE('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
    L_FLAG        := 'E';
    L_MSG       := 'ITEM IS NOT VALID ITEM';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;

--LINE_TYPE VALIDATIOM
--=============================
BEGIN
SELECT DISTINCT(LINE_TYPE) INTO L_LINE_TYPE FROM PO_LINE_TYPES_B WHERE LINE_TYPE=J.LINE_TYPE;
EXCEPTION
WHEN OTHERS THEN
    L_FLAG        := 'E';
    L_MSG       := 'LINE_TYPE IS NOT VALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--END OF THE ITEM VALIDATION
 IF  L_FLAG != 'E' THEN
 INSERT INTO PO.PO_LINES_INTERFACE
 ( INTERFACE_LINE_ID
  ,INTERFACE_HEADER_ID
  ,LINE_NUM
  ,SHIPMENT_NUM
  ,LINE_TYPE
  ,ITEM
  ,ITEM_DESCRIPTION
  ,ITEM_ID
  ,UOM_CODE
  ,QUANTITY
  ,UNIT_PRICE
  ,SHIP_TO_ORGANIZATION_CODE
  ,SHIP_TO_LOCATION
  ,NEED_BY_DATE
  ,PROMISED_DATE
  ,LIST_PRICE_PER_UNIT
  ,ACTION
)
VALUES
(  PO_LINES_INTERFACE_S.NEXTVAL
  ,PO_HEADERS_INTERFACE_S.CURRVAL
  ,J.LINE_NUM
  ,J.SHIPMENT_NUM
  ,J.LINE_TYPE
  ,L_ITEM
  ,J.ITEM_DESCRIPTION
  ,J.ITEM_ID
  ,J.UOM_CODE
  ,J.QUANTITY,
   J.UNIT_PRICE,
   J.SHIP_TO_ORGANIZATION_CODE,
   J.SHIP_TO_LOCATION,
   J.NEED_BY_DATE,
   J.PROMISED_DATE,
   J.LIST_PRICE_PER_UNIT,
   J.ACTION);
COMMIT;
END IF;

FOR K IN C3(J.INTERFACE_LINE_ID)  LOOP
--SET OF BOOKS VALIDATION
--===========================
BEGIN
SELECT SET_OF_BOOKS_ID INTO V_SET_OF_BOOKS_ID FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=K.SET_OF_BOOKS_ID;
EXCEPTION
WHEN OTHERS THEN
    L_FLAG        := 'E';
    L_MSG       := 'SET_OF_BOOKS_ID IS NOT VALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;

INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
DESTINATION_ORGANIZATION_ID,
ORG_ID,
CHARGE_ACCOUNT_ID,
QUANTITY_ORDERED)
VALUES
(PO_HEADERS_INTERFACE_S.CURRVAL
,PO_LINES_INTERFACE_S.CURRVAL
,PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
V_SET_OF_BOOKS_ID,
K.DESTINATION_ORGANIZATION_ID,
K.ORG_ID,
K.CHARGE_ACCOUNT_ID,
K.ORDERED_QUANTITY);
END LOOP;
END LOOP;
END LOOP;
IF  L_FLAG !='E'
THEN FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'RECORDS SUCCESSFULLY INTERFACED TO PO');
END IF;
END PO_INTERFACE;
/


In order to import the Purchase orders in order to find the errors during the import of purchase orders and Purge the records that are accepted and got interfaced to PO following Standard programs need to be executed

========================================================================
http://oracleapps88.blogspot.com/2011/08/po-interface-tables.html

PO INTERFACES

                            PO INTERFACES

The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.

Import Standard Purchase Orders

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

PO

Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

Validations:

Header:

Check if OU name is valid

Check if Supplier is valid

Check if Supplier site is valid

Check if buyer is valid

Check if Payment term is valid

Check if Bill to and ship to are valid

Check if FOB, freight terms are valid

Lines:

Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid

General:

Check for duplicate records in interface tables

Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:

PO_HEADERS_INTERFACE:

INTERFACE_HEADER_ID(PO_HEADERS_INTERFACE_S.NEXTVAL), 

BATCH_ID, 

ORG_ID, 

INTERFACE_SOURCE_CODE, 

ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), 

GROUP_CODE, 

DOCUMENT_TYPE_CODE, 

PO_HEADER_ID (NULL), 

RELEASE_ID, 

RELEASE_NUM, 

CURRENCY_CODE, 

RATE, AGENT_NAME, 

VENDOR_ID, 

VENDOR_SITE_ID, 

SHIP_TO_LOCATION, 

BILL_TO_LOCATION,

PAYMENT_TERMS

PO_LINES_INTERFACE:

INTERFACE_LINE_ID, 

INTERFACE_HEADER_ID,

LINE_NUM, 

SHIPMENT_NUM,

ITEM, 

REQUISITION_LINE_ID, 

UOM, 

UNIT_PRICE, 

FREIGHT_TERMS, 

FOB

PO_DISTRIBUTIONS_INTERFACE:

INTERFACE_LINE_ID, 

INTERFACE_HEADER_ID, 

INTERFACE_DISTRIBUTION_ID, 

DISTRIBUTION_NUM, 

QUANTITY_ORDERED, 

QTY_DELIVERED, 

QTY_BILLED, 

QTY_CANCELLED, 

DELIVER_TO_LOCATION_ID, 

DELIVER_TO_PERSON_ID,

 SET_OF_BOOKS, 

CHARGE_ACCT, 

AMOUNT_BILLED.

Staging Tables

CREATE TABLE xx_po_header_stg(

interface_header_id NUMBER

,batch_id NUMBER

,org_id NUMBER

,action VARCHAR2(25)

,document_type_code VARCHAR2(25)

,currency_code VARCHAR2(15)

,AGENT_ID NUMBER

,full_name VARCHAR2(240)

,vendor_name VARCHAR2(240)

,vendor_site_code VARCHAR2(15)

,ship_to_location VARCHAR2(60)

,bill_to_location VARCHAR2(60)

,approval_status VARCHAR2(25)

,freight_carrier VARCHAR2(25)

,fob VARCHAR2(25)

,freight_terms VARCHAR2(25))

CREATE TABLE xx_po_line_stg

(

interface_header_id NUMBER

,interface_line_id NUMBER

,line_num NUMBER

,shipment_num NUMBER

,line_type VARCHAR2(25)

,item VARCHAR2(1000)

,item_description VARCHAR2(240)

,item_id NUMBER

,uom_code VARCHAR2(3)

,quantity NUMBER

,unit_price NUMBER

,ship_to_organization_code VARCHAR2(3)

,ship_to_location VARCHAR2(60)

,list_price_per_unit NUMBER)

CREATE TABLE xx_po_distribution_stg

(interface_header_id NUMBER,

interface_line_id NUMBER,

interface_distribution_id NUMBER,

org_id NUMBER,

quantity_ordered NUMBER,

destination_organization_id NUMBER,

set_of_books_id NUMBER,

charge_account_id VARCHAR2(2000),

distribution_num NUMBER);

Creation Of The Package.

CREATE OR REPLACE PACKAGE XXAK_PO_IMP_PKG IS

PROCEDURE XXAK_PO_IMP_PRC(Errbuf OUT VARCHAR2, Retcode OUT number) ;

END;

/

Creation Of The Procedure.

CREATE OR REPLACE PACKAGE BODY XXAK_PO_IMP_PKG

IS

PROCEDURE XXAK_PO_IMP_PRC (errbuf OUT VARCHAR2, retcode OUT number)

IS

CURSOR cur_head

IS

SELECT *

FROM xx_po_header_stg;

CURSOR cur_line (p_interface_header_id NUMBER)

IS

SELECT *

FROM xx_po_line_stg where interface_header_id=p_interface_header_id;

CURSOR cur_dist (p_interface_line_id NUMBER)

IS

SELECT *

FROM xx_po_distribution_stg where interface_line_id=p_interface_line_id;

lv_vendor_id NUMBER (10);

lv_agent_id NUMBER(10);

lv_itemid NUMBER;

lv_site_code VARCHAR2 (100);

lv_lookup_code VARCHAR2(25);

lv_curr_code VARCHAR2 (10);

lv_org_id NUMBER (6);

BEGIN

BEGIN

SELECT organization_id

INTO lv_org_id

FROM hr_operating_units

WHERE NAME LIKE 'Vision Operations';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid org_id');

END;

BEGIN

FOR var1 IN cur_head

LOOP

BEGIN

SELECT vendor_id

INTO lv_vendor_id

FROM po_vendors

WHERE vendor_name=var1.vendor_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid vendor_id');

END;

BEGIN

SELECT vendor_site_code

INTO lv_site_code

FROM po_vendor_sites_all

WHERE vendor_site_code = var1.vendor_site_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'invalid vendor_site_code');

END;

BEGIN

SELECT currency_code

INTO lv_curr_code

FROM fnd_currencies

WHERE currency_code = var1.currency_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid currency_code');

END;

BEGIN

SELECT DOCUMENT_TYPE_CODE

INTO lv_lookup_code

FROM PO_DOCUMENT_TYPES

WHERE DOCUMENT_TYPE_CODE = var1.DOCUMENT_TYPE_CODE;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid typecode');

END;

BEGIN

SELECT person_id

INTO lv_agent_id

FROM PER_ALL_PEOPLE_F

WHERE full_name= var1.full_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid Buyer');

END;

BEGIN

INSERT INTO po_headers_interface

(interface_header_id

, batch_id

, org_id

,action

, document_type_code

, currency_code

,agent_id

, agent_name

, vendor_name

, vendor_site_code

, ship_to_location

, bill_to_location

,approval_status

, freight_terms

,fob

,freight_carrier

,created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(PO_HEADERS_INTERFACE_S.NEXTVAL

, var1.batch_id

, lv_org_id

,var1.action

, var1.document_type_code

, var1.currency_code

, lv_agent_id

,var1.full_name

,var1.vendor_name

, var1.vendor_site_code

, var1.ship_to_location

, var1.bill_to_location

, var1.approval_status

, var1.freight_terms

,var1.fob

,var1.freight_carrier

, -1

, SYSDATE

, SYSDATE

, -1

);

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into headers int');

END;

FOR var2 IN cur_line (var1.interface_header_id)

LOOP

BEGIN

SELECT INVENTORY_ITEM_ID

INTO lv_itemid

FROM mtl_system_items_b

WHERE segment1 = var2.item

AND organization_id =lv_org_id;

EXCEPTION

WHEN OTHERS

THEN

-- lv_item := NULL;

fnd_file.put_line (fnd_file.LOG, 'invalid item');

END;

BEGIN

INSERT INTO po_lines_interface

(interface_line_id

, interface_header_id

, line_num

, shipment_num

, line_type

,item

, item_description

, item_id

, uom_code

, quantity

, unit_price

,ship_to_organization_code

, ship_to_location

,NEED_BY_DATE

-- ,PROMISED_DATE

, list_price_per_unit

, created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(PO_LINES_INTERFACE_S.NEXTVAL

,PO_HEADERS_INTERFACE_S.CURRVAL

, var2.line_num

, var2.shipment_num

, var2.line_type

, var2.item

, var2.item_description

, var2.item_id

,var2.uom_code

, var2.quantity

, var2.unit_price

,var2.ship_to_organization_code

,var2.ship_to_location

,SYSDATE

-- ,SYSDATE

, var2.list_price_per_unit

, -1

,SYSDATE

, SYSDATE

, -1

);

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into lines int');

END;

for var3 in cur_dist(var2.interface_line_id)

LOOP

BEGIN

INSERT INTO po_distributions_interface (

interface_header_id ,

interface_line_id ,

interface_distribution_id ,

org_id ,

quantity_ordered ,

destination_organization_id ,

set_of_books_id ,

charge_account_id ,

distribution_num

, created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(

PO_HEADERS_INTERFACE_S.CURRVAL,

PO_LINES_INTERFACE_S.CURRVAL,

PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL ,

lv_org_id ,

var3.quantity_ordered ,

var3.destination_organization_id ,

var3.set_of_books_id ,

var3.charge_account_id ,

var3.distribution_num

, -1

,SYSDATE

, SYSDATE

, -1 );

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into dist int');

END;

END LOOP;

END LOOP;

END LOOP;

END;

COMMIT;

END XXAK_PO_IMP_PRC;

END XXAK_PO_IMP_PKG ;

/