AP INVOICES MIGRATION SCRIPT FOR one Org to another Org

PARTIALLY PAID INVOICES
-------------------------

HEADER
-------

SELECT substr(trim(aia.invoice_num),1,44)||'_uat1' invoice_num,
       aia.invoice_date,
       --  getvendor(aia.vendor_id),
       pov.segment1 VENDOR_NUM,
       pvs.vendor_site_code ,
       (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID, 0)) invoice_amount,
       aia.invoice_currency_code,
       aia.exchange_rate,
       aia.exchange_rate_type,
       substr((aia.gl_date || aia.description),1,235) description,
       aia.doc_category_code,
       aia.pay_group_lookup_code,
       aia.invoice_type_lookup_code,
       aia.SOURCE,
       '30-NOV-2009' gl_date,
       --       apt.NAME Terms_Name,
       (select apt.name from ap_terms apt where aia.terms_id = apt. term_id) Terms_Name,
       aia.attribute4 DB_PAYMENT_METHOD,
       aia.attribute5 DB_PAYMENT_LOCATION,
       aia.attribute6 DB_VENDOR_ADDRESS,
       'ICL BIHAR' ORG_CODE,
       --       fu.user_name,
       (select user_name from fnd_user fu where aia.created_by = fu. user_id) user_name
/*       aia.invoice_amount,
       aia.AMOUNT_PAID,
       (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID, 0)) remaining_amount,
       --GET_CODE_COMBINATION(accts_pay_code_combination_id)
       gcc.segment4 || '.' || gcc.segment1 || '.' || gcc.segment2 || '.' ||
       gcc.segment3 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' ||
       gcc.segment7 CODE_COMBINATIONS
 */ from ap_invoices_all      aia,
       gl_code_combinations gcc,
       po_vendors           pov,
       PO_VENDOR_SITES_ALL  PVS
 where aia.org_id = 169
   and INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT' --'DEBIT' -- 'DEBIT' ''STANDARD''
   and aia.payment_status_flag = 'P'
 --AND api.gl_date <= '31-DEC-2009'
 /*  AND aia.INVOICE_ID IN
       (SELECT API2.INVOICE_ID
          FROM AP_INVOICES_ALL API2, AP_INVOICE_DISTRIBUTIONS_ALL APID
         WHERE APID.INVOICE_ID = API2.INVOICE_ID
           AND api2.org_id = 169
        --   and API2.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
           and API2.payment_status_flag = 'P'
           AND APID.ACCOUNTING_DATE <= '31-DEC-2009')
  */ and aia.accts_pay_code_combination_id = gcc.code_combination_id
   and pov.vendor_id = aia.vendor_id
   and pvs.org_id = aia.org_id
   AND PVS.vendor_id = aia.vendor_id
   AND PVS.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID
   and gcc.segment4 in
       ('10101', '10201', '10205', '10210', '10215', '10225', '10230',
        '10235', '10240', '10245', '10255', '10301', '10305', '10310',
        '10315', '10325', '22001', '22005', '22010')


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

LINES
----------


select substr(trim(api.invoice_num),1,44) ||'_uat1' invoice_num,
       1 LINE_NUMBER,
       api.invoice_amount - api.amount_paid LINE_AMOUNT,
       ---------
       substr(GET_PO_NUM(API.INVOICE_ID, API.ORG_ID) || '.' ||
       (select getmrns_poh(po_header_id)
          from po_headers_all poh
         where poh.segment1 = GET_PO_NUM(API.INVOICE_ID, API.ORG_ID)
           and poh.org_id = 169) || api.description,1,235) LINE_DESCRIPTION,
       ---------
       'ITEM' LINE_TYPE_LOOKUP_CODE,
       '30-NOV-2009' ACCOUNTING_DATE,
       '19041.123.000.000.0000.000.000' DIST_CODE_CONCATENATED,
       getvendorcode(api.vendor_id) VENDOR_CODE,
       NULL CELL_SITE1,
       NULL CELL_SITE2,
       NULL CELL_SITE3,
       NULL CELL_SITE4,
       NULL CELL_SITE5,
       NULL TDS_CODE1,
       NULL WCT_CODE,
       NULL ESI_CODE
  from ap_invoices_all api,
       gl_code_combinations gcc
 where api.org_id = 169
   and INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT'
      /*and api.invoice_amount - api.amount_paid > 0
               and api.amount_paid > 0*/
   and api.payment_status_flag = 'P'
      --AND api.gl_date <= '31-DEC-2009'
 /*  AND API.INVOICE_ID IN
       (SELECT API2.INVOICE_ID
          FROM AP_INVOICES_ALL API2, AP_INVOICE_DISTRIBUTIONS_ALL APID
         WHERE APID.INVOICE_ID = API2.INVOICE_ID
           AND api2.org_id = 169
          -- and API2.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
           and API2.payment_status_flag = 'P'
           AND APID.ACCOUNTING_DATE <= '31-DEC-2009')
  */ and api.accts_pay_code_combination_id = gcc.code_combination_id
   and gcc.segment4 in
       ('10101', '10201', '10205', '10210', '10215', '10225', '10230',
        '10235', '10240', '10245', '10255', '10301', '10305', '10310',
        '10315', '10325', '22001', '22005', '22010')

