[Home] [Help]
136: -- insert statement will populate all un-posted invoice distributions
137: -- if action is PERIOD_CLOSE only one row will be fetched to check
138: -- existance unposted transactions
139: if (g_period_start_date is not null and g_period_end_date is not null) then
140: insert into ap_period_close_excps_gt
141: ( invoice_id
142: ,invoice_distribution_id
143: ,invoice_payment_id -- 7318763
144: ,accounting_event_id
202: );
203:
204:
205: elsif(g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1) then
206: insert into ap_period_close_excps_gt
207: ( invoice_id
208: ,invoice_distribution_id
209: ,invoice_payment_id -- 7318763
210: ,accounting_event_id
268: );
269:
270: end if;
271:
272: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||' is:'||sql%rowcount);
273:
274: if g_action = G_ACTION_PERIOD_CLOSE and sql%rowcount > 0 then
275: -- current action is PERIOD_CLOSE and there are unposted invoices
276: -- and we cannot allow to close period hence return
277: return 'Y';
278: end if;
279:
280: if (g_period_start_date is not null and g_period_end_date is not null) then
281: insert into ap_period_close_excps_gt
282: ( invoice_id
283: ,invoice_distribution_id
284: ,accounting_event_id
285: ,accounting_date
338: and ( g_action <> G_ACTION_PERIOD_CLOSE
339: OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
340: );
341: elsif(g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1) then
342: insert into ap_period_close_excps_gt
343: ( invoice_id
344: ,invoice_distribution_id
345: ,accounting_event_id
346: ,accounting_date
401: );
402:
403: end if ;
404:
405: 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
406: || ' is:'||sql%rowcount);
407:
408: if g_action = G_ACTION_PERIOD_CLOSE and sql%rowcount > 0 then
409: -- current action is PERIOD_CLOSE and there are unposted self assessed tax dists
412: end if;
413:
414:
415: if (g_period_start_date is not null and g_period_end_date is not null) then
416: insert into ap_period_close_excps_gt
417: ( invoice_id
418: ,invoice_line_number
419: ,accounting_date
420: ,org_id
474: );
475:
476: elsif(g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1) then
477:
478: insert into ap_period_close_excps_gt
479: ( invoice_id
480: ,invoice_line_number
481: ,accounting_date
482: ,org_id
539:
540:
541:
542:
543: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_LINES_WITHOUT_DISTS||' is:'||sql%rowcount);
544:
545: if g_action = G_ACTION_PERIOD_CLOSE and sql%rowcount > 0 then
546: -- current action is PERIOD_CLOSE and there are lines without any distributions
547: -- so we cannot allow to close perio, hence return
549: end if;
550:
551: -- gagrawal
552: if (g_period_start_date is not null and g_period_end_date is not null) then
553: insert into ap_period_close_excps_gt
554: ( invoice_id
555: ,accounting_event_id
556: ,accounting_date
557: ,org_id
600: OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
601: );
602:
603: elsif(g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1) then
604: insert into ap_period_close_excps_gt
605: ( invoice_id
606: ,accounting_event_id
607: ,accounting_date
608: ,org_id
648: and ( g_action <> G_ACTION_PERIOD_CLOSE
649: OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
650: );
651: end if;
652: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PREPAY_HIST||' is:'||sql%rowcount);
653:
654:
655: if g_action = G_ACTION_PERIOD_CLOSE and sql%rowcount > 0 then
656: -- current action is PERIOD_CLOSE and there are lines without any distributions
665: -- PAYMENTS --
666: ----------------
667: <
668:
669: INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT
670: (payment_history_id
671: ,accounting_event_id
672: ,accounting_date
673: ,check_id
725: and ( g_action <> G_ACTION_PERIOD_CLOSE
726: OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
727: );
728:
729: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PMT_HISTORY||' is:'||sql%rowcount);
730:
731: if g_action = G_ACTION_PERIOD_CLOSE and sql%rowcount > 0 then
732: -- current action is PERIOD_CLOSE and there are lines without any distributions
733: -- so we cannot allow to close period, hence return
735: end if;
736:
737: if g_action = G_ACTION_SWEEP then -- populate GT ONLY when sweeping
738: -- get unaccounted invoice payments
739: insert into ap_period_close_excps_gt
740: (invoice_payment_id
741: ,accounting_event_id
742: ,accounting_date
743: ,check_id
792: AND aip.org_id = orgs.org_id
793: AND ac.check_id = aip.check_id
794: AND NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS';
795:
796: debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_INV_PMTS||' is:'||sql%rowcount);
797:
798: end if;
799:
800: IF g_action <> G_ACTION_PERIOD_CLOSE THEN
1443: SET POH.closed_date = g_sweep_to_date
1444: WHERE po_header_id in (SELECT PLL.PO_HEADER_ID
1445: FROM PO_LINE_LOCATIONS_ALL PLL,
1446: PO_DISTRIBUTIONS_ALL PD,
1447: AP_PERIOD_CLOSE_EXCPS_GT GT
1448: WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1449: AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1450: AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1451: AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1464: SET closed_date = g_sweep_to_date
1465: WHERE line_location_id in (SELECT PLL.LINE_LOCATION_ID
1466: FROM PO_LINE_LOCATIONS_ALL PLL,
1467: PO_DISTRIBUTIONS_ALL PD,
1468: AP_PERIOD_CLOSE_EXCPS_GT GT
1469: WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1470: AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1471: AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1472: AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1516: BEGIN
1517:
1518: INSERT into ZX_TAX_DIST_ID_GT
1519: (SELECT detail_tax_dist_id
1520: FROM ap_period_close_excps_gt
1521: WHERE detail_tax_dist_id is not null
1522: AND source_type = G_SRC_TYP_UNACCT_DISTS
1523: AND source_table_name in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1524: G_SRC_TAB_AP_SELF_TAX_DIST_ALL));
1629: ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, 'INV'
1630: ,G_SRC_TAB_AP_PMT_HISTORY,'PMT'
1631: ) source
1632: ,invoice_payment_id -- 7318763
1633: FROM ap_period_close_excps_gt gt
1634: WHERE gt.source_type in (G_SRC_TYP_UNACCT_DISTS, G_SRC_TYP_UNACCT_PMT_HISTORY,
1635: G_SRC_TYP_UNACCT_PREPAY_HIST)
1636: AND gt.source_table_name in (G_SRC_TAB_AP_INV_DISTS_ALL, G_SRC_TAB_AP_PMT_HISTORY,
1637: G_SRC_TAB_AP_SELF_TAX_DIST_ALL, G_SRC_TAB_AP_PREPAY_HIST)
1799: period_name = g_sweep_to_period,
1800: last_update_date = sysdate,
1801: last_updated_by = 5
1802: WHERE aid.invoice_distribution_id in (SELECT gt.invoice_distribution_id
1803: FROM ap_period_close_excps_gt gt
1804: WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
1805: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL)
1806: AND aid.posted_flag <> 'Y'
1807: returning invoice_distribution_id bulk collect into l_dbi_key_value_list;
1808:
1809: debug ('update_ap_acct_date: total records updated in ap_invoice_distributions_all: '||sql%rowcount);
1810:
1811: forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
1812: update ap_period_close_excps_gt gt
1813: set process_status_flag = 'Y'
1814: where invoice_distribution_id = l_dbi_key_value_list(i)
1815: AND gt.source_type =G_SRC_TYP_UNACCT_DISTS
1816: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL; -- 7318763
1820: p_operation => 'U',
1821: p_key_value_list => l_dbi_key_value_list,
1822: p_calling_sequence => 'AP_PERIOD_CLOSE_PKG.update_ap_acct_date');
1823:
1824: debug ('update_ap_acct_date: total distributions processed in ap_period_close_excps_gt: '||l_dbi_key_value_list.count);
1825:
1826: forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
1827: UPDATE ap_invoice_lines_all ail
1828: SET accounting_date = g_sweep_to_date,
1829: period_name = g_sweep_to_period,
1830: last_update_date = sysdate,
1831: last_updated_by = 5
1832: WHERE (ail.invoice_id, ail.line_number) in (SELECT gt.invoice_id, gt.invoice_line_number
1833: FROM ap_period_close_excps_gt gt
1834: WHERE gt.invoice_distribution_id = l_dbi_key_value_list(i)
1835: AND gt.source_type=G_SRC_TYP_UNACCT_DISTS
1836: AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL); -- 7318763
1837:
1845: period_name = g_sweep_to_period,
1846: last_update_date = sysdate,
1847: last_updated_by = 5
1848: WHERE astd.invoice_distribution_id in (SELECT gt.invoice_distribution_id
1849: FROM ap_period_close_excps_gt gt
1850: WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
1851: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
1852: AND astd.posted_flag <> 'Y'
1853: returning invoice_distribution_id bulk collect into ltab_id;
1854:
1855: debug ('update_ap_acct_date: total records updated in ap_self_assessed_tax_dist_all: '||sql%rowcount);
1856:
1857: forall i in ltab_id.first .. ltab_id.last
1858: update ap_period_close_excps_gt gt
1859: set process_status_flag = 'Y'
1860: where invoice_distribution_id = ltab_id(i)
1861: AND gt.source_type =G_SRC_TYP_UNACCT_DISTS
1862: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
1860: where invoice_distribution_id = ltab_id(i)
1861: AND gt.source_type =G_SRC_TYP_UNACCT_DISTS
1862: AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
1863:
1864: debug ('update_ap_acct_date: total self assessed tax distributions processed in ap_period_close_excps_gt: '||ltab_id.count);
1865:
1866: ltab_id.delete;
1867:
1868:
1872: last_update_date = sysdate,
1873: last_updated_by = 5
1874: WHERE (ail.invoice_id,ail.line_number) in
1875: (SELECT gt.invoice_id, gt.invoice_line_number
1876: FROM ap_period_close_excps_gt gt
1877: WHERE gt.source_type = G_SRC_TYP_LINES_WITHOUT_DISTS
1878: AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL)
1879: returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
1880: debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
1879: returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
1880: debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
1881:
1882: forall i in ltab_id.first..ltab_id.last
1883: update ap_period_close_excps_gt gt
1884: set process_status_flag = 'Y'
1885: where invoice_id = ltab_id(i)
1886: and invoice_line_number = ltab_line_num(i)
1887: AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
1886: and invoice_line_number = ltab_line_num(i)
1887: AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
1888: AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL; -- 7318763
1889:
1890: debug ('update_ap_acct_date: total invoice lines processed in ap_period_close_excps_gt: '||ltab_id.count );
1891:
1892: ltab_id.delete;
1893:
1894: UPDATE ap_invoice_payments_all aip
1896: period_name = g_sweep_to_period,
1897: last_update_date = sysdate,
1898: last_updated_by = 5
1899: WHERE aip.invoice_payment_id in (SELECT gt.invoice_payment_id
1900: FROM ap_period_close_excps_gt gt
1901: WHERE gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
1902: AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS)
1903: AND aip.posted_flag <> 'Y'
1904: returning invoice_payment_id bulk collect into ltab_id;
1905:
1906: debug ('update_ap_acct_date: total records updated in ap_invoice_payments_all: '||sql%rowcount);
1907:
1908: forall i in ltab_id.first .. ltab_id.last
1909: update ap_period_close_excps_gt gt
1910: set process_status_flag = 'Y'
1911: where invoice_payment_id = ltab_id(i)
1912: AND gt.source_type =G_SRC_TYP_UNACCT_INV_PMTS
1913: AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS; -- 7318763
1911: where invoice_payment_id = ltab_id(i)
1912: AND gt.source_type =G_SRC_TYP_UNACCT_INV_PMTS
1913: AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS; -- 7318763
1914:
1915: debug ('update_ap_acct_date: total invoice payments processed in ap_period_close_excps_gt: '||ltab_id.count );
1916:
1917: ltab_id.delete;
1918:
1919:
1921: SET accounting_date = g_sweep_to_date,
1922: last_update_date = sysdate,
1923: last_updated_by = 5
1924: WHERE aph.payment_history_id in (SELECT gt.payment_history_id
1925: FROM ap_period_close_excps_gt gt
1926: WHERE gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
1927: AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY)
1928: AND aph.posted_flag <> 'Y'
1929: returning aph.payment_history_id bulk collect into ltab_id;
1930:
1931: debug ('update_ap_acct_date: total records updated in ap_payment_history_all: '||sql%rowcount);
1932:
1933: forall i in ltab_id.first .. ltab_id.last
1934: update ap_period_close_excps_gt gt
1935: set process_status_flag = 'Y'
1936: where payment_history_id = ltab_id(i)
1937: AND gt.source_type =G_SRC_TYP_UNACCT_PMT_HISTORY
1938: AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY; -- 7318763
1935: set process_status_flag = 'Y'
1936: where payment_history_id = ltab_id(i)
1937: AND gt.source_type =G_SRC_TYP_UNACCT_PMT_HISTORY
1938: AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY; -- 7318763
1939: debug ('update_ap_acct_date: total payment history processed in ap_period_close_excps_gt: '||ltab_id.count );
1940:
1941: ltab_id.delete;
1942:
1943: -- gagrawal
1946: SET accounting_date = g_sweep_to_date,
1947: last_update_date = sysdate,
1948: last_updated_by = 5
1949: WHERE apph.accounting_event_id in (SELECT gt.accounting_event_id
1950: FROM ap_period_close_excps_gt gt
1951: WHERE gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
1952: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST
1953: AND gt.accounting_event_id IS NOT NULL)
1954: AND apph.posted_flag <> 'Y'
1957: debug ('update_ap_acct_date: total records updated in ap_prepay_history_all: '||sql%rowcount);
1958:
1959:
1960: forall i in ltab_id.first .. ltab_id.last
1961: update ap_period_close_excps_gt gt
1962: set process_status_flag = 'Y'
1963: where accounting_event_id = ltab_id(i)
1964: AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
1965: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
1963: where accounting_event_id = ltab_id(i)
1964: AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
1965: AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
1966:
1967: debug ('update_ap_acct_date: total prepay history processed in ap_period_close_excps_gt: '||ltab_id.count );
1968:
1969:
1970: ltab_id.delete;
1971: