SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number

      ,aia.invoice_id

  ,aia.invoice_num

  ,aia.invoice_amount

  ,distribution_line_number

  ,aida.accrual_posted_flag

  ,decode(aida.match_status_flag,

              'A', 'Validated',

              'N', 'Never Validated',

              'T', 'Needs Revalidation',

              'S', 'Stopped') "Invoice Validation Status"

  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),

              'S', 'Selected for Accounting',

              'P', 'Partial',

              'N', 'Unaccounted',

              'Y', 'Accounted',

              'Unaccounted') "Invoice Accounting Status",

      aida.invoice_distribution_id,

      aida.accounting_event_id,

      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,

                                               aia.invoice_amount,

                                               aia.payment_status_flag,

                                               aia.invoice_type_lookup_code)"Invoice Approval Status",

      decode(aida.posted_flag,

              'Y', 'Posted',

              'N', 'Not Posted',

              'Not Posted') "Invoice Posting Status"   

FROM ap_checks_all aca

    ,ap_invoice_payments_all aipa

,ap_invoices_all aia

,ap_suppliers asa

,ap_invoice_distributions_all aida

WHERE aca.check_id = aipa.check_id

AND aipa.invoice_id = aia.invoice_id

AND aia.vendor_id = asa.vendor_id

AND aida.invoice_id = aia.invoice_id

AND aca.check_number = <pass check number>

Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id

              ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND ailf.invoice_line_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

   --AND aif.source = 'ERS'

           AND aif.status = 'REJECTED'

        UNION

        SELECT aif.invoice_id

      ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND aif.invoice_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICES_INTERFACE'

           --AND aif.source = 'ERS'

   AND aif.status = 'REJECTED'

Query to get AP Invoice details in R12

SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.INVOICE_ID

  ,AP_INVOICES_ALL.INVOICE_NUM

  ,AP_INVOICES_ALL.INVOICE_CURRENCY_CODE

  ,AP_INVOICES_ALL.VENDOR_ID

  ,AP_INVOICES_ALL.VENDOR_SITE_ID

  ,AP_INVOICES_ALL.INVOICE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.INVOICE_RECEIVED_DATE

  ,AP_INVOICES_ALL.CREATION_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID

  ,PO_HEADERS_ALL.SEGMENT1

  ,PO_LINES_ALL.LINE_NUM

  ,PO_HEADERS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.LAST_UPDATE_DATE

  ,PO_HEADERS_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.ITEM_DESCRIPTION

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.WFAPPROVAL_STATUS

  ,AP_INVOICES_ALL.PAYMENT_STATUS_FLAG

  ,AP_INVOICES_ALL.CANCELLED_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE

  ,PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID

  ,AP_INVOICES_ALL.TERMS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE

  ,AP_INVOICES_ALL.EXCHANGE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICES_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE

  ,AP_INVOICES_ALL.EXCHANGE_RATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICES_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED

  ,AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT

  ,AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT

  ,CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL 

        THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

   END

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE

  ,PO_VENDORS.RECEIPT_REQUIRED_FLAG

  ,PO_VENDORS.INSPECTION_REQUIRED_FLAG

  ,PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG

  ,PO_VENDORS.LAST_UPDATE_DATE

  ,AP_INVOICES_ALL.SOURCE

  ,AP_INVOICES_ALL.PROJECT_ID

  ,AP_INVOICES_ALL.TASK_ID

  ,AP_INVOICES_ALL.EXPENDITURE_TYPE

  ,AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID

  ,AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE

  ,AP_INVOICES_ALL.PAYMENT_CROSS_RATE

  ,AP_INVOICES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.LINE_NUMBER

  ,AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICE_LINES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.LEGAL_ENTITY_ID

  FROM AP_INVOICE_DISTRIBUTIONS_ALL

      ,AP_INVOICE_LINES_ALL

  ,AP_INVOICES_ALL

  ,PO_LINES_ALL

  ,PO_HEADERS_ALL

  ,PO_VENDORS

  ,PO_VENDOR_SITES_ALL

  ,PO_DISTRIBUTIONS_ALL

  ,(SELECT AP_INVOICES_ALL.INVOICE_ID 

      FROM AP_INVOICES_ALL 

WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/Y YYY HH24:MI:SS') 

UNION 

SELECT AP_INVOICE_LINES_ALL.INVOICE_ID 

  FROM AP_INVOICE_LINES_ALL 

WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM /DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID 

  FROM AP_HOLDS_ALL 

WHERE AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL 

   AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/D D/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL

      ,AP_INVOICE_DISTRIBUTIONS_ALL

  ,PO_HEADERS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID

UNION 

    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, PO_LINES_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID 

   AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/DD/YYYY H H24:MI:SS') 

   ) TEMP 

 WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID 

   AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+) 

   AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICE_LINES_ALL.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER = AP_INVOICE_LINES_ALL.LINE_NUMBER 

