[Home] [Help]
1021: | Purpose : This Procedure has all the necessary code to upgrade |
1022: | old write off data into the new tables. |
1023: | This procedure upgrades PO,AP and Inventory Write off |
1024: | transactions from the old PO_ACCRUAL_WRITE_OFFS_ALL |
1025: | table into the new CST_WRITE_OFFS and |
1026: | CST_WRITE_OFF_DETAILS table.This upgrade is done only |
1027: | for the very first run of the load program for the |
1028: | given OU.The old WIP write off data is not Upgraded. |
1029: | |
1088: l_stmt_num := 10;
1089: debug(' l_stmt_num:'||l_stmt_num);
1090: debug(' Upgrading WO Receiving data ');
1091:
1092: INSERT into cst_write_offs
1093: (write_off_id,
1094: transaction_date,
1095: accrual_account_id,
1096: offset_account_id,
1206: debug(' l_stmt_num :'||l_stmt_num);
1207: debug(' l_old_erv :'||l_old_erv);
1208: debug(' Upgrading WO miscellenaous AP invoice 11i');
1209:
1210: INSERT into cst_write_offs
1211: (write_off_id,
1212: transaction_date,
1213: accrual_account_id,
1214: offset_account_id,
1339: l_stmt_num := 45;
1340: debug(' l_stmt_num :'||l_stmt_num);
1341: debug(' Upgrading WO miscellenaous AP invoice 11.0');
1342:
1343: INSERT into cst_write_offs
1344: (write_off_id,
1345: transaction_date,
1346: accrual_account_id,
1347: offset_account_id,
1470: l_stmt_num := 50;
1471: debug(' l_stmt_num :'||l_stmt_num);
1472: debug(' Upgrading WO regular AP INVOICE data 11i');
1473:
1474: INSERT into cst_write_offs
1475: (write_off_id,
1476: transaction_date,
1477: accrual_account_id,
1478: offset_account_id,
1636: debug(' Upgrading WO AP INVOICE data 11.0');
1637:
1638: /* Bug 6757017: The following query will upgrade write off date from 11.0 releases
1639: where the line_match_order wouild be null */
1640: INSERT into cst_write_offs
1641: (write_off_id,
1642: transaction_date,
1643: accrual_account_id,
1644: offset_account_id,
1806: entered amount for pre 11.5.10 txns as it is not technically feasible because of the way
1807: we store the currency info in MTA for the txns.So we will be inserting NULL as entered amounts
1808: for pre 11.5.10 txns */
1809:
1810: INSERT into cst_write_offs
1811: (write_off_id,
1812: transaction_date,
1813: accrual_account_id,
1814: offset_account_id,
1967: cwo.request_id,
1968: cwo.program_application_id,
1969: cwo.program_id,
1970: cwo.program_update_date
1971: FROM cst_write_offs cwo,
1972: po_accrual_write_offs_all pawo,
1973: mtl_transaction_accounts mta,
1974: xla_distribution_links xld,
1975: cst_accrual_accounts ca --BUG7528609
2044: cwo.request_id,
2045: cwo.program_application_id,
2046: cwo.program_id,
2047: cwo.program_update_date
2048: FROM cst_write_offs cwo,
2049: po_accrual_write_offs_all pawo,
2050: po_lookup_codes plc,
2051: rcv_receiving_sub_ledger rrs,
2052: rcv_transactions rt,
2126: cwo.request_id,
2127: cwo.program_application_id,
2128: cwo.program_id,
2129: cwo.program_update_date
2130: FROM cst_write_offs cwo,
2131: po_accrual_write_offs_all pawo,
2132: po_lookup_codes plc,
2133: ap_invoice_distributions_all aida,
2134: ap_ae_lines_all aal,
2443: )
2444: )
2445: AND xah.gl_transfer_status_code = 'Y'
2446: AND NOT EXISTS (SELECT 1
2447: FROM cst_write_offs cwo1
2448: WHERE cwo1.transaction_type_code = 'WRITE OFF'
2449: AND cwo1.invoice_distribution_id is NOT NULL
2450: AND cwo1.accrual_account_id = xal.code_combination_id
2451: AND cwo1.invoice_distribution_id = aida.invoice_distribution_id
2449: AND cwo1.invoice_distribution_id is NOT NULL
2450: AND cwo1.accrual_account_id = xal.code_combination_id
2451: AND cwo1.invoice_distribution_id = aida.invoice_distribution_id
2452: AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2453: FROM cst_write_offs cwo2
2454: WHERE cwo2.invoice_distribution_id is NOT NULL
2455: AND cwo2.invoice_distribution_id = aida.invoice_distribution_id
2456: AND cwo2.accrual_account_id = xal.code_combination_id
2457: AND EXISTS (Select 1 from cst_write_off_details cwod
2694: AND to_number(hoi.org_information3) = p_operating_unit
2695: )
2696: AND NOT EXISTS (
2697: SELECT 1
2698: FROM cst_write_offs cwo1
2699: WHERE cwo1.transaction_type_code = 'WRITE OFF'
2700: AND cwo1.inventory_transaction_id is NOT NULL
2701: AND cwo1.inventory_transaction_id = mmt.transaction_id
2702: AND cwo1.accrual_account_id = xal.code_combination_id
2700: AND cwo1.inventory_transaction_id is NOT NULL
2701: AND cwo1.inventory_transaction_id = mmt.transaction_id
2702: AND cwo1.accrual_account_id = xal.code_combination_id
2703: AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2704: FROM cst_write_offs cwo2
2705: WHERE cwo2.inventory_transaction_id is NOT NULL
2706: AND cwo2.inventory_transaction_id = mmt.transaction_id
2707: AND cwo2.accrual_account_id = xal.code_combination_id
2708: AND EXISTS ( Select 1 from cst_write_off_details cwod
2821: AND to_number(hoi.org_information3) = p_operating_unit
2822: )
2823: AND NOT EXISTS (
2824: SELECT 1
2825: FROM cst_write_offs cwo1
2826: WHERE cwo1.transaction_type_code = 'WRITE OFF'
2827: AND cwo1.inventory_transaction_id is NOT NULL
2828: AND cwo1.inventory_transaction_id = mmt.transaction_id
2829: AND cwo1.accrual_account_id = xal.code_combination_id
2827: AND cwo1.inventory_transaction_id is NOT NULL
2828: AND cwo1.inventory_transaction_id = mmt.transaction_id
2829: AND cwo1.accrual_account_id = xal.code_combination_id
2830: AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2831: FROM cst_write_offs cwo2
2832: WHERE cwo2.inventory_transaction_id is NOT NULL
2833: AND cwo2.inventory_transaction_id = mmt.transaction_id
2834: AND cwo2.accrual_account_id = xal.code_combination_id
2835: AND EXISTS ( Select 1 from cst_write_off_details cwod
3671: cwo.destination_type_code,
3672: cwo.operating_unit_id,
3673: l_build_id,
3674: l_request_id
3675: FROM cst_write_offs cwo,
3676: --{ Immunization for po_vendor merge
3677: po_distributions_all pod,
3678: po_headers_all poh
3679: --}