total unpaid
-----------

header
----------
SELECT substr(trim(aia.invoice_num),1,44)||'_uat2' invoice_num,
         aia.invoice_date,
         pv.segment1 vendor_num,
         pvs.vendor_site_code,
         (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID,0))  invoice_amount,
         aia.invoice_currency_code,
         aia.exchange_rate,
         aia.exchange_rate_type,
       substr((aia.gl_date ||'.'|| aia.description),1,235) description,
         aia.doc_category_code,
       aia.pay_group_lookup_code,
         aia.invoice_type_lookup_code,
       aia.SOURCE,
        '30-NOV-2009' gl_date,
       apt.NAME Terms_Name,
         aia.attribute4 DB_PAYMENT_METHOD,
         aia.attribute5 DB_PAYMENT_LOCATION,
         aia.attribute6 DB_VENDOR_ADDRESS,
         'ICL BIHAR' ORG_CODE,
         fu.user_name
--aia.invoice_amount,
--nvl(aia.AMOUNT_PAID,0),
/*(aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID,0)) , (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID,0))  remaining_amount,
         gcc.segment4
       || '.'
       || gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7 ACCOUNT
 */ FROM ap_invoices_all aia,
       ap_terms apt,
       po_vendors pv,
       po_vendor_sites_all pvs,
       gl_code_combinations gcc,
         fnd_user fu
 WHERE aia.org_id = 169
   AND aia.payment_status_flag IN ('N', 'P', 'Y')
  -- AND aia.gl_date <= '31-Dec-2009'
   AND aia.cancelled_date IS NULL
   and INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
   --and aia.PREPAY_AMOUNT_APPLIED  is Not null
   AND aia.terms_id = apt.term_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvs.vendor_site_id
   AND pv.vendor_id = pvs.vendor_id
   AND aia.accts_pay_code_combination_id = gcc.code_combination_id
   --and (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID,0)) <> 0
   and aia.invoice_amount <>0
   and nvl(aia.amount_paid,0) = 0
   --AND (aia.INVOICE_AMOUNT - nvl(aia.AMOUNT_PAID,0)) * nvl(aia.EXCHANGE_RATE,1) >0
   and aia.created_by = fu.user_id (+)
    and gcc.segment4 in ('10101','10201','10205','10210','10215','10225',
'10230','10235','10240',
   '10245','10255','10301','10305','10310','10315','10325',
   '22001','22005','22010')
   and gcc.segment1='123'
order by aia.invoice_num


line
------
select substr(trim(api.invoice_num),1,44) ||'_uat1' invoice_num,
       1 LINE_NUMBER,
       api.invoice_amount - api.amount_paid LINE_AMOUNT,
       ---------
       substr(GET_PO_NUM(API.INVOICE_ID, API.ORG_ID) || '.' ||
       (select getmrns_poh(po_header_id)
          from po_headers_all poh
         where poh.segment1 = GET_PO_NUM(API.INVOICE_ID, API.ORG_ID)
           and poh.org_id = 169) || api.description,1,235) LINE_DESCRIPTION,
       ---------
       'ITEM' LINE_TYPE_LOOKUP_CODE,
       '30-NOV-2009' ACCOUNTING_DATE,
       '19041.123.000.000.0000.000.000' DIST_CODE_CONCATENATED,
       getvendorcode(api.vendor_id) VENDOR_CODE,
       NULL CELL_SITE1,
       NULL CELL_SITE2,
       NULL CELL_SITE3,
       NULL CELL_SITE4,
       NULL CELL_SITE5,
       NULL TDS_CODE1,
       NULL WCT_CODE,
       NULL ESI_CODE
  from ap_invoices_all api,
       gl_code_combinations gcc
 where api.org_id = 169
   and INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT'
      /*and api.invoice_amount - api.amount_paid > 0
               and api.amount_paid > 0*/
   and api.payment_status_flag = 'P'
      --AND api.gl_date <= '31-DEC-2009'
 /*  AND API.INVOICE_ID IN
       (SELECT API2.INVOICE_ID
          FROM AP_INVOICES_ALL API2, AP_INVOICE_DISTRIBUTIONS_ALL APID
         WHERE APID.INVOICE_ID = API2.INVOICE_ID
           AND api2.org_id = 169
          -- and API2.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
           and API2.payment_status_flag = 'P'
           AND APID.ACCOUNTING_DATE <= '31-DEC-2009')
  */ and api.accts_pay_code_combination_id = gcc.code_combination_id
   and gcc.segment4 in
       ('10101', '10201', '10205', '10210', '10215', '10225', '10230',
        '10235', '10240', '10245', '10255', '10301', '10305', '10310',
        '10315', '10325', '22001', '22005', '22010')