Query To Fetch AP Invoice Details From SO Number(Doc ID 2949013.1)

SELECT dh.source_order_number

      ,df.source_line_number as so_line_number

  ,df.fulfill_line_number 

  ,ddr.doc_user_key as po_number

  ,ai.invoice_num

  ,ail.quantity_invoiced

  ,ail.amount

  ,ail.line_number as ap_invoice_line_number

  FROM fusion.ap_invoices_all ai

      ,fusion.ap_invoice_lines_all ail

  ,fusion.doo_document_references ddr

  ,fusion.doo_headers_all dh

  ,fusion.doo_fulfill_lines_all df

 WHERE ai.invoice_id = ail.invoice_id

   AND dh.source_order_number = <sales_order_number>

   AND ddr.doc_id = TO_CHAR(ai.po_header_id)

   AND ddr.fulfill_line_id = df.fulfill_line_id

   AND df.header_id = dh.header_id

Query to get AP Invoice Distribution to Inventory Material Distribution Data

SELECT mmt.transaction_date

          ,(SELECT ml.meaning

              FROM mfg_lookups ml

             WHERE ml.lookup_Type = 'CST_ACCOUNTING_LINE_TYPE'

               AND ml.lookup_code = mta.accounting_line_type

                ) accounting_type

          ,gl_code.concatenated_segments account, mtt.transaction_type_name transaction_type

          ,(SELECT po_hdr.segment1 

              FROM po_headers_all po_hdr 

             WHERE po_hdr.po_header_id = po_dist.po_header_id) transaction_source

          ,mmt.transaction_uom uom

          ,mta.primary_quantity 

          ,mta.base_transaction_value

          ,mmt.transaction_id, mmt.transaction_type_id, items.segment1 item, mmt.inventory_item_id, mmt.organization_id

          ,gl_code.code_combination_id

      FROM ap_invoice_distributions_all inv_dist

          ,po_distributions_all po_dist

          ,rcv_transactions rcv_trx

          ,mtl_material_transactions mmt

          ,mtl_transaction_types mtt

          ,mtl_transaction_accounts mta

          ,gl_code_combinations_kfv gl_code

          ,mtl_system_items_b items

     WHERE 1=1

       --AND inv_dist.po_distribution_id = 10536072

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id

       AND po_dist.po_header_id = rcv_trx.po_header_id

       AND po_dist.po_line_id = rcv_trx.po_line_id

       AND po_dist.line_location_id = rcv_trx.po_line_location_id

       AND po_dist.po_distribution_id = rcv_trx.po_distribution_id

       AND transaction_type = 'DELIVER'

       AND mmt.rcv_transaction_id = rcv_trx.transaction_id

       AND mmt.transaction_type_id = mtt.transaction_type_id

       AND mmt.transaction_id = mta.transaction_id

       AND mta.reference_account = gl_code.code_combination_id

       AND mmt.inventory_item_id = items.inventory_item_id

       AND mmt.organization_id = items.organization_id

Script to update Supplier pay_group_lookup_code in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.pay_group_lookup_code := 'Employee';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Type(ap_suppliers.vendor_type_lookup_code) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.vendor_type_lookup_code := 'LOCAL';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Tax Payer ID(AP_SUPPLIERS.NUM_1099) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.jgzz_fiscal_code := 'X123456789X';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index     => v_index

                ,p_encoded       => 'F'

,p_data          => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

API to Create Online Accounting for AP Payment in Oracle APPS R12 (ap_drilldown_pub_pkg.payment_online_accounting)

    PROCEDURE xx_payment_accounting(errbuf OUT VARCHAR2

   ,retcode           OUT VARCHAR2

   ,p_invoice_id    IN  NUMBER

   )

IS

CURSOR c1 

IS

SELECT DISTINCT aca.check_id, aca.check_number        

              FROM ap_invoices_all xlpi

      ,ap_invoice_payments_all aipa

  ,ap_checks_all aca

             WHERE xlpi.invoice_id   = aipa.invoice_id

               AND xlpi.invoice_id   = pin_invoice_id

               AND aipa.check_id     = aca.check_id

               AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';

   -- This condition is to pick invoices which are paid and unaccounted

   ;  

ln_processed_cnt    NUMBER := 0;

ln_failed_cnt       NUMBER := 0;  

lv_error_buf        VARCHAR2(4000);

ln_retcode          NUMBER; 

BEGIN

FOR rec_create_pmt_accounting IN c1

LOOP

BEGIN

fnd_file.put_line(fnd_file.log,'Check ID: '||rec_create_pmt_accounting.check_id);

ln_retcode   := NULL;

lv_error_buf := NULL;

ap_drilldown_pub_pkg.payment_online_accounting(p_check_id           => rec_create_pmt_accounting.check_id

  ,p_accounting_mode    => 'P'      

  ,p_errbuf             => lv_error_buf

  ,p_retcode            => ln_retcode

  ,p_calling_sequence   => 'XX_PAYMENT_ACCOUNTING'

  );

IF ln_retcode = 0 

THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounted Sucessfully'

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 1 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in WARNING. Errbuf : '

||lv_error_buf

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 2 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

);       

ln_failed_cnt := ln_failed_cnt +1;          

ELSE

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

||' Retcode: '

||ln_retcode

);

ln_failed_cnt := ln_failed_cnt +1;

END IF; 

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: ' 

||rec_create_pmt_accounting.check_id

||'. Payment Accounting failed with Unhandled Exception. Error Message:' 

||SQLERRM);

ln_failed_cnt := ln_failed_cnt + 1;

END;

END LOOP;

fnd_file.put_line ( fnd_file.log,'Processed Record Count: '

||ln_processed_cnt

||' Failed Record Count: '

||ln_failed_cnt);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line ( fnd_file.log,'Main Error in xx_payment_accounting. Error Message: '||SQLERRM);

END xx_payment_accounting;

Script to Update Liability Account, Prepay Account, Invoice Withholding Tax Group and Tax Classification Code at Supplier Site Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA

(

  VENDOR_NAME                    VARCHAR2(250 BYTE),

  VENDOR_ID                      NUMBER,

  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),

  ORG_ID                         NUMBER,

  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),

  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),

  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),

  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),

  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),

  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),

  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),

  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),

  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),

  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),

  TAX_APPLICABILITY              VARCHAR2(80 BYTE),

  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),

  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),

  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),

  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)

);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;

API Script:

-----------------------------------------------------------------------------------------------

DECLARE

    v_api_version              NUMBER;

    v_init_msg_list            VARCHAR2(200);

    v_commit                    VARCHAR2(200);

    v_validation_level          NUMBER;

    x_return_status            VARCHAR2(200);

    x_msg_count                NUMBER;

    v_vendor                    NUMBER;

    x_msg_data                  VARCHAR2(200);

    l_vendor_site_rec          apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

    l_existing_vendor_site_rec ap_supplier_sites_all%rowtype;

    l_vendor_site_id            NUMBER;

l_liability_ccid gl_code_combinations_kfv.code_combination_id%TYPE;

l_prepay_ccid gl_code_combinations_kfv.code_combination_id%TYPE;

l_ap_awt_group_id ap_awt_groups.group_id%TYPE;

l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;

    v_calling_prog              VARCHAR2(200);

    

    l_msg                      VARCHAR2(200);

    l_status_flag         VARCHAR(1):='N';

    l_status_msg     VARCHAR2(2000) := NULL;

        

    CURSOR c 

IS

SELECT DISTINCT 

            vendor_id

               ,vendor_site_code

   ,new_tax_classification_code

                   ,new_invoice_wht_tax_group

                   ,new_liability_account

                   ,new_pre_payment_account

          FROM xx_vendor_master_data

         WHERE NVL(supp_status_flag, 'N') = 'S'

   AND NVL(supp_site_status_flag, 'N') IN ('N', 'E')

   --AND vendor_id = 7216

   --AND vendor_id IN (5041)--5059)

   ;

BEGIN

    FOR i IN c 

LOOP

l_status_flag     := 'N';

l_status_msg      := NULL;

l_liability_ccid  := NULL;

l_prepay_ccid   := NULL;

l_ap_awt_group_id := NULL;

l_allow_awt_flag  := 'N';

BEGIN

SELECT vendor_site_id 

  INTO l_vendor_site_id

  FROM ap_supplier_sites_all

WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code

   AND org_id = 101

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_vendor_site_id := null;

l_status_flag := 'E';

l_status_msg := 'Unable to find vendor_site_code. '; 

WHEN OTHERS THEN

l_vendor_site_id := NULL;

l_status_flag := 'E';

l_status_msg := 'Error while validating vendor_site_code. error message: ' || SQLERRM;  

END;

BEGIN

SELECT code_combination_id 

  INTO l_liability_ccid

  FROM gl_code_combinations_kfv

WHERE concatenated_segments = TRIM(i.new_liability_account)

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_liability_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_liability_account. '; 

WHEN OTHERS THEN

l_liability_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_liability_account. error message: ' || SQLERRM;  

END;

BEGIN

SELECT code_combination_id 

  INTO l_prepay_ccid

  FROM gl_code_combinations_kfv

WHERE concatenated_segments = TRIM(i.new_pre_payment_account)

   ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_prepay_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_pre_payment_account. '; 

WHEN OTHERS THEN

l_prepay_ccid := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_pre_payment_account. error message: ' || SQLERRM;  

END;

IF TRIM(i.new_invoice_wht_tax_group) IS NOT NULL THEN

BEGIN

SELECT group_id 

  INTO l_ap_awt_group_id

  FROM ap_awt_groups

WHERE name = TRIM(i.new_invoice_wht_tax_group)

   ;

l_allow_awt_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Unable to find new_invoice_wht_tax_group. '; 

WHEN OTHERS THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  

END;

END IF;

dbms_output.put_line('l_status_flag = ' || l_status_flag);

dbms_output.put_line('l_status_msg = ' || l_status_msg);

        IF l_status_flag <> 'E' THEN

fnd_global.apps_initialize(0, 50240, 200);

mo_global.init('SQLAP');

mo_global.set_policy_context('S', 101);

v_api_version := 1.0;

v_init_msg_list := fnd_api.g_true;

v_commit := fnd_api.g_true;

v_validation_level := fnd_api.g_valid_level_full;

--l_vendor_site_id := j.vendor_site_id; -- to be end dated

v_calling_prog := 'Supplier Tax code update';

l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;

l_vendor_site_rec.last_update_date := sysdate;

--l_vendor_site_rec.last_updated_by := 6147023;

l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;

l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;

l_vendor_site_rec.accts_pay_code_combination_id := l_liability_ccid;

l_vendor_site_rec.prepay_code_combination_id := l_prepay_ccid;

l_vendor_site_rec.vat_code := i.new_tax_classification_code;

l_vendor_site_rec.awt_group_id := l_ap_awt_group_id;

l_vendor_site_rec.allow_awt_flag := l_allow_awt_flag;

--l_vendor_site_rec.auto_tax_calc_flag := 'y';

--l_vendor_site_rec.vat_registration_num := i.tax_registration_no;

ap_vendor_pub_pkg.update_vendor_site_public(p_api_version => v_api_version

                                           ,p_init_msg_list => v_init_msg_list

       ,p_commit => v_commit

                                           ,p_validation_level  => v_validation_level

       ,x_return_status => x_return_status

       ,x_msg_count => x_msg_count

       ,x_msg_data     => x_msg_data

       ,p_vendor_site_rec   => l_vendor_site_rec

       ,p_vendor_site_id    => l_vendor_site_id

       ,p_calling_prog      => v_calling_prog

       );

dbms_output.put_line('x_return_status = ' || x_return_status);

dbms_output.put_line('x_msg_count = ' || x_msg_count);

dbms_output.put_line('x_msg_data = ' || x_msg_data);

IF (x_return_status <> fnd_api.g_ret_sts_success) 

THEN

FOR i1 IN 1 .. fnd_msg_pub.count_msg 

LOOP

l_msg := fnd_msg_pub.get(p_msg_index => i1,

  p_encoded   => fnd_api.g_false);

DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);

UPDATE xx_vendor_master_data 

   SET supp_site_status_flag = 'E'

      ,supp_site_status_msg = l_msg 

     WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code;

END LOOP;

ELSE

   DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');

   UPDATE xx_vendor_master_data 

  SET supp_site_status_flag = 'S'

,supp_site_status_msg = 'Update Successful' 

WHERE vendor_id = i.vendor_id

  AND vendor_site_code = i.vendor_site_code

  ;

END IF;

ELSE

UPDATE xx_vendor_master_data 

   SET supp_site_status_flag = 'E'

      ,supp_site_status_msg = l_status_msg 

WHERE vendor_id = i.vendor_id

   AND vendor_site_code = i.vendor_site_code

   ;

END IF;

COMMIT;

    END LOOP;

END;

Script to Update Supplier Vendor Type and Invoice Withholding Tax Group at Supplier Level in Oracle APPS

Table Script:

DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;

CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA

(

  VENDOR_NAME                    VARCHAR2(250 BYTE),

  VENDOR_ID                      NUMBER,

  VENDOR_SITE_CODE               VARCHAR2(15 BYTE),

  ORG_ID                         NUMBER,

  LIABILITY_ACCOUNT              VARCHAR2(207 BYTE),

  NEW_LIABILITY_ACCOUNT          VARCHAR2(207 BYTE),

  PRE_PAYMENT_ACCOUNT            VARCHAR2(207 BYTE),

  NEW_PRE_PAYMENT_ACCOUNT        VARCHAR2(207 BYTE),

  TAX_CLASSIFICATION_CODE        VARCHAR2(207 BYTE),

  NEW_TAX_CLASSIFICATION_CODE    VARCHAR2(30 BYTE),

  INVOICE_WITHHOLDING_TAX_GROUP  VARCHAR2(25 BYTE),

  NEW_INVOICE_WHT_TAX_GROUP      VARCHAR2(25 BYTE),

  SUPPLIER_TYPE                  VARCHAR2(80 BYTE),

  NEW_SUPPLIER_TYPE              VARCHAR2(80 BYTE),

  TAX_APPLICABILITY              VARCHAR2(80 BYTE),

  SUPP_STATUS_FLAG               VARCHAR2(1 BYTE),

  SUPP_STATUS_MSG                VARCHAR2(2000 BYTE),

  SUPP_SITE_STATUS_FLAG          VARCHAR2(1 BYTE),

  SUPP_SITE_STATUS_MSG           VARCHAR2(2000 BYTE)

);

DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;

CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;

API Script:

-----------------------------------------------------------------------------------------------

--SET serveroutput on;

DECLARE

p_api_version          NUMBER;

p_init_msg_list        VARCHAR2(200);

p_commit                VARCHAR2(200);

p_validation_level      NUMBER;

x_return_status        VARCHAR2(200);

x_msg_count            NUMBER;

x_msg_data              VARCHAR2(200);

lr_vendor_rec          apps.ap_vendor_pub_pkg.r_vendor_rec_type;

lr_existing_vendor_rec ap_suppliers%ROWTYPE;

l_msg                  VARCHAR2(200);

p_vendor_id            NUMBER;

l_new_supplier_type fnd_lookup_values.lookup_code%type;

l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;

l_ap_awt_group_id     ap_awt_groups.group_id%TYPE;

l_status_flag VARCHAR(1):='N';

l_status_msg VARCHAR2(2000) := NULL;

CURSOR c_vendor_type IS

SELECT DISTINCT vendor_id

               ,supplier_type

   ,new_supplier_type

                       ,new_invoice_wht_tax_group    

  FROM xx_vendor_master_data

WHERE NVL(supp_status_flag, 'N') IN ('N', 'E')

   --AND vendor_id = 7216

   AND vendor_id IN (5041)--5059)

   ;

    BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50240, 200); --user_id, 

mo_global.init('SQLAP');

fnd_client_info.set_org_context(101);

-- Assign Basic Values

p_api_version      := 1.0;

p_init_msg_list    := fnd_api.g_true;

p_commit           := fnd_api.g_true;

p_validation_level := fnd_api.g_valid_level_full;

     

FOR j in c_vendor_type 

        LOOP

            p_vendor_id        := j.vendor_id;

l_status_flag     := 'N';

l_status_msg      := NULL;

l_ap_awt_group_id := NULL;

l_allow_awt_flag  := 'N';

    

            -- gather vendor details

            BEGIN

                SELECT *

                  INTO lr_existing_vendor_rec

                  FROM ap_suppliers asa

                 WHERE asa.vendor_id = p_vendor_id;

            EXCEPTION

    WHEN OTHERS THEN

DBMS_OUTPUT.put_line('Unable to derive the supplier  information for vendor id:' ||

                                      p_vendor_id);

END;

            

IF j.supplier_type <> j.new_supplier_type THEN

BEGIN

SELECT lookup_code

  INTO l_new_supplier_type

  FROM fnd_lookup_values

WHERE meaning = j.new_supplier_type

   AND lookup_type = 'VENDOR TYPE'

   AND enabled_flag = 'Y'

   AND language = userenv('LANG');

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_new_supplier_type := null;

l_status_flag := 'E';

l_status_msg := 'Supplier Type does not exists in VENDOR TYPE Lookup: ' || SQLERRM; 

WHEN OTHERS THEN

l_new_supplier_type := null;

l_status_flag := 'E';

l_status_msg := 'Error while validating new supplier type. error message: ' || SQLERRM;  

END;

ELSE

NULL;

END IF;

IF TRIM(j.new_invoice_wht_tax_group) IS NOT NULL THEN

BEGIN

SELECT group_id 

  INTO l_ap_awt_group_id

  FROM ap_awt_groups

WHERE name = TRIM(j.new_invoice_wht_tax_group)

   ;

l_allow_awt_flag := 'Y';

EXCEPTION

WHEN OTHERS THEN

l_ap_awt_group_id := NULL;

l_status_flag := 'E';

l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;  

END;

END IF;

dbms_output.put_line('l_status_flag = ' || l_status_flag);

dbms_output.put_line('l_status_msg = ' || l_status_msg);

    

             lr_vendor_rec.vendor_id       := lr_existing_vendor_rec.vendor_id;

-----------------------------------------------

--Deactivate Vendor

             -- lr_vendor_rec.end_date_active := SYSDATE;

             -- lr_vendor_rec.enabled_flag    := 'N';

-----------------------------------------------

             lr_vendor_rec.vendor_type_lookup_code := l_new_supplier_type;

lr_vendor_rec.allow_awt_flag := l_allow_awt_flag;

lr_vendor_rec.awt_group_id := l_ap_awt_group_id;

            IF l_status_flag != 'E'

THEN

ap_vendor_pub_pkg.update_vendor(p_api_version      => p_api_version

   ,p_init_msg_list    => p_init_msg_list

   ,p_commit           => p_commit

   ,p_validation_level => p_validation_level

   ,x_return_status    => x_return_status

   ,x_msg_count        => x_msg_count

   ,x_msg_data         => x_msg_data

   ,p_vendor_rec       => lr_vendor_rec

   ,p_vendor_id        => p_vendor_id

   );

DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);

                DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);

    

IF (x_return_status <> fnd_api.g_ret_sts_success) THEN

FOR i IN 1 .. fnd_msg_pub.count_msg 

LOOP

l_msg := fnd_msg_pub.get(p_msg_index => i

                        ,p_encoded   => fnd_api.g_false

);

DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);

UPDATE xx_vendor_master_data 

                           SET supp_status_flag = 'E'

      ,supp_status_msg = l_msg 

     WHERE vendor_id = j.vendor_id;

                    END LOOP;

                ELSE

DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');

    UPDATE xx_vendor_master_data 

   SET supp_status_flag = 'S'

  ,supp_status_msg = 'Update Successful' 

WHERE vendor_id = j.vendor_id;

END IF;

ELSE

UPDATE xx_vendor_master_data 

   SET supp_status_flag = 'E'

      ,supp_status_msg = l_status_msg 

WHERE vendor_id = j.vendor_id;

            END IF;

        END LOOP;

    END;

/

AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG in Oracle APPS(AP_TAX_CALCULATION_METHOD)

SELECT *

  FROM fnd_lookup_values

 WHERE lookup_type = 'AP_TAX_CALCULATION_METHOD'

 AND language = USERENV('LANG')

Here AP_SUPPLIER_SITES_ALL.AUTO_TAX_CALC_FLAG will be fnd_lookup_values.lookup_code

API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

We had a requirement to update Tax Classification Code, Invoice Withholding Tax Group at supplier level. While using ap_vendor_pub_pkg.update_vendor_site API we got below error message:

The API call failed with error ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

But when used ap_vendor_pub_pkg.update_vendor_site_public the update was successful.

AP_CHECKS_ALL.PAYMENT_METHOD_LOOKUP_CODE and AP_CHECKS_ALL.PAYMENT_METHOD_CODE in Oracle APPS

Below is from Oracle Metalink ID 1919051.1

PAYMENT_METHOD_LOOKUP_CODE is obsolete in R12 hence it should not be referred in R12. If you want to refer payment method code from Invoice header then PAYMENT_METHOD_CODE should be used. During upgrade to R12 we populate PAYMENT_METNHOD_CODE with PAYMENT_METHOD_LOOKUP_CODE for migrated invoices.

SELECT payment_method_code, payment_method_name, description

  FROM iby_payment_methods_tl

 WHERE payment_method_code IN

          (SELECT DISTINCT PAYMENT_METHOD_CODE

              FROM AP_CHECKS_ALL

              )

AND language = USERENV('LANG')

Sample code to attach file at Invoice level in Oracle APPS

