[Home] [Help]
131: -- DBMS_OUTPUT.PUT_LINE('l_to_date = ' || to_char(l_to_date));
132:
133:
134: /*---------------------------------------------------------------+
135: | Delete from CST_BIS_MARGIN_SUMMARY for the given Legal Entity
136: +---------------------------------------------------------------*/
137:
138: BEGIN
139:
140: -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
141: -- DBMS_OUTPUT.PUT_LINE('DELETE from TEMP.');
142: -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
143:
144: DELETE from CST_BIS_MARGIN_SUMMARY
145: WHERE legal_entity_id = l_le_id
146: and gl_date between l_from_date and l_to_date;
147:
148:
256: EXIT WHEN all_ous%NOTFOUND;
257:
258:
259: /*---------------------------------------------------------------+
260: | Insert into CST_BIS_MARGIN_SUMMARY for all the invoices booked
261: | against regular orders
262: +---------------------------------------------------------------*/
263:
264: l_stmt_id := 30;
266: -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
267: -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
268: -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
269:
270: INSERT INTO CST_BIS_MARGIN_SUMMARY
271: (
272: margin_pk,
273: build_id
274: ,source
399: -- This is a NOT NULL column in R12. Hence, no NVL needed here. Using this filter as Discrete orgs
400: -- may have values 'N' or '1'(possibly due to wrong setup). This might be present at customer instances also.
401:
402: /*---------------------------------------------------------------+
403: | Insert into CST_BIS_MARGIN_SUMMARY for IC-AR
404: +---------------------------------------------------------------*/
405:
406: l_stmt_id := 35;
407:
409: -- DBMS_OUTPUT.PUT_LINE('INSERT into TEMP.');
410: -- DBMS_OUTPUT.PUT_LINE('.*******************************************');
411:
412:
413: INSERT INTO CST_BIS_MARGIN_SUMMARY
414: (
415: margin_pk,
416: build_id
417: ,source
548: +---------------------------------------------------------------*/
549:
550: l_stmt_id := 40;
551:
552: INSERT INTO CST_BIS_MARGIN_SUMMARY
553: (
554: margin_pk,
555: build_id
556: ,source
686: +---------------------------------------------------------------*/
687:
688: l_stmt_id := 45;
689: /*
690: INSERT INTO CST_BIS_MARGIN_SUMMARY
691: (
692: margin_pk,
693: build_id
694: ,source
821: +---------------------------------------------------------------*/
822:
823: l_stmt_id := 50;
824:
825: UPDATE CST_BIS_MARGIN_SUMMARY rma
826: SET (
827: parent_rowid ,
828: order_number,
829: header_id,
910: | Developer: ADWAJAN
911: | Comments: Code for collecting AR for IC RMA transactions
912: +-------------------------------------------------------------*/
913:
914: INSERT INTO CST_BIS_MARGIN_SUMMARY
915: (
916: margin_pk
917: ,build_id
918: ,source
1046: +---------------------------------------------------------------*/
1047:
1048: l_stmt_id := 80;
1049:
1050: INSERT INTO CST_BIS_MARGIN_SUMMARY
1051: (
1052: margin_pk,
1053: build_id
1054: ,source
1127: rctlgd.code_combination_id,
1128: rct.CUSTOMER_TRX_ID,
1129: rctl.CUSTOMER_TRX_LINE_ID
1130: FROM
1131: CST_BIS_MARGIN_SUMMARY temp,
1132: ra_customer_trx_all rct,
1133: ra_customer_trx_lines_all rctl,
1134: ra_cust_trx_line_gl_dist_all rctlgd
1135: WHERE
1156: and temp.SOURCE = 'INVOICE'
1157: /* added join to org_id bug 2554225 */
1158: and temp.org_id = l_ou_id
1159: /* bug 2397230 */
1160: and temp.rowid in (select max(rowid) from CST_BIS_MARGIN_SUMMARY t1
1161: where t1.build_id = temp.build_id
1162: and t1.source = 'INVOICE'
1163: /* added join to org_id bug 2554225 */
1164: and t1.org_id = l_ou_id
1170: t1.line_number,
1171: t1.CUSTOMER_TRX_LINE_ID )
1172: and not exists
1173: (select 'x'
1174: from CST_BIS_MARGIN_SUMMARY t2,
1175: oe_order_lines_all oel
1176: where t2.source = 'RMA-INVOICE'
1177: and t2.build_id = temp.build_id
1178: /* added join to org_id bug 2554225 */
1208: would now collect for accounting_line_type = 2 (COGS valuation in the
1209: absence of deferred COGS accounting at customer) and accounting_line_type
1210: = 35 (Deferred COGS recognized, in the presence of deferred COGS acc)
1211: */
1212: INSERT INTO CST_BIS_MARGIN_SUMMARY
1213: (
1214: margin_pk,
1215: build_id
1216: ,source
1361: | in the regular Invoices part of the code
1362: | (l_stmt_id := 90).
1363: +-------------------------------------------------------------*/
1364:
1365: INSERT INTO CST_BIS_MARGIN_SUMMARY
1366: (
1367: margin_pk,
1368: build_id
1369: ,source
1483: | calculate COGS for the logical txns in the
1484: | Drop Ship scenario - 11.5.10 Impact Analysis
1485: +-------------------------------------------------------------*/
1486:
1487: INSERT INTO CST_BIS_MARGIN_SUMMARY
1488: (
1489: margin_pk,
1490: build_id
1491: ,source
1612: +---------------------------------------------------------------*/
1613:
1614: l_stmt_id := 110;
1615:
1616: UPDATE CST_BIS_MARGIN_SUMMARY rma
1617: SET (
1618: parent_rowid ,
1619: order_number,
1620: header_id,
1713: -- as part of Uptake for R12
1714:
1715: l_stmt_id := 140;
1716:
1717: UPDATE CST_BIS_MARGIN_SUMMARY temp
1718: SET territory_id =
1719: (SELECT territory_id
1720: FROM hz_cust_site_uses_all hsu -- Object ra_site_uses_all obsoleted in R12
1721: WHERE NVL(hsu.org_id, -999) = NVL(l_ou_id, NVL(hsu.org_id, -999))
1733: -- as part of Uptake for R12
1734:
1735: l_stmt_id := 150;
1736:
1737: UPDATE CST_BIS_MARGIN_SUMMARY temp
1738: SET customer_class_code =
1739: (SELECT customer_class_code
1740: FROM hz_cust_accounts -- Object ra_customers obsoleted in R12
1741: WHERE cust_account_id = temp.customer_id) -- ra_customers.customer_id migrated to hz_cust_accounts.cust_account_id
1751: -- as part of Uptake for R12
1752:
1753: l_stmt_id := 160;
1754:
1755: UPDATE CST_BIS_MARGIN_SUMMARY temp
1756: SET sold_to_customer_name =
1757: (SELECT hp.party_name -- references ra_customers.customer_name
1758: FROM hz_cust_accounts hca, hz_parties hp -- Object ra_customers obsoleted in R12
1759: WHERE hca.party_id = hp.party_id
1770: -- hz_cust_acct_sites_all as part of Uptake for R12
1771:
1772: l_stmt_id := 170;
1773:
1774: UPDATE CST_BIS_MARGIN_SUMMARY temp
1775: SET bill_to_customer_name =
1776: (SELECT hp.party_name
1777: FROM hz_cust_accounts hca
1778: , hz_parties hp
1789: customer_id is not null
1790: and gl_date between l_from_date and l_to_date
1791: and build_id = l_build_id;
1792:
1793: /* UPDATE CST_BIS_MARGIN_SUMMARY temp
1794: SET bill_to_customer_name =
1795: (SELECT rc.customer_name
1796: FROM ra_customers rc,
1797: ra_site_uses_all rsu,
1815: -- hz_cust_acct_sites_all as part of Uptake for R12
1816:
1817: l_stmt_id := 180;
1818:
1819: UPDATE CST_BIS_MARGIN_SUMMARY temp
1820: SET ship_to_customer_name =
1821: (SELECT hp.party_name
1822: FROM hz_cust_accounts hca
1823: , hz_parties hp
1835: and gl_date between l_from_date and l_to_date
1836: and build_id = l_build_id;
1837:
1838: /*
1839: UPDATE CST_BIS_MARGIN_SUMMARY temp
1840: SET ship_to_customer_name =
1841: (SELECT rc.customer_name
1842: FROM ra_customers rc,
1843: ra_site_uses_all rsu,
1859: +---------------------------------------------------------------*/
1860:
1861: l_stmt_id := 181;
1862:
1863: update CST_BIS_MARGIN_SUMMARY cmt
1864: set (PERIOD_NAME_YEAR, PERIOD_NUM_YEAR) =
1865: (select gp.period_name, gp.PERIOD_YEAR
1866: from
1867: gl_periods gp,
1884: | Update Period Quarter
1885: +---------------------------------------------------------------*/
1886:
1887: l_stmt_id := 182;
1888: update CST_BIS_MARGIN_SUMMARY cmt
1889: set (PERIOD_NAME_QTR, PERIOD_NUM_QTR, PERIOD_SEQ_QTR) =
1890: (select gp.period_name, gp.period_num,
1891: gp.PERIOD_YEAR * 10 + gp.period_num
1892: from
1909: | Update Period Month
1910: +---------------------------------------------------------------*/
1911:
1912: l_stmt_id := 183;
1913: update CST_BIS_MARGIN_SUMMARY cmt
1914: set (PERIOD_NAME_MONTH, PERIOD_NUM_MONTH, PERIOD_SEQ_MONTH) =
1915: (select gp.period_name, gp.period_num,
1916: gp.PERIOD_YEAR * 100 + gp.period_num
1917: from
1937: -- hz_cust_acct_sites_all as part of Uptake for R12
1938:
1939: l_stmt_id := 184;
1940:
1941: update CST_BIS_MARGIN_SUMMARY cmt
1942: set COUNTRY_CODE =
1943: (select hl.country
1944: from hz_locations hl
1945: ,hz_cust_site_uses_all hcsu
1956: and cmt.gl_date between l_from_date and l_to_date
1957: and cmt.build_id = l_build_id;
1958:
1959: /*
1960: update CST_BIS_MARGIN_SUMMARY cmt
1961: set COUNTRY_CODE =
1962: (select raa.country
1963: from ra_site_uses_all rsua,
1964: ra_addresses_all raa
1977: +---------------------------------------------------------------*/
1978:
1979:
1980: l_stmt_id := 185;
1981: update CST_BIS_MARGIN_SUMMARY cmt
1982: set (AREA_CODE, COUNTRY_NAME) =
1983: (select bthv.PARENT_TERRITORY_CODE, bthv.CHILD_TERRITORY_NAME
1984: from bis_territory_hierarchies_v bthv
1985: where
1997:
1998: if (app_col_name is not null) then
1999: --app_col_name1 := '''' || app_col_name || '''';
2000:
2001: sql_stmt := 'update CST_BIS_MARGIN_SUMMARY cmt set (REGION_CODE, region_name)= '
2002: || '(select :app_col_name , brv.name from RA_ADDRESSES ra,bis_regions_v brv '
2003: || 'where cmt.country_code = ra.country'
2004: || ' and ra.country = brv.COUNTRY_CODE'
2005: || ' and brv.REGION_CODE = :app_col_name ) where'
2017: +---------------------------------------------------------------*/
2018:
2019:
2020: l_stmt_id := 190;
2021: update CST_BIS_MARGIN_SUMMARY cmt
2022: set AREA_NAME =
2023: (select BAV.name
2024: from bis_areas_v BAV
2025: where
2035: | Update Category id for Items
2036: +---------------------------------------------------------------*/
2037:
2038: l_stmt_id := 200;
2039: update CST_BIS_MARGIN_SUMMARY temp
2040: set OE_ITEM_CATEGORY_ID =
2041: (select max(MC.category_id)
2042: from
2043: mtl_categories MC
2065: | Update Operating Unit Name
2066: +---------------------------------------------------------------*/
2067:
2068: l_stmt_id := 210;
2069: update CST_BIS_MARGIN_SUMMARY cmt
2070: set OPERATING_UNIT_NAME =
2071: (select HOU.name
2072: from hr_operating_units HOU
2073: where