[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.
392: attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
393: , routing_header_id -- porting of Bug#3949109 (3927371)
394: , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
395: , po_line_location_id
396: FROM rcv_transactions
397: WHERE transaction_id = cp_transaction_id;
398:
399: r_trx c_trx%ROWTYPE;
400: r_base_trx c_base_trx%ROWTYPE;
406:
407: -- Bug 5581319. Added by Lakshmi Gopalsami
408: -- Increased the size of lv_localpath from 100 to 2000.
409: --
410: lv_localpath jai_rcv_transactions.codepath%TYPE; --VARCHAR2(2000); --File.Sql.35 Cbabu := '';
411: ln_dup_chk NUMBER; --File.Sql.35 Cbabu := 0;
412:
413: lv_process_status VARCHAR2(2);
414: lv_process_message VARCHAR2(1000);
770: jai_po_osp_hdrs jpoh
771: WHERE jpol.form_id = jpoh.form_id
772: AND (jpoh.po_header_id, jpol.po_line_id ) IN
773: ( SELECT po_header_id, po_line_id
774: FROM rcv_transactions
775: WHERE transaction_id = p_transaction_id
776: );
777: ln_line_id NUMBER ;
778: ln_ret_value VARCHAR2(3);
803: p_process_message OUT NOCOPY VARCHAR2
804: ) IS
805:
806: r_trx c_trx%ROWTYPE;
807: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
808: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
809: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
810: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
811: lv_common_err_mesg VARCHAR2(1000);
804: ) IS
805:
806: r_trx c_trx%ROWTYPE;
807: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
808: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
809: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
810: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
811: lv_common_err_mesg VARCHAR2(1000);
812:
805:
806: r_trx c_trx%ROWTYPE;
807: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
808: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
809: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
810: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
811: lv_common_err_mesg VARCHAR2(1000);
812:
813: lv_codepath VARCHAR2(1996);
806: r_trx c_trx%ROWTYPE;
807: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
808: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
809: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
810: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
811: lv_common_err_mesg VARCHAR2(1000);
812:
813: lv_codepath VARCHAR2(1996);
814: ln_processed_cnt NUMBER(10); --File.Sql.35 Cbabu := 0;
816:
817: r_tax jai_rcv_excise_processing_pkg.tax_breakup;
818: lv_breakup_type VARCHAR2(15);
819:
820: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
821: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
822: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
823:
824: CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
817: r_tax jai_rcv_excise_processing_pkg.tax_breakup;
818: lv_breakup_type VARCHAR2(15);
819:
820: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
821: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
822: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
823:
824: CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
825: select transaction_id, shipment_line_id, organization_type
818: lv_breakup_type VARCHAR2(15);
819:
820: lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
821: lv_ttype_deliver JAI_RCV_TRANSACTIONS.transaction_type%type ;
822: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
823:
824: CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
825: select transaction_id, shipment_line_id, organization_type
826: from JAI_RCV_TRANSACTIONS
822: lv_type_rtr JAI_RCV_TRANSACTIONS.transaction_type%type ;
823:
824: CURSOR c_trxs_for_unclaim(cp_shipment_line_id IN NUMBER, cp_tax_transaction_id IN NUMBER) IS
825: select transaction_id, shipment_line_id, organization_type
826: from JAI_RCV_TRANSACTIONS
827: where (transaction_type IN (lv_ttype_deliver, lv_type_rtr) --'DELIVER', 'RETURN TO RECEIVING')
828: or (transaction_type= lv_ttype_correct and parent_transaction_type = lv_ttype_deliver) --'CORRECT' , 'DELIVER'
829: )
830: and tax_transaction_id = cp_tax_transaction_id
852: lv_2nd_claim_flag VARCHAR2(1);
853: ln_qty_to_claim JAI_RCV_CENVAT_CLAIMS.quantity_for_2nd_claim%TYPE;
854: lv_process_special_reason VARCHAR2(50);
855: ln_process_special_amount NUMBER;
856: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
857: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
858: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
859:
860: BEGIN
853: ln_qty_to_claim JAI_RCV_CENVAT_CLAIMS.quantity_for_2nd_claim%TYPE;
854: lv_process_special_reason VARCHAR2(50);
855: ln_process_special_amount NUMBER;
856: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
857: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
858: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
859:
860: BEGIN
861:
854: lv_process_special_reason VARCHAR2(50);
855: ln_process_special_amount NUMBER;
856: -- lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
857: -- lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
858: -- lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
859:
860: BEGIN
861:
862: ln_processed_cnt := 0;
1108: --p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
1109: /*dbms_output.put_line('Unclaim#PRC_FLG#'||p_process_flag);*/
1110: END IF;
1111:
1112: UPDATE JAI_RCV_TRANSACTIONS
1113: SET CENVAT_RG_STATUS = 'X',
1114: cenvat_rg_message = 'Cenvat Unclaimed'
1115: WHERE CURRENT OF c_trxs_for_unclaim;
1116:
1120: END LOOP;
1121:
1122: lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
1123:
1124: UPDATE JAI_RCV_TRANSACTIONS
1125: SET CENVAT_RG_STATUS = 'X',
1126: cenvat_rg_message = 'Cenvat Unclaimed'
1127: WHERE transaction_id = temp_rec.transaction_id
1128: -- following is to take care of Pending DELIVER and RTR and related CORRECTS
1278:
1279: Cursor c_interface_exists(cp_group_id IN NUMBER)
1280: IS
1281: select 1
1282: from rcv_transactions_interface
1283: where group_id = cp_group_id
1284: and rownum=1 ;
1285:
1286: Cursor c_interface_error(cp_group_id IN NUMBER)
1285:
1286: Cursor c_interface_error(cp_group_id IN NUMBER)
1287: IS
1288: select 1
1289: from rcv_transactions_interface
1290: where
1291: group_id = cp_group_id and
1292: (transaction_status_code = 'ERROR' or processing_status_code = 'ERROR') ;
1293:
1293:
1294: ln_error NUMBER ;
1295: --ended, CSahoo for Bug 5344225
1296:
1297: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1294: ln_error NUMBER ;
1295: --ended, CSahoo for Bug 5344225
1296:
1297: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1302:
1295: --ended, CSahoo for Bug 5344225
1296:
1297: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1302:
1303: lv_common_err_mesg VARCHAR2(500);
1296:
1297: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1302:
1303: lv_common_err_mesg VARCHAR2(500);
1304: ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1297: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
1298: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
1299: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
1300: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
1301: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
1302:
1303: lv_common_err_mesg VARCHAR2(500);
1304: ln_processed_cnt NUMBER; --File.Sql.35 Cbabu := 0;
1305: ln_batch_id NUMBER(15);
1311: /*CURSOR c_trxns_to_populate_dtls IS
1312: SELECT rowid, transaction_id, shipment_line_id, process_status, cenvat_rg_status,
1313: transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg
1314: , attribute_category -- Vijay Shankar for Bug#4250171
1315: FROM JAI_RCV_TRANSACTIONS a
1316: WHERE (p_organization_id IS NULL OR organization_id = p_organization_id)
1317: AND a.receipt_num IS NULL
1318: AND (p_shipment_header_id IS NULL OR a.shipment_header_id = p_shipment_header_id)
1319: AND
1329: CURSOR c_get_transactions IS
1330: SELECT rowid, transaction_id, process_status, cenvat_rg_status, process_message, cenvat_rg_message,
1331: transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg,
1332: shipment_line_id -- Vijay Shankar for Bug#3940588
1333: FROM JAI_RCV_TRANSACTIONS a
1334: WHERE ( p_simulate_flag = 'Y'
1335: OR
1336: ( process_status IS NULL OR process_status IN ('N', 'E','P') OR cenvat_rg_status IN ('N', 'E','P')
1337: -- following condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation
1375: PROCEDURE ja_in_populate_details IS
1376:
1377: CURSOR c_lock_rcv_dtls IS
1378: SELECT rowid
1379: FROM jai_rcv_transactions
1380: WHERE transaction_id IN
1381: ( SELECT transaction_id
1382: FROM jai_rtp_populate_t)
1383: FOR UPDATE OF transaction_id ;
1407: parent_transaction_type,
1408: receipt_num,
1409: cenvat_claimed_ptg,
1410: attribute_category
1411: FROM jai_rcv_transactions a
1412: WHERE organization_id = p_organization_id
1413: AND a.receipt_num IS NULL
1414: AND p_called_from = 'JAINPORE');
1415:
1434: parent_transaction_type,
1435: receipt_num,
1436: cenvat_claimed_ptg,
1437: attribute_category
1438: FROM jai_rcv_transactions a
1439: WHERE organization_id = p_organization_id
1440: AND a.shipment_header_id = p_shipment_header_id
1441: AND a.receipt_num IS NULL
1442: AND EXISTS (SELECT 1
1454:
1455: PROCEDURE ja_in_get_transactions IS
1456: CURSOR c_lock_rcv_trans IS
1457: SELECT rowid
1458: FROM jai_rcv_transactions
1459: WHERE transaction_id IN
1460: ( SELECT transaction_id
1461: FROM jai_rtp_trans_t )
1462: FOR UPDATE OF transaction_id ;
1488: parent_transaction_type,
1489: receipt_num,
1490: cenvat_claimed_ptg,
1491: shipment_line_id
1492: FROM jai_rcv_transactions a
1493: WHERE organization_id = p_organization_id
1494: AND transaction_type = p_transaction_type
1495: AND parent_transaction_type = p_parent_trx_type
1496: AND receipt_num IS NOT NULL
1532: parent_transaction_type,
1533: receipt_num,
1534: cenvat_claimed_ptg,
1535: shipment_line_id
1536: FROM jai_rcv_transactions a
1537: WHERE organization_id = p_organization_id
1538: AND transaction_type = p_transaction_type
1539: AND receipt_num IS NOT NULL
1540: AND ( p_simulate_flag = 'Y'
1578: parent_transaction_type,
1579: receipt_num,
1580: cenvat_claimed_ptg,
1581: shipment_line_id
1582: FROM jai_rcv_transactions a
1583: WHERE organization_id = p_organization_id
1584: AND receipt_num IS NOT NULL
1585: AND ( p_simulate_flag = 'Y'
1586: OR
1621: parent_transaction_type,
1622: receipt_num,
1623: cenvat_claimed_ptg,
1624: shipment_line_id
1625: FROM jai_rcv_transactions a
1626: WHERE organization_id = p_organization_id
1627: AND shipment_header_id = p_shipment_header_id AND
1628: ( p_simulate_flag = 'Y'
1629: OR
1827: WHERE shipment_header_id = p_shipment_header_id
1828: AND tax_modified_flag IN ('Y', 'X')
1829: -- This condition will take not to update tax modified flag incase localization table is not populated
1830: -- eg.Incase of Unordered Receipt, Until MATCH happens we should not update the tax_modified_flag to 'Y'
1831: AND exists (select 1 from JAI_RCV_TRANSACTIONS
1832: where shipment_line_id = a.shipment_line_id);
1833:
1834: -- This Commit is a definitive Commit that has to happen
1835: COMMIT;
1888: errbuf := lv_process_message;
1889: retcode := jai_constants.request_error;
1890: RETURN;
1891: ELSIF p_commit_switch = 'Y' THEN
1892: -- first commit to save all the populated details of JAI_RCV_TRANSACTIONS
1893: COMMIT;
1894: END IF;
1895:
1896: /*~~~~~~~~~~~~~~~~~~~~~~~~~ Start of PROCESSING TRANSACTIONS ~~~~~~~~~~~~~~~~~~~~~*/
2020:
2021: IF p_simulate_flag = 'Y' THEN
2022: FND_FILE.put_line(FND_FILE.log, 'Codepath->'||lv_codepath);
2023: ELSE
2024: UPDATE JAI_RCV_TRANSACTIONS
2025: SET codepath = lv_codepath
2026: --WHERE rowid = trx.row_id
2027: --commented the above and added the below by sanjikum for Bug#4929410
2028: WHERE transaction_id = trx.transaction_id;
2126: nvl(b.receipt_excise_rate,0) receipt_excise_rate,
2127: nvl(b.rtv_excise_rate, nvl(b.receipt_excise_rate, 0)) rtv_excise_rate,
2128: decode( b.excise_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_excise_in_batch,
2129: decode( b.vat_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_vat_in_batch
2130: from JAI_RCV_TRANSACTIONS a, jai_rcv_rtv_batch_trxs b
2131: where a.transaction_id = b.transaction_id
2132: and (pn_min_transaction_id is null or a.transaction_id >= pn_min_transaction_id)
2133: and (pn_max_transaction_id is null or a.transaction_id <= pn_max_transaction_id)
2134: and
2144: where regime_code = cpv_regime_code;
2145:
2146: ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2147:
2148: lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2149: ld_excise_invoice_date DATE;
2150: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2151: ld_gen_excise_invoice_date DATE;
2152:
2146: ln_regime_id JAI_RGM_DEFINITIONS.regime_id%TYPE;
2147:
2148: lv_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2149: ld_excise_invoice_date DATE;
2150: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2151: ld_gen_excise_invoice_date DATE;
2152:
2153: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2154: ld_vat_invoice_date DATE;
2149: ld_excise_invoice_date DATE;
2150: lv_gen_excise_invoice_no JAI_RCV_TRANSACTIONS.excise_invoice_no%TYPE;
2151: ld_gen_excise_invoice_date DATE;
2152:
2153: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2154: ld_vat_invoice_date DATE;
2155: lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2156: ld_gen_vat_invoice_date DATE;
2157:
2151: ld_gen_excise_invoice_date DATE;
2152:
2153: lv_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2154: ld_vat_invoice_date DATE;
2155: lv_gen_vat_invoice_no JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE;
2156: ld_gen_vat_invoice_date DATE;
2157:
2158: lv_same_invoice_no_flag VARCHAR2(1);
2159:
2165: lv_errbuf VARCHAR2(1000);
2166: lv_statement_id VARCHAR2(4);
2167:
2168:
2169: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2166: lv_statement_id VARCHAR2(4);
2167:
2168:
2169: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174:
2167:
2168:
2169: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174:
2175: lv_excise_processing_reqd VARCHAR2(1);
2168:
2169: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174:
2175: lv_excise_processing_reqd VARCHAR2(1);
2176: lv_vat_processing_reqd VARCHAR2(1);
2169: lv_process_flag JAI_RCV_TRANSACTIONS.PROCESS_STATUS%TYPE;
2170: lv_process_message JAI_RCV_TRANSACTIONS.process_message%TYPE;
2171: lv_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2172: lv_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174:
2175: lv_excise_processing_reqd VARCHAR2(1);
2176: lv_vat_processing_reqd VARCHAR2(1);
2177: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2173: lv_common_err_mesg JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2174:
2175: lv_excise_processing_reqd VARCHAR2(1);
2176: lv_vat_processing_reqd VARCHAR2(1);
2177: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2178: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2179:
2180: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2181: r_trx_after_processing c_trx%ROWTYPE;
2174:
2175: lv_excise_processing_reqd VARCHAR2(1);
2176: lv_vat_processing_reqd VARCHAR2(1);
2177: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2178: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2179:
2180: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2181: r_trx_after_processing c_trx%ROWTYPE;
2182: lb_err_flag boolean;
2176: lv_vat_processing_reqd VARCHAR2(1);
2177: lv_ssi_cenvat_rg_flag JAI_RCV_TRANSACTIONS.CENVAT_RG_STATUS%TYPE;
2178: lv_ssi_cenvat_rg_message JAI_RCV_TRANSACTIONS.cenvat_rg_message%TYPE;
2179:
2180: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE;
2181: r_trx_after_processing c_trx%ROWTYPE;
2182: lb_err_flag boolean;
2183: ln_processed_cnt number;
2184:
2381:
2382: if rtv_rec.excise_invoice_action = INV_GEN_STATUS_GENERATE then
2383:
2384: lv_statement_id := '24';
2385: update JAI_RCV_TRANSACTIONS a
2386: set excise_invoice_no = lv_excise_invoice_no,
2387: excise_invoice_date = ld_excise_invoice_date,
2388: --attribute3 = pn_batch_num,
2389: last_update_date = sysdate,
2409:
2410: if rtv_rec.vat_batch_num = pn_batch_num and rtv_rec.vat_invoice_no is null then
2411: if rtv_rec.vat_invoice_action = INV_GEN_STATUS_GENERATE then
2412: lv_statement_id := '26';
2413: update JAI_RCV_TRANSACTIONS a
2414: set vat_invoice_no = lv_vat_invoice_no,
2415: vat_invoice_date = ld_vat_invoice_date,
2416: --attribute4 = pn_batch_num,
2417: last_update_date = sysdate,
2590: end if;
2591: end if;
2592:
2593: lv_statement_id := '31.1';
2594: jai_rcv_transactions_pkg.update_attributes(
2595: p_transaction_id => rtv_rec.transaction_id,
2596: p_attribute1 => lv_excise_inv_gen_action,
2597: p_attribute2 => lv_vat_inv_gen_action
2598: );
2618: end if;
2619: -- end if;
2620:
2621: lv_statement_id := '35';
2622: UPDATE JAI_RCV_TRANSACTIONS
2623: SET codepath = lv_codepath
2624: WHERE current of c_rtv_trxs;
2625:
2626: <
2674: attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
2675: , routing_header_id -- porting of Bug#3949109 (3927371)
2676: , attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
2677: , po_line_location_id
2678: FROM rcv_transactions
2679: WHERE transaction_id = cp_transaction_id;
2680:
2681: r_trx c_trx%ROWTYPE;
2682: r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
2680:
2681: r_trx c_trx%ROWTYPE;
2682: r_base_trx c_base_trx%ROWTYPE;/*bgowrava for forward porting Bug#5756676*/
2683:
2684: lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
2685: ln_cenvat_claimed_ptg NUMBER;
2686:
2687: lv_object_code VARCHAR2(10); --File.Sql.35 Cbabu := 'RCPT_TRXN:';
2688: lv_statement_id VARCHAR2(5);
2689:
2690: lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
2691: lv_temp VARCHAR2(100);
2692:
2693: lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
2694: lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
2695:
2696: lv_process_status VARCHAR2(2); --added by ssawant
2697: lv_qty_register_entry_type VARCHAR2(2); /*bug 7662347*/
2690: lv_execution_point VARCHAR2(30); --File.Sql.35 Cbabu := 'COMMON_CODE';
2691: lv_temp VARCHAR2(100);
2692:
2693: lv_process_vat_flag JAI_RCV_TRANSACTIONS.PROCESS_VAT_STATUS%TYPE;
2694: lv_process_vat_message JAI_RCV_TRANSACTIONS.process_vat_message%TYPE;
2695:
2696: lv_process_status VARCHAR2(2); --added by ssawant
2697: lv_qty_register_entry_type VARCHAR2(2); /*bug 7662347*/
2698:
3039: p_codepath := jai_general_pkg.plot_codepath(18, p_codepath);
3040:
3041: IF p_simulate_flag = 'N' THEN
3042:
3043: jai_rcv_transactions_pkg.update_process_flags(
3044: p_transaction_id => p_transaction_id,
3045: p_process_flag => p_process_flag,
3046: p_process_message => p_process_message,
3047: p_cenvat_rg_flag => p_cenvat_rg_flag,
3071: ELSIF p_cenvat_rg_flag <> 'Y' THEN
3072: p_cenvat_rg_flag := 'E';
3073: p_cenvat_rg_message := p_common_err_mesg;
3074: ELSE
3075: -- dont update any of the fields of JAI_RCV_TRANSACTIONS table
3076: NULL;
3077: END IF;
3078: p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END');
3079:
3139: WHERE organization_id = cp_organization_id
3140: AND secondary_inventory_name = cp_subinventory;
3141:
3142: -- porting from Bug#3949109 (3927371)
3143: 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
3144: SELECT subinventory
3145: FROM rcv_transactions
3146: WHERE shipment_line_id = cp_shipment_line_id
3147: AND parent_transaction_id = cp_receive_trx_id
3141:
3142: -- porting from Bug#3949109 (3927371)
3143: 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
3144: SELECT subinventory
3145: FROM rcv_transactions
3146: WHERE shipment_line_id = cp_shipment_line_id
3147: AND parent_transaction_id = cp_receive_trx_id
3148: AND transaction_type = cp_transaction_type --'DELIVER'
3149: AND transaction_id > cp_receive_trx_id;
3148: AND transaction_type = cp_transaction_type --'DELIVER'
3149: AND transaction_id > cp_receive_trx_id;
3150:
3151: -- Start, Vijay Shankar for Bug#3940588
3152: ln_tax_transaction_id JAI_RCV_TRANSACTIONS.tax_transaction_id%TYPE;
3153: ln_non_po_vendor_cnt NUMBER;
3154: lv_third_party_flag VARCHAR2(1);
3155:
3156: 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*/
3170: || based on shipment_line_id of transaction_id passed to the cursor
3171: */
3172: CURSOR cur_dlry_subinv ( cp_transaction_id IN NUMBER ) IS
3173: SELECT subinventory
3174: FROM rcv_transactions
3175: WHERE transaction_type = 'DELIVER'
3176: AND shipment_line_id = (SELECT shipment_line_id
3177: FROM jai_rcv_transactions
3178: WHERE transaction_id = cp_transaction_id);
3173: SELECT subinventory
3174: FROM rcv_transactions
3175: WHERE transaction_type = 'DELIVER'
3176: AND shipment_line_id = (SELECT shipment_line_id
3177: FROM jai_rcv_transactions
3178: WHERE transaction_id = cp_transaction_id);
3179:
3180: r_base_trx c_base_trx%ROWTYPE;
3181: r_parent_base_trx c_base_trx%ROWTYPE;
3180: r_base_trx c_base_trx%ROWTYPE;
3181: r_parent_base_trx c_base_trx%ROWTYPE;
3182:
3183: -- Vijay Shankar for Bug#4038024
3184: lv_required_trx_type RCV_TRANSACTIONS.transaction_type%TYPE;
3185:
3186: r_ancestor_dtls c_base_trx%ROWTYPE;
3187: r_shp_line_dtls c_shp_line_dtls%ROWTYPE;
3188: r_shp_hdr_dtls c_shp_hdr_dtls%ROWTYPE;
3195:
3196:
3197: ln_location_id NUMBER(15);
3198: ln_organization_id NUMBER(15);
3199: lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3200: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3201: ln_ancestor_trxn_id NUMBER(15);
3202:
3203: ln_tax_amount NUMBER;
3196:
3197: ln_location_id NUMBER(15);
3198: ln_organization_id NUMBER(15);
3199: lv_subinventory RCV_TRANSACTIONS.subinventory%TYPE;
3200: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
3201: ln_ancestor_trxn_id NUMBER(15);
3202:
3203: ln_tax_amount NUMBER;
3204: ln_cenvat_amount NUMBER;
3201: ln_ancestor_trxn_id NUMBER(15);
3202:
3203: ln_tax_amount NUMBER;
3204: ln_cenvat_amount NUMBER;
3205: lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3206: lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3207:
3208: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3209:
3202:
3203: ln_tax_amount NUMBER;
3204: ln_cenvat_amount NUMBER;
3205: lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%TYPE;
3206: lv_base_subinv_asset_flag JAI_RCV_TRANSACTIONS.BASE_ASSET_INVENTORY%TYPE;
3207:
3208: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3209:
3210: /* Vijay Shankar for Bug#4250171 */
3208: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3209:
3210: /* Vijay Shankar for Bug#4250171 */
3211: r_trx c_trx%ROWTYPE;
3212: r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3213:
3214: /* Added by Ramananda for bug#4407165 */
3215: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3216: ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3212: r_parent_trx c_trx%ROWTYPE; -- JAI_RCV_TRANSACTIONS record
3213:
3214: /* Added by Ramananda for bug#4407165 */
3215: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.populate_details';
3216: ln_tax_apportion_factor jai_rcv_transactions.tax_apportion_factor%TYPE; --Added by Sanjikum for Bug#4495135
3217:
3218: BEGIN
3219:
3220: IF lb_debug THEN
3282: -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3283: -- this is coded to support UNORDERED Transactions by localization in new receipts code. Shall chk the impact
3284: IF r_base_trx.transaction_type = 'MATCH' THEN
3285: lv_transaction_type := 'RECEIVE';
3286: UPDATE JAI_RCV_TRANSACTIONS
3287: SET transaction_type = lv_transaction_type
3288: WHERE transaction_id = p_transaction_id;
3289: END IF;
3290:
3371: IF nvl(r_trx.attribute_category, 'XXXX') NOT IN (OPM_RECEIPT, OPM_RETURNS) THEN
3372: /*
3373: || srjayara bug 5064235 -- forward porting for bug# 5054114
3374: || Added if condition .. end if to populate the subinventory from DELIVER line
3375: || in rcv_transactions
3376: */
3377: IF lv_subinventory IS NULL THEN
3378: OPEN cur_dlry_subinv ( p_transaction_id );
3379: FETCH cur_dlry_subinv INTO lv_subinventory ;
3472: FND_FILE.put_line(FND_FILE.log, '... RecNum:'||r_shp_hdr_dtls.receipt_num ||',p_cenvat_amount:'||ln_cenvat_amount );
3473: END IF;
3474:
3475: p_codepath := jai_general_pkg.plot_codepath(17, p_codepath);
3476: jai_rcv_transactions_pkg.update_row(
3477: p_transaction_id => p_transaction_id,
3478: p_parent_transaction_type => r_parent_base_trx.transaction_type,
3479: p_receipt_num => r_shp_hdr_dtls.receipt_num,
3480: p_inventory_item_id => r_shp_line_dtls.item_id,
3509: p_codepath := jai_general_pkg.plot_codepath(19, p_codepath);
3510: --This update can't be merged with the above, as get_apportion_factor uses the column tax_transaction_id
3511: --which is updated only in the above update
3512:
3513: jai_rcv_transactions_pkg.update_row(
3514: p_transaction_id => p_transaction_id,
3515: p_tax_apportion_factor => ln_tax_apportion_factor
3516: );
3517: --End Added by Sanjikum for Bug#4495135
3552: /*---------------------------------------------
3553: Functionality of this Procedure :
3554: 0. If some Scenario has to be Supported/UnSupported by Localization, then this needs to be changed as everything is
3555: driven based on Flags set in this procedure
3556: 1. This Procedure is to set Process Flags or cenvat RG Flag in JAI_RCV_TRANSACTIONS.
3557: Possible Values :
3558: ===================
3559: X - Not Applicable (All values prefexing X means variations of Non Applicability with exact problem)
3560: P - Pending for Parent transaction to be Processed
3575: ---------------------------------------------*/
3576:
3577: CURSOR c_trx(cp_transaction_id IN NUMBER) IS
3578: SELECT *
3579: FROM JAI_RCV_TRANSACTIONS
3580: WHERE transaction_id = cp_transaction_id;
3581:
3582: CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
3583: SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
3578: SELECT *
3579: FROM JAI_RCV_TRANSACTIONS
3580: WHERE transaction_id = cp_transaction_id;
3581:
3582: CURSOR c_receipt_line_dtls(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
3583: SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
3584: claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type
3585: FROM JAI_RCV_LINES
3586: WHERE shipment_line_id = cp_shipment_line_id;
3584: claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type
3585: FROM JAI_RCV_LINES
3586: WHERE shipment_line_id = cp_shipment_line_id;
3587:
3588: 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*/
3589: SELECT count(1) total_cnt,
3590: sum( decode(upper(tax_type), 'EXCISE', 1,
3591: 'ADDL. EXCISE', 1,
3592: 'OTHER EXCISE', 1,
3602: FROM JAI_RCV_LINE_TAXES
3603: WHERE shipment_line_id = cp_shipment_line_id
3604: AND tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery); --('TDS', 'MODVAT RECOVERY')
3605:
3606: CURSOR c_excise_tax_count(cp_shipment_line_id JAI_RCV_TRANSACTIONS.shipment_line_id%type) is
3607: SELECT count(1)
3608: FROM JAI_RCV_LINE_TAXES
3609: WHERE shipment_line_id = cp_shipment_line_id
3610: -- AND tax_type NOT IN ('TDS','Modvat Recovery')
3632: ( select tax_type from jai_regime_tax_types_v /*modified this to use View as part of OPM changes */
3633: where regime_code = cp_regime_code
3634: );
3635:
3636: CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3637: cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
3638: ) IS
3639: SELECT count(1)
3640: FROM JAI_RCV_JOURNAL_ENTRIES
3633: where regime_code = cp_regime_code
3634: );
3635:
3636: CURSOR c_acct_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3637: cp_parent_transaction_type JAI_RCV_TRANSACTIONS.parent_transaction_type%type
3638: ) IS
3639: SELECT count(1)
3640: FROM JAI_RCV_JOURNAL_ENTRIES
3641: WHERE transaction_id = cp_parent_transaction_id
3641: WHERE transaction_id = cp_parent_transaction_id
3642: AND transaction_type = cp_parent_transaction_type;
3643:
3644: ld_parent_rg_entry_date DATE;
3645: CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3646: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3647: ) IS
3648: SELECT creation_date
3649: FROM (SELECT creation_date
3642: AND transaction_type = cp_parent_transaction_type;
3643:
3644: ld_parent_rg_entry_date DATE;
3645: CURSOR c_rg_count(cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3646: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3647: ) IS
3648: SELECT creation_date
3649: FROM (SELECT creation_date
3650: FROM JAI_CMN_RG_23AC_II_TRXS
3658: AND organization_id = cp_organization_id
3659: AND transaction_source_num = 19);
3660:
3661: CURSOR c_parent_rg23d_entry(
3662: cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3663: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3664: ) IS
3665: SELECT creation_date
3666: FROM JAI_CMN_RG_23D_TRXS
3659: AND transaction_source_num = 19);
3660:
3661: CURSOR c_parent_rg23d_entry(
3662: cp_parent_transaction_id JAI_RCV_TRANSACTIONS.transaction_id%type,
3663: cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type
3664: ) IS
3665: SELECT creation_date
3666: FROM JAI_CMN_RG_23D_TRXS
3667: WHERE receipt_ref = cp_parent_transaction_id
3683: r_exc_inv_no c_excise_invoice_no%ROWTYPE;
3684:
3685: lv_statement_id VARCHAR2(5);
3686:
3687: lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
3688: lv_include_cenvat_in_cost VARCHAR2(5);
3689:
3690: ln_excise_tax_count NUMBER; --File.Sql.35 Cbabu := 0;
3691: ln_account_count NUMBER; --File.Sql.35 Cbabu := 0;
3699: lv_vat_inv_gen_action VARCHAR2(50);
3700:
3701: lv_qty_upd_event VARCHAR2(30); --added by ssawant
3702:
3703: lv_codepath JAI_RCV_TRANSACTIONS.codepath%TYPE; --File.Sql.35 := '';
3704:
3705: BEGIN
3706:
3707: ln_excise_tax_count := 0;
4281: FETCH c_excise_invoice_no INTO r_exc_inv_no;
4282: CLOSE c_excise_invoice_no;
4283:
4284: -- this is to update excise invoice no in case of Offline Claim or somehow excise invoice is not update in POPULATE_DETAILS
4285: jai_rcv_transactions_pkg.update_excise_invoice_no(
4286: p_transaction_id => p_transaction_id,
4287: p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
4288: p_excise_invoice_date => r_exc_inv_no.excise_invoice_date
4289: );
4387: IF p_simulate_flag = 'N' THEN
4388: lv_codepath := jai_general_pkg.plot_codepath(43, lv_codepath);
4389: lv_statement_id := '43';
4390: /* Call to update the Flag values as the validation is completed */
4391: jai_rcv_transactions_pkg.update_process_flags(
4392: p_transaction_id => p_transaction_id,
4393: p_process_flag => p_process_flag,
4394: p_process_message => p_process_message,
4395: p_cenvat_rg_flag => p_cenvat_rg_flag,
4411: IF ( Check_57F4_transaction( p_transaction_id ) = 'YES' ) THEN
4412: lv_excise_inv_gen_action := INV_GEN_STATUS_PENDING;
4413: END IF ;
4414:
4415: jai_rcv_transactions_pkg.update_attributes(
4416: p_transaction_id => p_transaction_id,
4417: p_attribute1 => lv_excise_inv_gen_action,
4418: p_attribute2 => lv_vat_inv_gen_action
4419: );
4451: CURSOR c_shp_hdr(cp_transaction_id number) IS
4452: SELECT receipt_source_code
4453: from rcv_shipment_headers
4454: WHERE shipment_header_id = (select shipment_header_id
4455: from rcv_transactions
4456: where transaction_id = cp_transaction_id);
4457:
4458: CURSOR c_shp_line(cp_shipment_line_id number) IS
4459: SELECT from_organization_id, to_organization_id
4558: ) RETURN NUMBER IS
4559:
4560: ln_trx_id NUMBER;
4561:
4562: lv_transaction_type RCV_TRANSACTIONS.transaction_type%TYPE;
4563:
4564: BEGIN
4565:
4566: ln_trx_id := p_transaction_id;
4564: BEGIN
4565:
4566: ln_trx_id := p_transaction_id;
4567: FOR i IN (select transaction_id, transaction_type, parent_transaction_id
4568: from rcv_transactions
4569: where shipment_line_id = p_shipment_line_id
4570: and transaction_id <= p_transaction_id
4571: order by transaction_id desc)
4572: LOOP
4860: END get_equivalent_qty_of_receive;
4861:
4862:
4863: FUNCTION get_message( p_message_code IN VARCHAR2) RETURN VARCHAR2 IS
4864: lv_message JAI_RCV_TRANSACTIONS.process_message%type;
4865: BEGIN
4866:
4867: IF p_message_code = 'NO-ORG' THEN
4868: lv_message := 'Organization is not found ';