DECLARE l_rowid rowid; l_attached_document_id NUMBER; l_document_id NUMBER; l_media_id NUMBER; l_description fnd_documents_tl.description%TYPE; l_seq_num NUMBER; l_datatype_id NUMBER; CURSOR c_invoice_attachment IS SELECT fad_l.seq_num ,fl_l.file_id media_id ,fl_l.file_data media_data_blob ,fad_l.category_id ,fl_l.file_id ,fl_l.file_name ,fl_l.file_content_type ,fl_l.file_data ,fl_l.file_format ,fdd.datatype_id ,fdd.name document_type ,fdd.user_name ,aia.invoice_id pk1_value ,fdt_l.description FROM fnd_attached_documents fad_l ,fnd_documents fd_l ,fnd_documents_tl fdt_l ,fnd_document_categories_tl fdct_l ,fnd_lobs fl_l ,fnd_document_datatypes fdd ,xx_hdr_tab gen ,ap_invoices_all aia WHERE 1 = 1 AND fad_l.document_id = fd_l.document_id AND fad_l.document_id = fdt_l.document_id AND fdct_l.category_id = fd_l.category_id AND fd_l.media_id = fl_l.file_id AND fad_l.entity_name = 'XX_HDR_TAB' AND fdt_l.language = USERENV('LANG') AND fdct_l.language = USERENV('LANG') AND fd_l.datatype_id = fdd.datatype_id AND fdd.language = USERENV('LANG') AND fad_l.pk1_value = '90' AND fad_l.pk1_value = gen.xx_hdr_id AND gen.invoice_num = aia.invoice_num ; BEGIN FOR i IN c_invoice_attachment LOOP insert into xx_test1 values(xx_test1_s.nextval, 'i.document_type: '||i.document_type); IF i.document_type = 'WEB_PAGE' THEN l_media_id := null; ELSE SELECT fnd_lobs_s.nextval INTO l_media_id FROM dual; END IF; insert into xx_test1 values(xx_test1_s.nextval, 'l_media_id: '||l_media_id); If i.document_type IN ('WEB_PAGE', 'FILE') THEN SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM dual; SELECT fnd_attached_documents_s.NEXTVAL INTO l_attached_document_id FROM dual; SELECT nvl(max(seq_num),0) + 10 INTO l_seq_num FROM fnd_attached_documents WHERE pk1_value = i.pk1_value --UNIQUE ID AND entity_name = 'AP_INVOICES'; insert into xx_test1 values(xx_test1_s.nextval, 'l_seq_num: '||l_seq_num); fnd_documents_pkg.insert_row (X_ROWID => l_rowid ,X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => SYSDATE ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => SYSDATE ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => 0 ,X_DATATYPE_ID => i.datatype_id --5 -- Web Page ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_USAGE_TYPE => 'O' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ); fnd_documents_pkg.insert_tl_row (X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => sysdate ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_TITLE => i.file_name ); fnd_attached_documents_pkg.insert_row (X_ROWID => l_rowid ,X_ATTACHED_DOCUMENT_ID => l_attached_document_id ,X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => sysdate ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_SEQ_NUM => l_seq_num ,X_ENTITY_NAME => 'AP_INVOICES' ,X_COLUMN1 => null ,X_PK1_VALUE => i.pk1_value ,X_PK2_VALUE => null ,X_PK3_VALUE => null ,X_PK4_VALUE => null ,X_PK5_VALUE => null ,X_AUTOMATICALLY_ADDED_FLAG => 'N' ,X_DATATYPE_ID => i.datatype_id ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ,X_TITLE => i.description ); IF l_media_id IS NOT NULL THEN INSERT INTO FND_LOBS (file_id ,file_name ,file_content_type ,file_data ,upload_date ,expiration_date ,program_name ,program_tag ,language ,oracle_charset ,file_format ) VALUES (l_media_id ,i.file_name ,i.file_content_type ,i.file_data ,sysdate ,null ,null ,null ,'US' ,'UTF8' ,'binary' ); END IF; commit; END IF; END LOOP; END;