[Home] [Help]
197: -- bug9553092, interest invoices would be picked up for sweep and display
198: -- in the GT table only when the corresponding payment event is a problem
199: -- for the period close processing
200: --
201: insert into ap_period_close_excps_gt
202: ( invoice_id
203: ,invoice_distribution_id
204: ,invoice_payment_id -- 7318763
205: ,accounting_event_id
317:
318:
319: l_rowcount := sql%rowcount; -- bug 9509700
320:
321: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||' is:'||l_rowcount);
322:
323: if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
324: -- current action is PERIOD_CLOSE and there are unposted invoices
325: -- and we cannot allow to close period hence return
327: end if;
328:
329: l_rowcount := NULL;
330:
331: insert into ap_period_close_excps_gt
332: ( invoice_id
333: ,invoice_distribution_id
334: ,accounting_event_id
335: ,accounting_date
428:
429:
430: l_rowcount := sql%rowcount; -- bug 9509700
431:
432: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||'for table='||G_SRC_TAB_AP_SELF_TAX_DIST_ALL
433: || ' is:'||l_rowcount);
434:
435: if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
436: -- current action is PERIOD_CLOSE and there are unposted self assessed tax dists
441: l_rowcount := NULL;
442: /* Removed the Hint Bug#8870730 */
443:
444: /* bug 11702640 Made changes to the select statement below */
445: insert into ap_period_close_excps_gt
446: ( invoice_id
447: ,invoice_line_number
448: ,accounting_date
449: ,org_id
505: and ai.approval_ready_flag <> 'S'; --bug 9224843
506:
507: l_rowcount := sql%rowcount; -- bug 9509700
508:
509: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_LINES_WITHOUT_DISTS||' is:'||l_rowcount);
510:
511: if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
512: -- current action is PERIOD_CLOSE and there are lines without any distributions
513: -- so we cannot allow to close perio, hence return
516:
517: l_rowcount := NULL;
518:
519: -- gagrawal
520: insert into ap_period_close_excps_gt
521: ( invoice_id
522: ,accounting_event_id
523: ,accounting_date
524: ,org_id
568:
569: l_rowcount := sql%rowcount; -- bug 9509700
570:
571:
572: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PREPAY_HIST||' is:'||l_rowcount);
573:
574: if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
575: -- current action is PERIOD_CLOSE and there are lines without any distributions
576: -- so we cannot allow to close perio, hence return
586: -- PAYMENTS --
587: ----------------
588: <
589:
590: INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT
591: (payment_history_id
592: ,accounting_event_id
593: ,accounting_date
594: ,check_id
645: );
646:
647: l_rowcount := sql%rowcount; -- bug 9509700
648:
649: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PMT_HISTORY||' is:'||l_rowcount);
650:
651: if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
652: -- current action is PERIOD_CLOSE and there are lines without any distributions
653: -- so we cannot allow to close period, hence return
657: l_rowcount := NULL;
658:
659: if g_action = G_ACTION_SWEEP then -- populate GT ONLY when sweeping
660: -- get unaccounted invoice payments
661: insert into ap_period_close_excps_gt
662: (invoice_payment_id
663: ,accounting_event_id
664: ,accounting_date
665: ,check_id
710: AND NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS';
711:
712: l_rowcount := sql%rowcount; -- bug 9509700
713:
714: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_INV_PMTS||' is:'||l_rowcount);
715:
716: l_rowcount := NULL;
717: end if;
718:
758:
759: -- Bug 8887052: Modified decode on invoices to consider MANUAL events
760: -- untransferred headers as well.
761:
762: INSERT INTO ap_period_close_excps_gt
763: (accounting_event_id
764: ,accounting_date
765: ,org_id
766: ,legal_entity_id
825: IF xla_events_unacct.COUNT> 0 THEN
826: FOR i IN xla_events_unacct.FIRST..xla_events_unacct.LAST
827: LOOP
828: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
829: FROM ap_period_close_excps_gt
830: WHERE accounting_event_id = xla_events_unacct(i).event_id)
831: AND xla_events_unacct(i).entity_code='AP_INVOICES' THEN
832: INTO ap_period_close_excps_gt
833: ( invoice_id
828: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
829: FROM ap_period_close_excps_gt
830: WHERE accounting_event_id = xla_events_unacct(i).event_id)
831: AND xla_events_unacct(i).entity_code='AP_INVOICES' THEN
832: INTO ap_period_close_excps_gt
833: ( invoice_id
834: ,invoice_distribution_id
835: ,invoice_payment_id -- 7318763
836: ,accounting_event_id
962:
963: FOR i IN xla_events_unacct.FIRST..xla_events_unacct.LAST
964: LOOP
965: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
966: FROM ap_period_close_excps_gt
967: WHERE accounting_event_id = xla_events_unacct(i).event_id)
968: AND xla_events_unacct(i).entity_code='AP_PAYMENTS' then
969: INTO AP_PERIOD_CLOSE_EXCPS_GT
970: (payment_history_id
965: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
966: FROM ap_period_close_excps_gt
967: WHERE accounting_event_id = xla_events_unacct(i).event_id)
968: AND xla_events_unacct(i).entity_code='AP_PAYMENTS' then
969: INTO AP_PERIOD_CLOSE_EXCPS_GT
970: (payment_history_id
971: ,accounting_event_id
972: ,accounting_date
973: ,check_id
1029: -- Bug 8887052: Modified decode on invoices to consider
1030: -- unaccounted MANUAL events as well
1031:
1032: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
1033: FROM ap_period_close_excps_gt
1034: WHERE accounting_event_id = xla_events_unacct(i).event_id)
1035: THEN
1036: INTO ap_period_close_excps_gt
1037: (accounting_event_id
1032: INSERT WHEN NOT EXISTS (SELECT accounting_event_id
1033: FROM ap_period_close_excps_gt
1034: WHERE accounting_event_id = xla_events_unacct(i).event_id)
1035: THEN
1036: INTO ap_period_close_excps_gt
1037: (accounting_event_id
1038: ,accounting_date
1039: ,org_id
1040: ,legal_entity_id
1706: SET POH.closed_date = g_sweep_to_date
1707: WHERE po_header_id in (SELECT PLL.PO_HEADER_ID
1708: FROM PO_LINE_LOCATIONS_ALL PLL,
1709: PO_DISTRIBUTIONS_ALL PD,
1710: AP_PERIOD_CLOSE_EXCPS_GT GT
1711: WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1712: AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1713: AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1714: AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1727: SET closed_date = g_sweep_to_date
1728: WHERE line_location_id in (SELECT PLL.LINE_LOCATION_ID
1729: FROM PO_LINE_LOCATIONS_ALL PLL,
1730: PO_DISTRIBUTIONS_ALL PD,
1731: AP_PERIOD_CLOSE_EXCPS_GT GT
1732: WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1733: AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1734: AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1735: AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1779: BEGIN
1780:
1781: INSERT into ZX_TAX_DIST_ID_GT
1782: (SELECT detail_tax_dist_id
1783: FROM ap_period_close_excps_gt
1784: WHERE detail_tax_dist_id is not null
1785: AND source_type = G_SRC_TYP_UNACCT_DISTS
1786: AND source_table_name in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1787: G_SRC_TAB_AP_SELF_TAX_DIST_ALL));
1902: ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, 'INV'
1903: ,G_SRC_TAB_AP_PMT_HISTORY,'PMT'
1904: ) source
1905: ,invoice_payment_id -- 7318763
1906: FROM ap_period_close_excps_gt gt
1907: WHERE gt.source_type in (G_SRC_TYP_UNACCT_DISTS, G_SRC_TYP_UNACCT_PMT_HISTORY,
1908: G_SRC_TYP_UNACCT_PREPAY_HIST)
1909: AND gt.source_table_name in (G_SRC_TAB_AP_INV_DISTS_ALL, G_SRC_TAB_AP_PMT_HISTORY,
1910: G_SRC_TAB_AP_SELF_TAX_DIST_ALL, G_SRC_TAB_AP_PREPAY_HIST)
2080: period_name = g_sweep_to_period,
2081: last_update_date = sysdate,
2082: last_updated_by = 5
2083: WHERE aid.invoice_distribution_id in (SELECT gt.invoice_distribution_id
2084: FROM ap_period_close_excps_gt gt
2085: WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
2086: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL)
2087: AND aid.posted_flag in ('N','S','P') --Bug 9045217
2088: returning invoice_distribution_id bulk collect into l_dbi_key_value_list;
2089:
2090: debug ('update_ap_acct_date: total records updated in ap_invoice_distributions_all: '||sql%rowcount);
2091:
2092: forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
2093: update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
2094: set process_status_flag = 'Y'
2095: where invoice_distribution_id = l_dbi_key_value_list(i)
2096: AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2097: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL; -- 7318763
2101: p_operation => 'U',
2102: p_key_value_list => l_dbi_key_value_list,
2103: p_calling_sequence => 'AP_PERIOD_CLOSE_PKG.update_ap_acct_date');
2104:
2105: debug ('update_ap_acct_date: total distributions processed in ap_period_close_excps_gt: '||l_dbi_key_value_list.count);
2106:
2107: forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
2108: UPDATE ap_invoice_lines_all ail
2109: SET accounting_date = g_sweep_to_date,
2110: period_name = g_sweep_to_period,
2111: last_update_date = sysdate,
2112: last_updated_by = 5
2113: WHERE (ail.invoice_id, ail.line_number)
2114: in (SELECT /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ gt.invoice_id, gt.invoice_line_number --Bug 9045217
2115: FROM ap_period_close_excps_gt gt
2116: WHERE gt.invoice_distribution_id = l_dbi_key_value_list(i)
2117: AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2118: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL); -- 7318763
2111: last_update_date = sysdate,
2112: last_updated_by = 5
2113: WHERE (ail.invoice_id, ail.line_number)
2114: in (SELECT /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ gt.invoice_id, gt.invoice_line_number --Bug 9045217
2115: FROM ap_period_close_excps_gt gt
2116: WHERE gt.invoice_distribution_id = l_dbi_key_value_list(i)
2117: AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2118: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL); -- 7318763
2119:
2128: last_update_date = sysdate,
2129: last_updated_by = 5
2130: WHERE astd.invoice_distribution_id
2131: in (SELECT gt.invoice_distribution_id
2132: FROM ap_period_close_excps_gt gt
2133: WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
2134: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
2135: AND astd.posted_flag <> 'Y'
2136: returning invoice_distribution_id bulk collect into ltab_id;
2137:
2138: debug ('update_ap_acct_date: total records updated in ap_self_assessed_tax_dist_all: '||sql%rowcount);
2139:
2140: forall i in ltab_id.first .. ltab_id.last
2141: update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
2142: set process_status_flag = 'Y'
2143: where invoice_distribution_id = ltab_id(i)
2144: AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2145: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
2143: where invoice_distribution_id = ltab_id(i)
2144: AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2145: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
2146:
2147: debug ('update_ap_acct_date: total self assessed tax distributions processed in ap_period_close_excps_gt: '||ltab_id.count);
2148:
2149: ltab_id.delete;
2150:
2151:
2155: last_update_date = sysdate,
2156: last_updated_by = 5
2157: WHERE (ail.invoice_id,ail.line_number) in
2158: (SELECT gt.invoice_id, gt.invoice_line_number
2159: FROM ap_period_close_excps_gt gt
2160: WHERE gt.source_type = G_SRC_TYP_LINES_WITHOUT_DISTS
2161: AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL)
2162: returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
2163: debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
2162: returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
2163: debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
2164:
2165: forall i in ltab_id.first..ltab_id.last
2166: update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N4)*/ap_period_close_excps_gt gt --Bug 9045217
2167: set process_status_flag = 'Y'
2168: where invoice_id = ltab_id(i)
2169: and invoice_line_number = ltab_line_num(i)
2170: AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
2169: and invoice_line_number = ltab_line_num(i)
2170: AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
2171: AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL; -- 7318763
2172:
2173: debug ('update_ap_acct_date: total invoice lines processed in ap_period_close_excps_gt: '||ltab_id.count );
2174:
2175: ltab_id.delete;
2176:
2177: UPDATE ap_invoice_payments_all aip
2179: period_name = g_sweep_to_period,
2180: last_update_date = sysdate,
2181: last_updated_by = 5
2182: WHERE aip.invoice_payment_id in (SELECT gt.invoice_payment_id
2183: FROM ap_period_close_excps_gt gt
2184: WHERE gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
2185: AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS)
2186: AND aip.posted_flag <> 'Y'
2187: returning invoice_payment_id, accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
2188:
2189: debug ('update_ap_acct_date: total records updated in ap_invoice_payments_all: '||sql%rowcount);
2190:
2191: forall i in ltab_id.first .. ltab_id.last
2192: update ap_period_close_excps_gt gt
2193: set process_status_flag = 'Y'
2194: where invoice_payment_id = ltab_id(i)
2195: AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2196: AND gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
2195: AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2196: AND gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
2197: AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS; -- 7318763
2198:
2199: debug ('update_ap_acct_date: total invoice payments processed in ap_period_close_excps_gt: '||ltab_id.count );
2200:
2201: ltab_id.delete;
2202: Itab_event_id.delete; --Bug 9045217
2203:
2205: SET accounting_date = g_sweep_to_date,
2206: last_update_date = sysdate,
2207: last_updated_by = 5
2208: WHERE aph.payment_history_id in (SELECT gt.payment_history_id
2209: FROM ap_period_close_excps_gt gt
2210: WHERE gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
2211: AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY)
2212: AND aph.posted_flag <> 'Y'
2213: returning aph.payment_history_id,aph.accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
2214:
2215: debug ('update_ap_acct_date: total records updated in ap_payment_history_all: '||sql%rowcount);
2216:
2217: forall i in ltab_id.first .. ltab_id.last
2218: update ap_period_close_excps_gt gt
2219: set process_status_flag = 'Y'
2220: where payment_history_id = ltab_id(i)
2221: AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2222: AND gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
2220: where payment_history_id = ltab_id(i)
2221: AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2222: AND gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
2223: AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY; -- 7318763
2224: debug ('update_ap_acct_date: total payment history processed in ap_period_close_excps_gt: '||ltab_id.count );
2225:
2226: ltab_id.delete;
2227: Itab_event_id.delete; --Bug 9045217
2228:
2232: SET accounting_date = g_sweep_to_date,
2233: last_update_date = sysdate,
2234: last_updated_by = 5
2235: WHERE apph.accounting_event_id in (SELECT gt.accounting_event_id
2236: FROM ap_period_close_excps_gt gt
2237: WHERE gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
2238: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST
2239: AND gt.accounting_event_id IS NOT NULL)
2240: AND apph.posted_flag <> 'Y'
2243: debug ('update_ap_acct_date: total records updated in ap_prepay_history_all: '||sql%rowcount);
2244:
2245:
2246: forall i in ltab_id.first .. ltab_id.last
2247: update ap_period_close_excps_gt gt
2248: set process_status_flag = 'Y'
2249: where accounting_event_id = ltab_id(i)
2250: AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
2251: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
2249: where accounting_event_id = ltab_id(i)
2250: AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
2251: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
2252:
2253: debug ('update_ap_acct_date: total prepay history processed in ap_period_close_excps_gt: '||ltab_id.count );
2254:
2255: ltab_id.delete;
2256:
2257: return TRUE;