[Home] [Help]
14: 13-Jun-2005 File Version: 116.4
15: Ramananda for bug#4428980. Removal of SQL LITERALs is done
16:
17: 18-JUL-2005 Ramananda for bug# 4499078, File Version # 120.2
18: Sequence JAI_AR_SUP_HDRS_ALL_S1 is used instead of JAI_AR_SUP_HDRS_ALL_S2 and JAI_AR_SUP_HDRS_ALL_S3
19:
20: 26-JUL-2005 Ramananda for bug#4499078, File Version # 120.3
21: Added column Interface_Line_Attribute2 column in Cursor GET_SUPP_INV_LINES
22:
156: -- CURSOR FOR CHECKING WHETHER AN INVOICE HAS AN EXISTING ENTRY --
157:
158: CURSOR CHK_FOR_SUPP_INV_ENT(ID NUMBER) IS
159: SELECT 1
160: FROM JAI_AR_SUP_HDRS_ALL
161: WHERE CUSTOMER_TRX_ID = ID ;
162:
163: -- CURSOR FOR SELECTING INVOICE FILTERED BY EFFECTIVE DATE AND TYPE --
164:
539: ORDER BY CUSTOMER_TRX_ID;
540:
541: CURSOR CHECK_FOR_INV(INV NUMBER) IS
542: SELECT customer_trx_id
543: FROM JAI_AR_SUP_HDRS_ALL
544: WHERE customer_trx_id = INV;
545:
546: CURSOR CHECK_SUPP_ASSESS_INV(STAT VARCHAR2,NO NUMBER) IS
547: SELECT 1
544: WHERE customer_trx_id = INV;
545:
546: CURSOR CHECK_SUPP_ASSESS_INV(STAT VARCHAR2,NO NUMBER) IS
547: SELECT 1
548: FROM JAI_AR_SUP_HDRS_ALL
549: WHERE customer_trx_id = NO
550: AND supp_inv_type = STAT;
551:
552: CURSOR get_inv_line( ino NUMBER,
818: of the table JAI_AR_SUP_LINES.
819:
820: 07-Jul-2007 Bgowrava for Bug#6139693, File Version 120.13
821: Added the NVL condition for the EXIST_NET_SALES_AMT fields in the update statements of
822: JAI_AR_SUP_HDRS_ALL.
823:
824: 12-Sep-2007 Bgowrava for bug#6400997, File Version 120.9.12000000.6
825: Added the columns SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS in the insert statements
826: and assigned values 'N' to both of them. This was done because the default values were
846: END IF;
847:
848: v_statement_id := '3';
849:
850: -- CURSOR TO CHECK IF AN INVOICE HAS ENTRIES EXISTING IN THE JAI_AR_SUP_HDRS_ALL --
851: OPEN CHECK_FOR_INV(FETCH_INV.CUSTOMER_TRX_ID);
852: FETCH CHECK_FOR_INV INTO INV_NO;
853:
854: IF CHECK_FOR_INV%NOTFOUND THEN -- 0000a1
1059: FETCH CHECK_SUPP_ASSESS_INV INTO CHECK_ASSESS_INV ;
1060:
1061: IF CHECK_SUPP_ASSESS_INV%NOTFOUND THEN
1062: v_statement_id := '21';
1063: INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1064: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE, LAST_UPDATE_DATE, CREATED_BY,
1065: CREATION_DATE, LAST_UPDATED_BY, EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT,
1066: SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1067: ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1063: INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1064: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE, LAST_UPDATE_DATE, CREATED_BY,
1065: CREATION_DATE, LAST_UPDATED_BY, EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT,
1066: SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1067: ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1068: FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS, SYSDATE, fnd_global.user_id,
1069: SYSDATE, fnd_global.user_id, OAMOUNT, NAMOUNT,
1070: I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1071: );--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1072:
1073: ELSE
1074: v_statement_id := '22';
1075:
1076: UPDATE JAI_AR_SUP_HDRS_ALL
1077: SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+OAMOUNT,
1078: NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NAMOUNT
1079: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1080: AND SUPP_INV_TYPE = STATUS;
1194: IF CHECK_SUPP_ASSESS_INV%NOTFOUND THEN
1195:
1196: v_statement_id := '37';
1197:
1198: INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1199: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1200: LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1201: EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT, SET_OF_BOOKS_ID,
1202: CUSTOMER_SITE, ORG_ID, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1199: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1200: LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1201: EXIST_NET_SALES_AMT, NEW_NET_SALES_AMT, SET_OF_BOOKS_ID,
1202: CUSTOMER_SITE, ORG_ID, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1203: ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1204: FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
1205: SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id,
1206: OLD_INVOICE_AMOUNT, NEW_INVOICE_AMOUNT, I.SET_OF_BOOKS_ID,
1207: I.BILL_TO_SITE_USE_ID, I.ORG_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1210: ELSIF CHECK_SUPP_ASSESS_INV%FOUND THEN
1211:
1212: v_statement_id := '38';
1213:
1214: UPDATE JAI_AR_SUP_HDRS_ALL
1215: SET EXIST_NET_SALES_AMT = nvl(EXIST_NET_SALES_AMT,0)+ OLD_INVOICE_AMOUNT,
1216: NEW_NET_SALES_AMT = nvl(NEW_NET_SALES_AMT,0) +NEW_INVOICE_AMOUNT
1217: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1218: AND SUPP_INV_TYPE = STATUS;
1634:
1635: v_statement_id := '78';
1636:
1637:
1638: INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1639: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1640: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE,
1641: CREATED_BY, SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1642: ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1638: INSERT INTO JAI_AR_SUP_HDRS_ALL(SUP_HDR_ID,
1639: CUSTOMER_TRX_ID, CUSTOMER_ID, TRX_DATE, SUPP_INV_TYPE,
1640: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE,
1641: CREATED_BY, SET_OF_BOOKS_ID, ORG_ID, CUSTOMER_SITE, BATCH_ID, SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1642: ) VALUES ( JAI_AR_SUP_HDRS_ALL_S.nextval,
1643: FETCH_INV.CUSTOMER_TRX_ID, I.BILL_TO_CUSTOMER_ID, FETCH_INV.TRX_DATE, STATUS,
1644: SYSDATE, fnd_global.user_id, NULL, SYSDATE,
1645: fnd_global.user_id, I.SET_OF_BOOKS_ID, I.ORG_ID, I.BILL_TO_SITE_USE_ID, FETCH_INV.BATCH_ID, 'N', 'N'
1646: );--Bgowrava for Bug#6400997, added SUPP_INV_RAISED_IND, CHOSEN_FOR_PROCESS
1719: WHERE CUSTOMER_TRX_LINE_ID = A.LINK_TO_CUST_TRX_LINE_ID
1720: AND SUP_INV_TYPE = STATUS;
1721:
1722: v_statement_id := '88';
1723: UPDATE JAI_AR_SUP_HDRS_ALL
1724: SET EXIST_EXCISE_AMT = NVL(EXIST_EXCISE_AMT,0)+A.TAX_AMOUNT
1725: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1726: AND SUPP_INV_TYPE = STATUS;
1727:
1733: AND SUP_INV_TYPE = STATUS;
1734:
1735: v_statement_id := '90';
1736:
1737: UPDATE JAI_AR_SUP_HDRS_ALL
1738: SET EXIST_OTHER_AMT = NVL(EXIST_OTHER_AMT,0)+A.TAX_AMOUNT
1739: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1740: AND SUPP_INV_TYPE = STATUS;
1741: END IF;
1748: AND SUP_INV_TYPE = STATUS;
1749:
1750: v_statement_id := '92';
1751:
1752: UPDATE JAI_AR_SUP_HDRS_ALL
1753: SET NEW_EXCISE_AMT = NVL(NEW_EXCISE_AMT,0)+B.TAX_AMOUNT
1754: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1755: AND SUPP_INV_TYPE = STATUS;
1756:
1763: AND SUP_INV_TYPE = STATUS;
1764:
1765: v_statement_id := '94';
1766:
1767: UPDATE JAI_AR_SUP_HDRS_ALL
1768: SET NEW_OTHER_AMT = NVL(NEW_OTHER_AMT,0)+B.TAX_AMOUNT
1769: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID
1770: AND SUPP_INV_TYPE = STATUS;
1771:
1781: WHERE CUSTOMER_TRX_LINE_ID = v_cust_trx_line_no_s;
1782:
1783: v_statement_id := '96';
1784:
1785: UPDATE JAI_AR_SUP_HDRS_ALL
1786: SET EXCISE_DIFF_AMT = NVL(NEW_EXCISE_AMT,0) - NVL(EXIST_EXCISE_AMT,0),
1787: OTHER_DIFF_AMT = NVL(NEW_OTHER_AMT,0) - NVL(EXIST_OTHER_AMT,0)
1788: WHERE CUSTOMER_TRX_ID = FETCH_INV.CUSTOMER_TRX_ID;
1789:
1865: B.invoice_currency_code,
1866: B.primary_salesrep_id,
1867: B.term_id,
1868: C.organization_id
1869: FROM JAI_AR_SUP_HDRS_ALL A,
1870: RA_CUSTOMER_TRX_ALL B,
1871: JAI_AR_TRXS C
1872: where A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
1873: AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
1879: customer_id,
1880: customer_site,
1881: supp_inv_type,
1882: org_id
1883: from JAI_AR_SUP_HDRS_ALL;
1884:
1885: cursor get_curr(id number,cid number,sid number) is
1886: select distinct invoice_currency_code,
1887: primary_salesrep_id,
1902: a.description,
1903: a.unit_code, --2001/04/10 Anuradha Parthasarathy
1904: c.organization_id, --2001/05/10 Anuradha Parthasarathy,
1905: a.interface_line_attribute2
1906: from JAI_AR_SUP_HDRS_ALL b,
1907: JAI_AR_SUP_LINES a,
1908: JAI_AR_TRXS c
1909: where a.customer_trx_id = b.customer_trx_id
1910: and a.customer_trx_id = c.customer_trx_id
1935: B.CUSTOMER_ID,
1936: B.supp_INV_TYPE ,
1937: a.INVENTORY_ITEM_ID,
1938: a.interface_line_attribute2
1939: from JAI_AR_SUP_HDRS_ALL b,
1940: JAI_AR_SUP_LINES a,
1941: RA_CUST_TRX_LINE_SALESREPS_ALL C
1942: where a.customer_trx_id = b.customer_trx_id
1943: and a.sup_inv_type = b.supp_inv_type
1962: B.CUSTOMER_ID,
1963: B.supp_INV_TYPE ,
1964: INVENTORY_ITEM_ID,
1965: D.SALES_CREDIT_TYPE_ID
1966: from JAI_AR_SUP_HDRS_ALL b,
1967: JAI_AR_SUP_LINES a,
1968: RA_CUST_TRX_LINE_SALESREPS_ALL C,
1969: ra_salesreps_all d
1970: where a.customer_trx_id = b.customer_trx_id
2078: IF CHSN_FOR_CNSLDT = 'N' OR CHSN_FOR_CNSLDT IS NULL THEN
2079: FOR SUPP_INV IN GET_SUPP_INV
2080: LOOP
2081: if supp_inv.supp_inv_type = 'CR' then
2082: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2083: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2084: elsif supp_inv.supp_inv_type = 'DB' then
2085: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2086: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2079: FOR SUPP_INV IN GET_SUPP_INV
2080: LOOP
2081: if supp_inv.supp_inv_type = 'CR' then
2082: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2083: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2084: elsif supp_inv.supp_inv_type = 'DB' then
2085: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2086: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2087: elsif supp_inv.supp_inv_type = 'SI' then
2081: if supp_inv.supp_inv_type = 'CR' then
2082: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2083: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2084: elsif supp_inv.supp_inv_type = 'DB' then
2085: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2086: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2087: elsif supp_inv.supp_inv_type = 'SI' then
2088: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2089: end if ;
2082: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2083: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2084: elsif supp_inv.supp_inv_type = 'DB' then
2085: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2086: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2087: elsif supp_inv.supp_inv_type = 'SI' then
2088: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2089: end if ;
2090: open get_address(supp_inv.bill_to_site_use_id);
2084: elsif supp_inv.supp_inv_type = 'DB' then
2085: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2086: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2087: elsif supp_inv.supp_inv_type = 'SI' then
2088: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2089: end if ;
2090: open get_address(supp_inv.bill_to_site_use_id);
2091: fetch get_address into v_address;
2092: close get_address;
2237: sysdate);
2238: END LOOP;
2239: END LOOP;
2240:
2241: UPDATE JAI_AR_SUP_HDRS_ALL
2242: SET SUPPLEMENTARY_NUM = V_SEQ,
2243: SUPP_INV_DATE = SYSDATE,
2244: SUPP_INV_RAISED_IND = 'Y',
2245: LAST_UPDATE_DATE = SYSDATE,
2252: Elsif CHSN_FOR_CNSLDT = 'Y' THEN
2253: for cnsldt_header in get_cnsldt_header
2254: loop
2255: IF cnsldt_header.supp_inv_type = 'CR' then
2256: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2257: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2258: elsif cnsldt_header.supp_inv_type = 'DB' then
2259: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2260: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2253: for cnsldt_header in get_cnsldt_header
2254: loop
2255: IF cnsldt_header.supp_inv_type = 'CR' then
2256: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2257: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2258: elsif cnsldt_header.supp_inv_type = 'DB' then
2259: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2260: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2261: elsif cnsldt_header.supp_inv_type = 'SI' then
2255: IF cnsldt_header.supp_inv_type = 'CR' then
2256: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2257: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2258: elsif cnsldt_header.supp_inv_type = 'DB' then
2259: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2260: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2261: elsif cnsldt_header.supp_inv_type = 'SI' then
2262: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2263: end if ;
2256: --select JAI_AR_SUP_HDRS_ALL_S3.nextval into v_seq from dual;
2257: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2258: elsif cnsldt_header.supp_inv_type = 'DB' then
2259: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2260: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2261: elsif cnsldt_header.supp_inv_type = 'SI' then
2262: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2263: end if ;
2264:
2258: elsif cnsldt_header.supp_inv_type = 'DB' then
2259: --select JAI_AR_SUP_HDRS_ALL_S2.nextval into v_seq from dual;
2260: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2261: elsif cnsldt_header.supp_inv_type = 'SI' then
2262: select JAI_AR_SUP_HDRS_ALL_S1.nextval into v_seq from dual;
2263: end if ;
2264:
2265: for inv_cnsldt in get_inv_cnsldt(cnsldt_header.set_of_books_id,cnsldt_header.customer_id,
2266: cnsldt_header.customer_site,cnsldt_header.supp_inv_type)
2429: end loop;
2430: end loop;
2431: end loop;
2432:
2433: UPDATE JAI_AR_SUP_HDRS_ALL
2434: SET SUPPLEMENTARY_NUM = V_SEQ,
2435: SUPP_INV_DATE = SYSDATE,
2436: SUPP_INV_RAISED_IND = 'Y',
2437: LAST_UPDATE_DATE = SYSDATE,