[Home] [Help]
14: This procedure doesnot COMMIT the data if it is called from an Application
15:
16: - PROCESS_TRANSACTION
17: Driving procedure that validates the transaction processing and if eligible, then calls different API's based on
18: trasaction type for processing. This should never be invoked directly as this needs data in JAI_RCV_TRANSACTIONS and which
19: is populated in batch procedure only.
20: - POPULATE_DETAILS
21: UPDATEs JAI_RCV_TRANSACTIONS with details(transaction,setup, validity) of the transaction that will be used
22: while processing the transaction
17: Driving procedure that validates the transaction processing and if eligible, then calls different API's based on
18: trasaction type for processing. This should never be invoked directly as this needs data in JAI_RCV_TRANSACTIONS and which
19: is populated in batch procedure only.
20: - POPULATE_DETAILS
21: UPDATEs JAI_RCV_TRANSACTIONS with details(transaction,setup, validity) of the transaction that will be used
22: while processing the transaction
23: - VALIDATE_TRANSACTION
24: Validates applicability of Transaction for Normal and Cenvat Accounting, RG Processing. Finally after validation, updates
25: process_flag, cenvat_rg_flag, messages of JAI_RCV_TRANSACTIONS with relevant details. Further these values are used
21: UPDATEs JAI_RCV_TRANSACTIONS with details(transaction,setup, validity) of the transaction that will be used
22: while processing the transaction
23: - VALIDATE_TRANSACTION
24: Validates applicability of Transaction for Normal and Cenvat Accounting, RG Processing. Finally after validation, updates
25: process_flag, cenvat_rg_flag, messages of JAI_RCV_TRANSACTIONS with relevant details. Further these values are used
26: to decide whether to proceed or not for Accounting and Cenvat processing
27: - PROCESS_ISO_TRANSACTION
28: RETURNs true if ISO Entries needs to be passed for transaction, else returns false. This is basically an applicability func.
29: - GET_ANCESTOR_ID
186:
187: 17. 17-Jul-2005 Sanjikum for Bug#4495135, File Version 117.1
188: Following changes are done in procedure - populate_details
189: 1) Added the variable - ln_tax_apportion_factor and populated the same using - get_apportion_factor(p_transaction_id)
190: 2) Added the call to jai_rcv_transactions_pkg.update_row to update jai_rcv_transactions.tax_apportion_factor
191:
192: 18. 27/07/2005 Ramananda for Bug#4516577, Version 120.2
193: Problem
194: -------
220: "if p_process_vat_flag = jai_constants.successful THEN
221: goto end_of_vat_validation;
222: end if;"
223:
224: Column process_vat_flag changed to process_vat_status (jai_rcv_transactions).
225: This issue is identified while compiling the object as a part of this fix.
226:
227: Dependency due to this Bug
228: --------------------------
239: jai_rcv_rgm_clm.plb (120.2)
240:
241: 18. 01/09/2005 Bug4586752. Added by Lakshmi gopalsami Version 120.5
242: Assigned the value of location_id in populate_details
243: and used for populating jai_rcv_transactions
244: Dependency (Functional)
245: ----------------------
246: JAIRGMCT.fmb 120.2
247: JAIRGMPT.fmb 120.3
277: Changes are done in get_ancestor_id function.
278:
279: 24. 16-Feb-2007 srjayara for bug 5064235 -- forward porting for bug# 5054114 in 11i
280: File version 120.15
281: Issue : The subinventory's location_id is not getting populated in ja_in_rcv_transactions for
282: 'RECEIVE' line
283: Fix : The subinventory's location_id will be populated in ja_in_rcv_transactions for 'RECEIVE' line
284: by fetching the subinventory from rcv_transactions 'DELIVER' line.
285: 25. 21-Feb-2007 CSahoo for BUG 5344225, File Version 120.16
279: 24. 16-Feb-2007 srjayara for bug 5064235 -- forward porting for bug# 5054114 in 11i
280: File version 120.15
281: Issue : The subinventory's location_id is not getting populated in ja_in_rcv_transactions for
282: 'RECEIVE' line
283: Fix : The subinventory's location_id will be populated in ja_in_rcv_transactions for 'RECEIVE' line
284: by fetching the subinventory from rcv_transactions 'DELIVER' line.
285: 25. 21-Feb-2007 CSahoo for BUG 5344225, File Version 120.16
286: Forward porting of 11i BUG 5343848
287: Added two input parameters p_request_id,p_group_id to the procedure process_batch.
280: File version 120.15
281: Issue : The subinventory's location_id is not getting populated in ja_in_rcv_transactions for
282: 'RECEIVE' line
283: Fix : The subinventory's location_id will be populated in ja_in_rcv_transactions for 'RECEIVE' line
284: by fetching the subinventory from rcv_transactions 'DELIVER' line.
285: 25. 21-Feb-2007 CSahoo for BUG 5344225, File Version 120.16
286: Forward porting of 11i BUG 5343848
287: Added two input parameters p_request_id,p_group_id to the procedure process_batch.
288: Added a parameter request_id Default null
291: This call will wait till the RVCTP concurrent is completed.
292:
293: Added a parameter group_id Default null
294: If the group_id is not null
295: Run a infinite loop till the data in rcv_transactions_interface table
296: is purged for the particular group_id.
297:
298: The rest of the code in the concurrent is processed only after
299: the data is purged.
590: attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
591: , routing_header_id -- porting of Bug#3949109 (3927371)
592: , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
593: , po_line_location_id
594: FROM rcv_transactions
595: WHERE transaction_id = cp_transaction_id;
596:
597: r_trx c_trx%ROWTYPE;
598: r_base_trx c_base_trx%ROWTYPE;
604:
605: -- Bug 5581319. Added by Lakshmi Gopalsami
606: -- Increased the size of lv_localpath from 100 to 2000.
607: --
608: lv_localpath jai_rcv_transactions.codepath%TYPE; --VARCHAR2(2000); --File.Sql.35 Cbabu := '';
609: ln_dup_chk NUMBER; --File.Sql.35 Cbabu := 0;
610:
611: lv_process_status VARCHAR2(2);
612: lv_process_message VARCHAR2(1000);
979: jai_po_osp_hdrs jpoh
980: WHERE jpol.form_id = jpoh.form_id
981: AND (jpoh.po_header_id, jpol.po_line_id ) IN
982: ( SELECT po_header_id, po_line_id
983: FROM rcv_transactions
984: WHERE transaction_id = p_transaction_id
985: );
986: ln_line_id NUMBER ;
987: ln_ret_value VARCHAR2(3);
1012: p_process_message OUT NOCOPY VARCHAR2
1013: ) IS
1014:
1015: r_trx c_trx%ROWTYPE;
1016: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1017: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1018: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1019: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1020: lv_common_err_mesg VARCHAR2(1000);
1013: ) IS
1014:
1015: r_trx c_trx%ROWTYPE;
1016: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1017: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1018: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1019: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1020: lv_common_err_mesg VARCHAR2(1000);
1021:
1014:
1015: r_trx c_trx%ROWTYPE;
1016: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1017: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1018: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1019: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1020: lv_common_err_mesg VARCHAR2(1000);
1021:
1022: lv_codepath VARCHAR2(1996);
1015: r_trx c_trx%ROWTYPE;
1016: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1017: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1018: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1019: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1020: lv_common_err_mesg VARCHAR2(1000);
1021:
1022: lv_codepath VARCHAR2(1996);
1023: ln_processed_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
1025:
1026: r_tax jai_rcv_excise_processing_pkg.tax_breakup;
1027: lv_breakup_type VARCHAR2(15);
1028:
1029: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
1030: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
1031: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
1032:
1033: ln_dlry_trx_id number;--12817175
1026: r_tax jai_rcv_excise_processing_pkg.tax_breakup;
1027: lv_breakup_type VARCHAR2(15);
1028:
1029: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
1030: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
1031: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
1032:
1033: ln_dlry_trx_id number;--12817175
1034: ln_inclusive_amt number; -- 12817175
1027: lv_breakup_type VARCHAR2(15);
1028:
1029: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
1030: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
1031: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
1032:
1033: ln_dlry_trx_id number;--12817175
1034: ln_inclusive_amt number; -- 12817175
1035: r_dlry_trx c_trx%ROWTYPE;--12817175
1037:
1038:
1039: CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
1040: select transaction_id, shipment_line_id, organization_type
1041: from JAI_RCV_TRANSACTIONS
1042: where (transaction_type IN (lv_ttype_deliver, lv_type_rtr) --'DELIVER', 'RETURN TO RECEIVING')
1043: or (transaction_type= lv_ttype_correct and parent_transaction_type = lv_ttype_deliver) --'CORRECT' , 'DELIVER'
1044: )
1045: and tax_transaction_id = cp_tax_transaction_id
1072:
1073:
1074:
1075:
1076: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1077: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1078: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1079:
1080: /*Bug 8648138 - Start*/
1073:
1074:
1075:
1076: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1077: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1078: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1079:
1080: /*Bug 8648138 - Start*/
1081: ln_invoice_id ap_invoice_distributions_all.invoice_id%TYPE;
1074:
1075:
1076: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1077: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1078: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1079:
1080: /*Bug 8648138 - Start*/
1081: ln_invoice_id ap_invoice_distributions_all.invoice_id%TYPE;
1082:
1408: --p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
1409: /*dbms_output.put_line('Unclaim#PRC_FLG#'||p_process_flag);*/
1410: END IF;
1411:
1412: UPDATE JAI_RCV_TRANSACTIONS
1413: SET CENVAT_RG_STATUS = 'X',
1414: cenvat_rg_message = 'Cenvat Unclaimed'
1415: WHERE CURRENT OF c_trxs_for_unclaim;
1416:
1420: END LOOP;
1421:
1422: lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
1423:
1424: UPDATE JAI_RCV_TRANSACTIONS
1425: SET CENVAT_RG_STATUS = 'X',
1426: cenvat_rg_message = 'Cenvat Unclaimed'
1427: WHERE transaction_id = temp_rec.transaction_id
1428: -- following is to take care of Pending DELIVER and RTR and related CORRECTS
1603: /* added by nprashar for bug 13880102 */
1604:
1605: loop_continue EXCEPTION;
1606:
1607: TYPE process_msg_tab_type IS TABLE OF JAI_RCV_TRANSACTIONS.process_message%TYPE
1608: INDEX BY PLS_INTEGER;
1609:
1610: process_msg_tab process_msg_tab_type;
1611:
1632:
1633: Cursor c_interface_exists(cp_group_id IN NUMBER)
1634: IS
1635: select 1
1636: from rcv_transactions_interface
1637: where group_id = cp_group_id
1638: and rownum=1 ;
1639:
1640: Cursor c_interface_error(cp_group_id IN NUMBER)
1639:
1640: Cursor c_interface_error(cp_group_id IN NUMBER)
1641: IS
1642: select 1
1643: from rcv_transactions_interface
1644: where
1645: group_id = cp_group_id and
1646: (transaction_status_code = 'ERROR' or processing_status_code = 'ERROR') ;
1647:
1647:
1648: ln_error NUMBER ;
1649: --ended, CSahoo for Bug 5344225
1650:
1651: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1648: ln_error NUMBER ;
1649: --ended, CSahoo for Bug 5344225
1650:
1651: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1656:
1649: --ended, CSahoo for Bug 5344225
1650:
1651: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1656:
1657: lv_common_err_mesg VARCHAR2(500);
1650:
1651: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1656:
1657: lv_common_err_mesg VARCHAR2(500);
1658: ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1651: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1652: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1653: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1654: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1655: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1656:
1657: lv_common_err_mesg VARCHAR2(500);
1658: ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1659: ln_batch_id NUMBER(15);
1665: /*CURSOR c_trxns_to_populate_dtls IS
1666: SELECT rowid, transaction_id, shipment_line_id, process_status, cenvat_rg_status,
1667: transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg
1668: , attribute_category -- Vijay Shankar for Bug#4250171
1669: FROM JAI_RCV_TRANSACTIONS a
1670: WHERE (p_organization_id IS NULL OR organization_id = p_organization_id)
1671: AND a.receipt_num IS NULL
1672: AND (p_shipment_header_id IS NULL OR a.shipment_header_id = p_shipment_header_id)
1673: AND
1683: CURSOR c_get_transactions IS
1684: SELECT rowid, transaction_id, process_status, cenvat_rg_status, process_message, cenvat_rg_message,
1685: transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg,
1686: shipment_line_id -- Vijay Shankar for Bug#3940588
1687: FROM JAI_RCV_TRANSACTIONS a
1688: WHERE ( p_simulate_flag = 'Y'
1689: OR
1690: ( process_status IS NULL OR process_status IN ('N', 'E','P') OR cenvat_rg_status IN ('N', 'E','P')
1691: -- following condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation
1729: PROCEDURE ja_in_populate_details IS
1730:
1731: CURSOR c_lock_rcv_dtls IS
1732: SELECT rowid
1733: FROM jai_rcv_transactions
1734: WHERE transaction_id IN
1735: ( SELECT transaction_id
1736: FROM jai_rtp_populate_t)
1737: FOR UPDATE OF transaction_id ;
1761: parent_transaction_type,
1762: receipt_num,
1763: cenvat_claimed_ptg,
1764: attribute_category
1765: FROM jai_rcv_transactions a
1766: WHERE organization_id = p_organization_id
1767: AND a.receipt_num IS NULL
1768: AND p_called_from = 'JAINPORE'
1769: AND ( --Added date parameters for bug # 13880102
1795: parent_transaction_type,
1796: receipt_num,
1797: cenvat_claimed_ptg,
1798: attribute_category
1799: FROM jai_rcv_transactions a
1800: WHERE organization_id = p_organization_id
1801: AND a.shipment_header_id = p_shipment_header_id
1802: AND a.receipt_num IS NULL
1803: AND EXISTS (SELECT 1
1823:
1824: PROCEDURE ja_in_get_transactions IS
1825: CURSOR c_lock_rcv_trans IS
1826: SELECT rowid
1827: FROM jai_rcv_transactions
1828: WHERE transaction_id IN
1829: ( SELECT transaction_id
1830: FROM jai_rtp_trans_t )
1831: FOR UPDATE OF transaction_id ;
1857: parent_transaction_type,
1858: receipt_num,
1859: cenvat_claimed_ptg,
1860: shipment_line_id
1861: FROM jai_rcv_transactions a
1862: WHERE organization_id = p_organization_id
1863: AND transaction_type = p_transaction_type
1864: AND parent_transaction_type = p_parent_trx_type
1865: AND receipt_num IS NOT NULL
1901: parent_transaction_type,
1902: receipt_num,
1903: cenvat_claimed_ptg,
1904: shipment_line_id
1905: FROM jai_rcv_transactions a
1906: WHERE organization_id = p_organization_id
1907: AND transaction_type = p_transaction_type
1908: AND receipt_num IS NOT NULL
1909: AND ( p_simulate_flag = 'Y'
1947: parent_transaction_type,
1948: receipt_num,
1949: cenvat_claimed_ptg,
1950: shipment_line_id
1951: FROM jai_rcv_transactions a
1952: WHERE organization_id = p_organization_id
1953: AND receipt_num IS NOT NULL
1954: AND ( p_simulate_flag = 'Y'
1955: OR
1990: parent_transaction_type,
1991: receipt_num,
1992: cenvat_claimed_ptg,
1993: shipment_line_id
1994: FROM jai_rcv_transactions a
1995: WHERE organization_id = p_organization_id
1996: AND shipment_header_id = p_shipment_header_id AND
1997: ( p_simulate_flag = 'Y'
1998: OR
2196: WHERE shipment_header_id = p_shipment_header_id
2197: AND tax_modified_flag IN ('Y', 'X')
2198: -- This condition will take not to update tax modified flag incase localization table is not populated
2199: -- eg.Incase of Unordered Receipt, Until MATCH happens we should not update the tax_modified_flag to 'Y'
2200: AND exists (select 1 from JAI_RCV_TRANSACTIONS
2201: where shipment_line_id = a.shipment_line_id);
2202:
2203: -- This Commit is a definitive Commit that has to happen
2204: COMMIT;
2295: errbuf := lv_process_message;
2296: retcode := jai_constants.request_error;
2297: RETURN;
2298: ELSIF p_commit_switch = 'Y' THEN
2299: -- first commit to save all the populated details of JAI_RCV_TRANSACTIONS
2300: COMMIT;
2301: END IF;*/
2302:
2303:
2428:
2429: IF p_simulate_flag = 'Y' THEN
2430: FND_FILE.put_line(FND_FILE.log, 'Codepath->'||lv_codepath);
2431: ELSE
2432: UPDATE JAI_RCV_TRANSACTIONS
2433: SET codepath = lv_codepath
2434: --WHERE rowid = trx.row_id
2435: --commented the above and added the below by sanjikum for Bug#4929410
2436: WHERE transaction_id = trx.transaction_id;
2535: nvl(b.receipt_excise_rate,0) receipt_excise_rate,
2536: nvl(b.rtv_excise_rate, nvl(b.receipt_excise_rate, 0)) rtv_excise_rate,
2537: decode( b.excise_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_excise_in_batch,
2538: decode( b.vat_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_vat_in_batch
2539: from JAI_RCV_TRANSACTIONS a, jai_rcv_rtv_batch_trxs b
2540: where a.transaction_id = b.transaction_id
2541: and (pn_min_transaction_id is null or a.transaction_id >= pn_min_transaction_id)
2542: and (pn_max_transaction_id is null or a.transaction_id <= pn_max_transaction_id)
2543: and
2553: where regime_code = cpv_regime_code;
2554:
2555: ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2556:
2557: lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2558: ld_excise_invoice_date DATE;
2559: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2560: ld_gen_excise_invoice_date DATE;
2561:
2555: ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2556:
2557: lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2558: ld_excise_invoice_date DATE;
2559: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2560: ld_gen_excise_invoice_date DATE;
2561:
2562: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2563: ld_vat_invoice_date DATE;
2558: ld_excise_invoice_date DATE;
2559: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2560: ld_gen_excise_invoice_date DATE;
2561:
2562: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2563: ld_vat_invoice_date DATE;
2564: lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2565: ld_gen_vat_invoice_date DATE;
2566:
2560: ld_gen_excise_invoice_date DATE;
2561:
2562: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2563: ld_vat_invoice_date DATE;
2564: lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2565: ld_gen_vat_invoice_date DATE;
2566:
2567: lv_same_invoice_no_flag VARCHAR2(1);
2568:
2574: lv_errbuf VARCHAR2(1000);
2575: lv_statement_id VARCHAR2(4);
2576:
2577:
2578: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2575: lv_statement_id VARCHAR2(4);
2576:
2577:
2578: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583:
2576:
2577:
2578: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583:
2584: lv_excise_processing_reqd VARCHAR2(1);
2577:
2578: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583:
2584: lv_excise_processing_reqd VARCHAR2(1);
2585: lv_vat_processing_reqd VARCHAR2(1);
2578: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2579: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2580: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2581: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583:
2584: lv_excise_processing_reqd VARCHAR2(1);
2585: lv_vat_processing_reqd VARCHAR2(1);
2586: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2582: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2583:
2584: lv_excise_processing_reqd VARCHAR2(1);
2585: lv_vat_processing_reqd VARCHAR2(1);
2586: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2587: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2588:
2589: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2590: r_trx_after_processing c_trx%ROWTYPE;
2583:
2584: lv_excise_processing_reqd VARCHAR2(1);
2585: lv_vat_processing_reqd VARCHAR2(1);
2586: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2587: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2588:
2589: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2590: r_trx_after_processing c_trx%ROWTYPE;
2591: lb_err_flag boolean;
2585: lv_vat_processing_reqd VARCHAR2(1);
2586: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2587: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2588:
2589: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2590: r_trx_after_processing c_trx%ROWTYPE;
2591: lb_err_flag boolean;
2592: ln_processed_cnt number;
2593:
2797:
2798: if rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE then
2799:
2800: lv_statement_id := '24';
2801: update JAI_RCV_TRANSACTIONS a
2802: set excise_invoice_no = lv_excise_invoice_no,
2803: excise_invoice_date = ld_excise_invoice_date,
2804: --attribute3 = pn_batch_num,
2805: last_update_date = sysdate,
2825:
2826: if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null then
2827: if rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE then
2828: lv_statement_id := '26';
2829: update JAI_RCV_TRANSACTIONS a
2830: set vat_invoice_no = lv_vat_invoice_no,
2831: vat_invoice_date = ld_vat_invoice_date,
2832: --attribute4 = pn_batch_num,
2833: last_update_date = sysdate,
3010:
3011: lv_statement_id := '31.1';
3012: /*
3013: commented for bug#13364465
3014: jai_rcv_transactions_pkg.update_attributes(
3015: p_transaction_id => rtv_rec.transaction_id,
3016: p_attribute1 => lv_excise_inv_gen_action,
3017: p_attribute2 => lv_vat_inv_gen_action
3018: );*/
3017: p_attribute2 => lv_vat_inv_gen_action
3018: );*/
3019:
3020: --added for bug 13364465
3021: FND_FILE.put_line(FND_FILE.log, 'process_rtv call to jai_rcv_transactions_pkg.update_inv_stat_and_no rtv_rec.transaction_id '||rtv_rec.transaction_id
3022: ||' lv_excise_inv_gen_action '||lv_excise_inv_gen_action||' lv_vat_inv_gen_action '||lv_vat_inv_gen_action );
3023:
3024: jai_rcv_transactions_pkg.update_inv_stat_and_no(
3025: p_transaction_id => rtv_rec.transaction_id,
3020: --added for bug 13364465
3021: FND_FILE.put_line(FND_FILE.log, 'process_rtv call to jai_rcv_transactions_pkg.update_inv_stat_and_no rtv_rec.transaction_id '||rtv_rec.transaction_id
3022: ||' lv_excise_inv_gen_action '||lv_excise_inv_gen_action||' lv_vat_inv_gen_action '||lv_vat_inv_gen_action );
3023:
3024: jai_rcv_transactions_pkg.update_inv_stat_and_no(
3025: p_transaction_id => rtv_rec.transaction_id,
3026: p_excise_inv_gen_status => lv_excise_inv_gen_action,
3027: p_vat_inv_gen_status => lv_vat_inv_gen_action
3028: );
3052: end if;
3053: -- end if;
3054:
3055: lv_statement_id := '35';
3056: UPDATE JAI_RCV_TRANSACTIONS
3057: SET codepath = lv_codepath
3058: WHERE current of c_rtv_trxs;
3059:
3060: <
3108: attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
3109: , routing_header_id -- porting of Bug#3949109 (3927371)
3110: , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
3111: , po_line_location_id
3112: FROM rcv_transactions
3113: WHERE transaction_id = cp_transaction_id;
3114:
3115: r_trx c_trx%ROWTYPE;
3116: r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
3114:
3115: r_trx c_trx%ROWTYPE;
3116: r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
3117:
3118: lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
3119: ln_cenvat_claimed_ptg NUMBER;
3120:
3121: lv_object_code VARCHAR2(10); --File.Sql.35 Cbabu := 'RCPT_TRXN:';
3122: lv_statement_id VARCHAR2(5);
3123:
3124: lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
3125: lv_temp VARCHAR2(100);
3126:
3127: lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
3128: lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
3129:
3130: lv_process_status VARCHAR2(2); --added by ssawant
3131: lv_qty_register_entry_type VARCHAR2(20) := null; /*bug 7662347*/ /* Increased size from 2 to 20 for bug #12818250 by vkavulur */
3124: lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
3125: lv_temp VARCHAR2(100);
3126:
3127: lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
3128: lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
3129:
3130: lv_process_status VARCHAR2(2); --added by ssawant
3131: lv_qty_register_entry_type VARCHAR2(20) := null; /*bug 7662347*/ /* Increased size from 2 to 20 for bug #12818250 by vkavulur */
3132:
3129:
3130: lv_process_status VARCHAR2(2); --added by ssawant
3131: lv_qty_register_entry_type VARCHAR2(20) := null; /*bug 7662347*/ /* Increased size from 2 to 20 for bug #12818250 by vkavulur */
3132:
3133: lv_destination_type_code jai_rcv_transactions.destination_type_code%TYPE ;--Added by nprashar for bug #10221291
3134:
3135: CURSOR cur_parent_trx( cp_prnt_trx_id NUMBER ) IS
3136: SELECT *
3137: FROM jai_rcv_transactions
3133: lv_destination_type_code jai_rcv_transactions.destination_type_code%TYPE ;--Added by nprashar for bug #10221291
3134:
3135: CURSOR cur_parent_trx( cp_prnt_trx_id NUMBER ) IS
3136: SELECT *
3137: FROM jai_rcv_transactions
3138: WHERE transaction_id = cp_prnt_trx_id; --Added by nprashar for bug#10221291
3139:
3140: parent_trx_rec cur_parent_trx%ROWTYPE ; --Added by nprashar for bug#10221291
3141:
3506: p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3507:
3508: IF p_simulate_flag = 'N' THEN
3509:
3510: jai_rcv_transactions_pkg.update_process_flags(
3511: p_transaction_id => p_transaction_id,
3512: p_process_flag => p_process_flag,
3513: p_process_message => p_process_message,
3514: p_cenvat_rg_flag => p_cenvat_rg_flag,
3538: ELSIF p_cenvat_rg_flag <> 'Y' THEN
3539: p_cenvat_rg_flag := 'E';
3540: p_cenvat_rg_message := p_common_err_mesg;
3541: ELSE
3542: -- dont update any of the fields of JAI_RCV_TRANSACTIONS table
3543: NULL;
3544: END IF;
3545: p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END');
3546:
3606: WHERE organization_id = cp_organization_id
3607: AND secondary_inventory_name = cp_subinventory;
3608:
3609: -- porting from Bug#3949109 (3927371)
3610: CURSOR c_dlry_subinventory(cp_shipment_line_id IN NUMBER, cp_receive_trx_id IN NUMBER, cp_transaction_type rcv_transactions.transaction_type%type) IS
3611: SELECT subinventory
3612: FROM rcv_transactions
3613: WHERE shipment_line_id = cp_shipment_line_id
3614: AND parent_transaction_id = cp_receive_trx_id
3608:
3609: -- porting from Bug#3949109 (3927371)
3610: CURSOR c_dlry_subinventory(cp_shipment_line_id IN NUMBER, cp_receive_trx_id IN NUMBER, cp_transaction_type rcv_transactions.transaction_type%type) IS
3611: SELECT subinventory
3612: FROM rcv_transactions
3613: WHERE shipment_line_id = cp_shipment_line_id
3614: AND parent_transaction_id = cp_receive_trx_id
3615: AND transaction_type = cp_transaction_type --'DELIVER'
3616: AND transaction_id > cp_receive_trx_id;
3615: AND transaction_type = cp_transaction_type --'DELIVER'
3616: AND transaction_id > cp_receive_trx_id;
3617:
3618: -- Start, Vijay Shankar for Bug#3940588
3619: ln_tax_transaction_id JAI_RCV_TRANSACTIONS.tax_transaction_id%TYPE;
3620: ln_non_po_vendor_cnt NUMBER;
3621: lv_third_party_flag VARCHAR2(1);
3622:
3623: CURSOR c_non_po_vendor_cnt(cp_shipment_header_id IN NUMBER, cp_shipment_line_id IN NUMBER) IS /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3637: || based on shipment_line_id of transaction_id passed to the cursor
3638: */
3639: CURSOR cur_dlry_subinv ( cp_transaction_id IN NUMBER ) IS
3640: SELECT subinventory
3641: FROM rcv_transactions
3642: WHERE transaction_type = 'DELIVER'
3643: AND shipment_line_id = (SELECT shipment_line_id
3644: FROM jai_rcv_transactions
3645: WHERE transaction_id = cp_transaction_id);
3640: SELECT subinventory
3641: FROM rcv_transactions
3642: WHERE transaction_type = 'DELIVER'
3643: AND shipment_line_id = (SELECT shipment_line_id
3644: FROM jai_rcv_transactions
3645: WHERE transaction_id = cp_transaction_id);
3646:
3647: r_base_trx c_base_trx%ROWTYPE;
3648: r_parent_base_trx c_base_trx%ROWTYPE;
3647: r_base_trx c_base_trx%ROWTYPE;
3648: r_parent_base_trx c_base_trx%ROWTYPE;
3649:
3650: -- Vijay Shankar for Bug#4038024
3651: lv_required_trx_type RCV_TRANSACTIONS.transaction_type%TYPE;
3652:
3653: r_ancestor_dtls c_base_trx%ROWTYPE;
3654: r_shp_line_dtls c_shp_line_dtls%ROWTYPE;
3655: r_shp_hdr_dtls c_shp_hdr_dtls%ROWTYPE;
3662:
3663:
3664: ln_location_id NUMBER(15);
3665: ln_organization_id NUMBER(15);
3666: lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3667: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3668: ln_ancestor_trxn_id NUMBER(15);
3669:
3670: ln_tax_amount NUMBER;
3663:
3664: ln_location_id NUMBER(15);
3665: ln_organization_id NUMBER(15);
3666: lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3667: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3668: ln_ancestor_trxn_id NUMBER(15);
3669:
3670: ln_tax_amount NUMBER;
3671: ln_cenvat_amount NUMBER;
3668: ln_ancestor_trxn_id NUMBER(15);
3669:
3670: ln_tax_amount NUMBER;
3671: ln_cenvat_amount NUMBER;
3672: lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3673: lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3674:
3675: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3676:
3669:
3670: ln_tax_amount NUMBER;
3671: ln_cenvat_amount NUMBER;
3672: lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3673: lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3674:
3675: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3676:
3677: /* Vijay Shankar for Bug#4250171 */
3675: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3676:
3677: /* Vijay Shankar for Bug#4250171 */
3678: r_trx c_trx%ROWTYPE;
3679: r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3680:
3681: /* Added by Ramananda for bug#4407165 */
3682: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3683: ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3679: r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3680:
3681: /* Added by Ramananda for bug#4407165 */
3682: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3683: ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3684:
3685: BEGIN
3686:
3687: IF lb_debug THEN
3749: -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3750: -- this is coded to support UNORDERED Transactions by localization in new receipts code. Shall chk the impact
3751: IF r_base_trx.transaction_type = 'MATCH' THEN
3752: lv_transaction_type := 'RECEIVE';
3753: UPDATE JAI_RCV_TRANSACTIONS
3754: SET transaction_type = lv_transaction_type
3755: WHERE transaction_id = p_transaction_id;
3756: END IF;
3757:
3839: IF nvl(r_trx.attribute_category, 'XXXX') NOT IN (OPM_RECEIPT, OPM_RETURNS) THEN
3840: /*
3841: || srjayara bug 5064235 -- forward porting for bug# 5054114
3842: || Added if condition .. end if to populate the subinventory from DELIVER line
3843: || in rcv_transactions
3844: */
3845: IF lv_subinventory IS NULL THEN
3846: OPEN cur_dlry_subinv ( p_transaction_id );
3847: FETCH cur_dlry_subinv INTO lv_subinventory ;
3959: FND_FILE.put_line(FND_FILE.log, '... RecNum:'||r_shp_hdr_dtls.receipt_num ||',p_cenvat_amount:'||ln_cenvat_amount );
3960: END IF;
3961:
3962: p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3963: jai_rcv_transactions_pkg.update_row(
3964: p_transaction_id => p_transaction_id,
3965: p_parent_transaction_type => r_parent_base_trx.transaction_type,
3966: p_receipt_num => r_shp_hdr_dtls.receipt_num,
3967: p_inventory_item_id => r_shp_line_dtls.item_id,
3980: p_boe_applied_flag => NULL,
3981: p_third_party_flag => lv_third_party_flag, -- Vijay Shankar for Bug#3940588
3982: p_tax_amount => ln_tax_amount,
3983: p_cenvat_amount => ln_cenvat_amount,
3984: p_currency_conversion_rate => r_base_trx.currency_conversion_rate, /*Bug 12543504 - Update Conversion Rate in JAI_RCV_TRANSACTIONS*/
3985: p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
3986: p_excise_invoice_date => r_exc_inv_no.excise_invoice_date,
3987: p_tax_transaction_id => ln_tax_transaction_id, -- Vijay Shankar for Bug#3940588
3988: p_assessable_value => NULL -- This needs to be populated during Tax Calculation itself
3997: p_codepath := jai_general_pkg.plot_codepath(19, p_codepath);
3998: --This update can't be merged with the above, as get_apportion_factor uses the column tax_transaction_id
3999: --which is updated only in the above update
4000:
4001: jai_rcv_transactions_pkg.update_row(
4002: p_transaction_id => p_transaction_id,
4003: p_tax_apportion_factor => ln_tax_apportion_factor
4004: );
4005: --End Added by Sanjikum for Bug#4495135
4040: /*---------------------------------------------
4041: Functionality of this Procedure :
4042: 0. If some Scenario has to be Supported/UnSupported by Localization, then this needs to be changed as everything is
4043: driven based on Flags set in this procedure
4044: 1. This Procedure is to set Process Flags or cenvat RG Flag in JAI_RCV_TRANSACTIONS.
4045: Possible Values :
4046: ===================
4047: X - Not Applicable (All values prefexing X means variations of Non Applicability with exact problem)
4048: P - Pending for Parent transaction to be Processed
4063: ---------------------------------------------*/
4064:
4065: CURSOR c_trx(cp_transaction_id IN NUMBER) IS
4066: SELECT *
4067: FROM JAI_RCV_TRANSACTIONS
4068: WHERE transaction_id = cp_transaction_id;
4069:
4070: CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
4071: SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
4066: SELECT *
4067: FROM JAI_RCV_TRANSACTIONS
4068: WHERE transaction_id = cp_transaction_id;
4069:
4070: CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
4071: SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
4072: claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type, receipt_num --Added receipt_num for bug #16197556
4073: FROM JAI_RCV_LINES
4074: WHERE shipment_line_id = cp_shipment_line_id;
4072: claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type, receipt_num --Added receipt_num for bug #16197556
4073: FROM JAI_RCV_LINES
4074: WHERE shipment_line_id = cp_shipment_line_id;
4075:
4076: CURSOR c_taxes(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
4077: SELECT count(1) total_cnt,
4078: sum( decode(upper(tax_type), 'EXCISE', 1,
4079: 'ADDL. EXCISE', 1,
4080: 'OTHER EXCISE', 1,
4090: FROM JAI_RCV_LINE_TAXES
4091: WHERE shipment_line_id = cp_shipment_line_id
4092: AND tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery); --('TDS', 'MODVAT RECOVERY')
4093:
4094: CURSOR c_excise_tax_count(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
4095: SELECT count(1)
4096: FROM JAI_RCV_LINE_TAXES
4097: WHERE shipment_line_id = cp_shipment_line_id
4098: -- AND tax_type NOT IN ('TDS','Modvat Recovery')
4120: ( select tax_type from jai_regime_tax_types_v /*modified this to use View as part of OPM changes */
4121: where regime_code = cp_regime_code
4122: );
4123:
4124: CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4125: cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
4126: ) IS
4127: SELECT count(1)
4128: FROM JAI_RCV_JOURNAL_ENTRIES
4121: where regime_code = cp_regime_code
4122: );
4123:
4124: CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4125: cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
4126: ) IS
4127: SELECT count(1)
4128: FROM JAI_RCV_JOURNAL_ENTRIES
4129: WHERE transaction_id = cp_parent_transaction_id
4129: WHERE transaction_id = cp_parent_transaction_id
4130: AND transaction_type = cp_parent_transaction_type;
4131:
4132: ld_parent_rg_entry_date DATE;
4133: CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4134: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4135: ) IS
4136: SELECT creation_date
4137: FROM (SELECT creation_date
4130: AND transaction_type = cp_parent_transaction_type;
4131:
4132: ld_parent_rg_entry_date DATE;
4133: CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4134: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4135: ) IS
4136: SELECT creation_date
4137: FROM (SELECT creation_date
4138: FROM JAI_CMN_RG_23AC_II_TRXS
4146: AND organization_id = cp_organization_id
4147: AND transaction_source_num = 19);
4148:
4149: CURSOR c_parent_rg23d_entry(
4150: cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4151: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4152: ) IS
4153: SELECT creation_date
4154: FROM JAI_CMN_RG_23D_TRXS
4147: AND transaction_source_num = 19);
4148:
4149: CURSOR c_parent_rg23d_entry(
4150: cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
4151: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
4152: ) IS
4153: SELECT creation_date
4154: FROM JAI_CMN_RG_23D_TRXS
4155: WHERE receipt_ref = cp_parent_transaction_id
4190: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
4191:
4192: lv_statement_id VARCHAR2(5);
4193:
4194: lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
4195: lv_include_cenvat_in_cost VARCHAR2(5);
4196:
4197: ln_excise_tax_count NUMBER; --File.Sql.35 Cbabu := 0;
4198: ln_account_count NUMBER; --File.Sql.35 Cbabu := 0;
4206: lv_vat_inv_gen_action VARCHAR2(50);
4207:
4208: lv_qty_upd_event VARCHAR2(30); --added by ssawant
4209:
4210: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE; --File.Sql.35 := '';
4211: lv_dest_code VARCHAR2(20); /*added for bug 8538155 (FP for bug 8466620)*/
4212: ln_check_non_recover_tax NUMBER; /*Added by nprashar for bug # 9767315 */
4213: lv_exp_del_message varchar2(200);
4214:
4237: SELECT Set_Of_Books_Id
4238: FROM Org_Organization_Definitions
4239: WHERE Organization_Id = cp_org_id;
4240:
4241: v_conv_factor rcv_transactions.currency_conversion_rate%TYPE;
4242: v_tax_sum JAI_CMN_BOE_MATCHINGS.amount%TYPE := 0;
4243: v_applied_amt JAI_CMN_BOE_MATCHINGS.amount%TYPE;
4244: v_sob_id org_organization_definitions.set_of_books_id%TYPE;
4245:
4948: FETCH c_excise_invoice_no INTO r_exc_inv_no;
4949: CLOSE c_excise_invoice_no;
4950:
4951: -- this is to update excise invoice no in case of Offline Claim or somehow excise invoice is not update in POPULATE_DETAILS
4952: jai_rcv_transactions_pkg.update_excise_invoice_no(
4953: p_transaction_id => p_transaction_id,
4954: p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
4955: p_excise_invoice_date => r_exc_inv_no.excise_invoice_date
4956: );
5059: IF p_simulate_flag = 'N' THEN
5060: lv_codepath := jai_general_pkg.plot_codepath(43, lv_codepath);
5061: lv_statement_id := '43';
5062: /* Call to update the Flag values as the validation is completed */
5063: jai_rcv_transactions_pkg.update_process_flags(
5064: p_transaction_id => p_transaction_id,
5065: p_process_flag => p_process_flag,
5066: p_process_message => p_process_message,
5067: p_cenvat_rg_flag => p_cenvat_rg_flag,
5084: lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
5085: END IF ;
5086: /*
5087:
5088: jai_rcv_transactions_pkg.update_attributes(
5089: p_transaction_id => p_transaction_id,
5090: p_attribute1 => lv_excise_inv_gen_action,
5091: p_attribute2 => lv_vat_inv_gen_action
5092: );
5092: );
5093: */
5094:
5095: --13364465
5096: jai_rcv_transactions_pkg.update_inv_stat_and_no(
5097: p_transaction_id => p_transaction_id,
5098: p_excise_inv_gen_status => lv_excise_inv_gen_action,
5099: p_vat_inv_gen_status => lv_vat_inv_gen_action
5100: );
5132: CURSOR c_shp_hdr(cp_transaction_id number) IS
5133: SELECT receipt_source_code
5134: from rcv_shipment_headers
5135: WHERE shipment_header_id = (select shipment_header_id
5136: from rcv_transactions
5137: where transaction_id = cp_transaction_id);
5138:
5139: CURSOR c_shp_line(cp_shipment_line_id number) IS
5140: SELECT from_organization_id, to_organization_id
5239: ) RETURN NUMBER IS
5240:
5241: ln_trx_id NUMBER;
5242:
5243: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
5244:
5245: BEGIN
5246:
5247: ln_trx_id := p_transaction_id;
5245: BEGIN
5246:
5247: ln_trx_id := p_transaction_id;
5248: FOR i IN (select transaction_id, transaction_type, parent_transaction_id
5249: from rcv_transactions
5250: where shipment_line_id = p_shipment_line_id
5251: and transaction_id <= p_transaction_id
5252: order by transaction_id desc)
5253: LOOP
5541: END get_equivalent_qty_of_receive;
5542:
5543:
5544: FUNCTION get_message( p_message_code IN VARCHAR2) RETURN VARCHAR2 IS
5545: lv_message JAI_RCV_TRANSACTIONS.process_message%type;
5546: BEGIN
5547:
5548: IF p_message_code = 'NO-ORG' THEN
5549: lv_message := 'Organization is not found ';