[Home] [Help]
1: PACKAGE BODY jai_rcv_rgm_claims_pkg AS
2: /* $Header: jai_rcv_rgm_clm.plb 120.29.12020000.2 2013/01/30 03:23:50 vkaranam ship $ */
3:
4:
5: TABLE_RCV_TRANSACTIONS CONSTANT VARCHAR2(30) := 'RCV_TRANSACTIONS';
109:
110: Added the following as the object is not compiled because of R12 changes.
111: These were introudced as default values were removed from the procedure spec. and function spec.
112: 1. Procedue call update_rcv_lines is changed:
113: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
114: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
115:
116: 2. Procedure call generate_schedule is changed:
117: p_override => jai_constants.no
110: Added the following as the object is not compiled because of R12 changes.
111: These were introudced as default values were removed from the procedure spec. and function spec.
112: 1. Procedue call update_rcv_lines is changed:
113: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
114: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
115:
116: 2. Procedure call generate_schedule is changed:
117: p_override => jai_constants.no
118:
122:
123: 11. 02-Aug-2005 Ramananda for Bug#4519719. File Version 120.2
124: Issue : Processing should not take place if no VAT type of taxes
125: Fis : Added a condition to check if VAT type of taxes exist in the receipt
126: before the call to jai_rcv_rgm_claims_pkg.insert_rcv_lines. If no VAT type of taxes
127: exist, the return from the procedure.
128:
129: Dependency due to this bug:-
130: jai_rcv_trx_prc.plb (120.4)
254: Fix:
255: changes are done in update_rcv_lines procedure.
256:
257: 25. 17-Mar-2011 abezgam for Bug#11880460
258: Description: Following errors encountered by autobuild when compiling the package jai_rcv_rgm_claims_pkg.
259: 1. wrong number or types of arguments in call to insert_vat_repository_entry
260: 2. 'TEMP_REC'.'SHIPMENT_LINE_ID': invalid identifier
261: Fix:
262: 1. Corrected the parameters in the call to the procedure jai_cmn_rgm_recording_pkg.insert_vat_repository_entry.
295: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
296: 115.0 4248727 4245089 This is Part of VAT Enhancement, so dependent on VAT Enhancement
297: 115.2 4250171 4250171 There are changes done for OPM. So dependency is introduced
298: 115.3 4278511 4278511 There are changes done for OPM. So dependency is introduced
299: 115.4 4279050 4279050 jai_rcv_rgm_claims_s.sql 115.1 Sanjikum 07/04/2005
300: 115.4 4279050 4279050 ja_in_create_4279050.sql 115.0 Sanjikum 07/04/2005
301: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
302: ****************************************************************************************************************************************************************************************/
303: p_process_status := jai_constants.successful;
417: p_location_id := ln_location_id;
418: EXCEPTION
419: WHEN OTHERS THEN
420: p_process_status := jai_constants.unexpected_error;
421: p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.get_location Error:'||SQLERRM,1,250);
422: END get_location;
423:
424: PROCEDURE generate_schedule(
425: p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
510: cp_tax_id IN NUMBER,
511: cp_schedule_id IN NUMBER)
512: IS
513: SELECT COUNT(*) count
514: FROM jai_rcv_rgm_claims
515: WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
516: AND transaction_id = cp_transaction_id
517: AND tax_id = cp_tax_id
518: AND installment_no IN ( SELECT installment_no
535: p_process_status := jai_constants.successful;
536: p_process_message := NULL;
537:
538: IF p_override = 'Y' THEN
539: DELETE jai_rcv_rgm_claims
540: WHERE shipment_header_id = NVL(p_shipment_header_id, shipment_header_id)
541: AND shipment_line_id = NVL(p_shipment_line_id, shipment_line_id)
542: AND transaction_id = NVL(p_transaction_id, transaction_id)
543: AND tax_type = NVL(p_tax_type, tax_type)
613: WHERE schedule_id = ln_schedule_id;
614:
615: IF p_simulate_flag = 'Y' THEN
616: p_process_status := jai_constants.expected_error;
617: p_process_message := 'Duplicate Records in jai_rcv_rgm_claims';
618: --This message text is being compared for duplication check, so don't change this.
619: --Or search for the same text in this package and change it at all the places
620: RETURN;
621: ELSE
645: SET installment_amount = installment_amount*rec_txns.quantity_multiplier
646: WHERE schedule_id = ln_schedule_id;
647:
648: INSERT
649: INTO jai_rcv_rgm_claims
650: (
651: CLAIM_SCHEDULE_ID,
652: RCV_RGM_LINE_ID,
653: Shipment_header_id,
673: LAST_UPDATED_BY,
674: LAST_UPDATE_DATE,
675: LAST_UPDATE_LOGIN
676: )
677: SELECT jai_rcv_rgm_claims_s.NEXTVAL,
678: r_term.rcv_rgm_line_id,
679: rec_lines.shipment_header_id,
680: rec_lines.shipment_line_id,
681: jai_constants.vat_regime,
1117: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
1118: IS
1119: SELECT NVL(SUM(installment_amount),0) recoverable_amount,
1120: NVL(SUM(claimed_amount),0) recovered_amount
1121: FROM jai_rcv_rgm_claims
1122: WHERE shipment_header_id = cp_shipment_header_id
1123: AND shipment_line_id = cp_shipment_line_id;
1124:
1125: r_total_amount c_total_amount%ROWTYPE;
1313: RETURN;
1314: END IF;
1315:
1316: --this is just to check, if the transaction has already been processed by checking the records from
1317: --jai_rcv_rgm_claims for current transaction
1318: generate_schedule (
1319: p_shipment_header_id => r_trx.shipment_header_id,
1320: p_shipment_line_id => r_trx.shipment_line_id,
1321: p_transaction_id => r_trx.transaction_id,
1326: p_tax_id => NULL,
1327: p_override => NULL
1328: );
1329: IF lv_process_status = jai_constants.expected_error AND
1330: lv_process_message = 'Duplicate Records in jai_rcv_rgm_claims' THEN
1331: --This process message is being passed from the procedure generate_schedule
1332: --So shouldn't be changed OR should be changed at all the places in the in this package
1333: RETURN;
1334: END IF;
1389: p_correct_rtv_qty => ln_correct_rtv_qty,
1390: p_process_message => lv_process_message,
1391: p_process_status => lv_process_status,
1392: /* Added the following by Ramananda for bug#4530112 */
1393: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
1394: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
1395: );
1396:
1397: IF lv_process_status <> jai_constants.successful THEN
1390: p_process_message => lv_process_message,
1391: p_process_status => lv_process_status,
1392: /* Added the following by Ramananda for bug#4530112 */
1393: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
1394: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
1395: );
1396:
1397: IF lv_process_status <> jai_constants.successful THEN
1398: p_process_status := lv_process_status;
1563: p_rtv_qty => r_trx.quantity,
1564: --p_recoverable_amount => ln_total_cenvat_amount,
1565: p_process_message => lv_process_message,
1566: p_process_status => lv_process_status,
1567: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1568: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1569: );
1570:
1571: IF lv_process_status <> jai_constants.successful THEN
1564: --p_recoverable_amount => ln_total_cenvat_amount,
1565: p_process_message => lv_process_message,
1566: p_process_status => lv_process_status,
1567: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1568: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1569: );
1570:
1571: IF lv_process_status <> jai_constants.successful THEN
1572: p_process_status := lv_process_status;
1668: p_correct_receive_qty => r_base_trx.quantity,
1669: --p_recoverable_amount => ln_total_cenvat_amount,
1670: p_process_status => lv_process_status,
1671: p_process_message => lv_process_message,
1672: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1673: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1674: );
1675:
1676: IF lv_process_status <> jai_constants.successful THEN
1669: --p_recoverable_amount => ln_total_cenvat_amount,
1670: p_process_status => lv_process_status,
1671: p_process_message => lv_process_message,
1672: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1673: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1674: );
1675:
1676: IF lv_process_status <> jai_constants.successful THEN
1677: p_process_status := lv_process_status;
1780: p_shipment_line_id => r_base_trx.shipment_line_id,
1781: p_correct_rtv_qty => r_base_trx.quantity,
1782: p_process_message => lv_process_message,
1783: p_process_status => lv_process_status,
1784: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1785: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1786: );
1787:
1788: IF lv_process_status <> jai_constants.successful THEN
1781: p_correct_rtv_qty => r_base_trx.quantity,
1782: p_process_message => lv_process_message,
1783: p_process_status => lv_process_status,
1784: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1785: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1786: );
1787:
1788: IF lv_process_status <> jai_constants.successful THEN
1789: p_process_status := lv_process_status;
1880:
1881: CURSOR c_claim_schedule( cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1882: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
1883: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1884: cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1885: IS
1886: SELECT MIN(claim_schedule_id) claim_schedule_id
1887: FROM jai_rcv_rgm_claims
1888: WHERE shipment_header_id = cp_shipment_header_id
1883: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1884: cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1885: IS
1886: SELECT MIN(claim_schedule_id) claim_schedule_id
1887: FROM jai_rcv_rgm_claims
1888: WHERE shipment_header_id = cp_shipment_header_id
1889: AND shipment_line_id = cp_shipment_line_id
1890: AND tax_type = cp_tax_type
1891: AND installment_no = cp_installment_no
2140: pn_organization_id => rec_claims.organization_id,
2141: pn_location_id => rec_claims.location_id,
2142: pv_source => jai_constants.source_rcv,
2143: pv_source_trx_type => lv_source_trx_type,
2144: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
2145: pn_source_id => nvl(r_vat_invoice.tax_transaction_id, r_vat_invoice.transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay */
2146: pd_transaction_date => nvl(ld_accounting_date,trunc(sysdate)),--14061440
2147: pv_account_name => lv_account_name,
2148: pn_charge_account_id => ln_code_combination_id,
2215: p_simulate_flag => 'N',
2216: p_codepath => lv_code_path,
2217: p_process_message => lv_process_message,
2218: p_process_status => lv_process_status,
2219: p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2220: p_reference_id => r_claim_schedule.claim_schedule_id);
2221:
2222: IF lv_process_status <> jai_constants.successful THEN
2223: p_process_status := lv_process_status;
2268: p_simulate_flag => 'N',
2269: p_codepath => lv_code_path,
2270: p_process_message => lv_process_message,
2271: p_process_status => lv_process_status,
2272: p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2273: p_reference_id => r_claim_schedule.claim_schedule_id);
2274:
2275: IF lv_process_status <> jai_constants.successful THEN
2276: p_process_status := lv_process_status;
2282: IF gv_debug THEN
2283: fnd_file.put_line(fnd_file.log, 'After Passing the Accounting Entry');
2284: END IF;
2285:
2286: UPDATE jai_rcv_rgm_claims
2287: SET status = 'Y',
2288: claimed_amount = installment_amount,
2289: claimed_date = TRUNC(SYSDATE)
2290: WHERE shipment_header_id = rec_claims.shipment_header_id
2298: p_shipment_line_id => rec_claims.shipment_line_id,
2299: p_recovered_amount => rec_claims.installment_amount,
2300: p_process_message => lv_process_message,
2301: p_process_status => lv_process_status,
2302: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
2303: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2304: );
2305:
2306: IF lv_process_status <> jai_constants.successful THEN
2299: p_recovered_amount => rec_claims.installment_amount,
2300: p_process_message => lv_process_message,
2301: p_process_status => lv_process_status,
2302: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
2303: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2304: );
2305:
2306: IF lv_process_status <> jai_constants.successful THEN
2307: p_process_status := lv_process_status;
2315:
2316: EXCEPTION
2317: WHEN OTHERS THEN
2318: p_process_status := jai_constants.unexpected_error;
2319: p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.process_claim Error:'||SQLERRM,1,200);
2320: END process_claim;
2321:
2322: /*PROCEDURE process_no_claim(
2323: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2588: p_process_status := jai_constants.successful;
2589: p_process_message := NULL;
2590:
2591:
2592: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2593:
2594: OPEN c_regime(NULL, jai_constants.vat_regime);
2595: FETCH c_regime INTO r_regime;
2596: CLOSE c_regime;
2847: AND shipment_line_id = r_shipment_lines.shipment_line_id;
2848: --ABC--Update the other fields also, which are updated at the time of inserting the lines
2849:
2850: --Ideally, this shouldn't delete any rows.
2851: DELETE jai_rcv_rgm_claims
2852: WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2853: AND shipment_line_id = r_shipment_lines.shipment_line_id;
2854:
2855: -- Added by Jia for FP Bug#8805693, Begin
2875: -- Added by Jia for FP Bug#8805693, End
2876:
2877: END LOOP;
2878:
2879: lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2880: EXCEPTION
2881: WHEN OTHERS THEN
2882: p_process_status := jai_constants.unexpected_error;
2883: p_process_message := SUBSTR(SQLERRM,1,200);
2923:
2924: r_regime c_regime%ROWTYPE;
2925:
2926: BEGIN
2927: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'START');
2928:
2929:
2930: OPEN c_regime(p_regime_id, NULL);
2931: FETCH c_regime INTO r_regime;
2992: IF p_commit_switch = 'Y' THEN
2993: COMMIT;
2994: END IF;
2995:
2996: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'END');
2997: END process_batch;
2998:
2999: PROCEDURE do_rtv_accounting(
3000: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3012: b.location_id,
3013: b.receipt_num,
3014: a.tax_type,
3015: (NVL(SUM(a.installment_amount),0) - NVL(SUM(a.claimed_amount),0))*-1 installment_amount
3016: FROM jai_rcv_rgm_claims a,
3017: jai_rcv_rgm_lines b
3018: WHERE a.rcv_rgm_line_id = b.rcv_rgm_line_id
3019: AND a.transaction_id = cp_transaction_id
3020: GROUP BY b.organization_id,
3023: a.tax_type;
3024:
3025: CURSOR c_min_installment_no(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
3026: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3027: cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
3028: IS
3029: SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
3030: FROM jai_rcv_rgm_claims
3031: WHERE shipment_header_id = cp_shipment_header_id
3026: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
3027: cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
3028: IS
3029: SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
3030: FROM jai_rcv_rgm_claims
3031: WHERE shipment_header_id = cp_shipment_header_id
3032: AND shipment_line_id = cp_shipment_line_id
3033: AND transaction_type = cp_transaction_type --'RECEIVE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3034: AND claimed_date IS NOT NULL;
3039: cp_tax_type IN VARCHAR2,
3040: cp_installment_no IN NUMBER)
3041: IS
3042: SELECT (NVL(SUM(installment_amount),0) - NVL(SUM(claimed_amount),0))*-1 installment_amount
3043: FROM jai_rcv_rgm_claims
3044: WHERE shipment_header_id = cp_shipment_header_id
3045: AND shipment_line_id = cp_shipment_line_id
3046: AND transaction_id = cp_transaction_id
3047: AND installment_no > cp_installment_no
3066: cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
3067: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE)
3068: IS
3069: SELECT MIN(claim_schedule_id) claim_schedule_id
3070: FROM jai_rcv_rgm_claims
3071: WHERE shipment_header_id = cp_shipment_header_id
3072: AND shipment_line_id = cp_shipment_line_id
3073: AND tax_type = cp_tax_type
3074: AND transaction_id = cp_transaction_id
3378: pn_organization_id => rec_taxes.organization_id,
3379: pn_location_id => rec_taxes.location_id,
3380: pv_source => jai_constants.source_rcv,
3381: pv_source_trx_type => 'VAT CLAIM',
3382: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3383: pn_source_id => p_transaction_id, /* r_claim_schedule.claim_schedule_id, Vijay */
3384: pd_transaction_date => trunc(sysdate),
3385: pv_account_name => jai_constants.recovery,
3386: -- pn_charge_account_id => ln_rec_ccid,
3446: pn_organization_id => rec_taxes.organization_id,
3447: pn_location_id => rec_taxes.location_id,
3448: pv_source => jai_constants.source_rcv,
3449: pv_source_trx_type => 'VAT CLAIM for RTV',
3450: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3451: pn_source_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay*/
3452: pd_transaction_date => TRUNC(SYSDATE),
3453: pv_account_name => jai_constants.recovery,
3454: pn_charge_account_id => ln_rec_ccid,
3492: end if;
3493:
3494: END LOOP;
3495:
3496: UPDATE jai_rcv_rgm_claims
3497: SET claimed_amount = installment_amount,
3498: claimed_date = TRUNC(SYSDATE),
3499: status = 'Y'
3500: WHERE shipment_header_id = p_shipment_header_id
3833: p_process_status := jai_constants.unexpected_error;
3834: p_process_message := SUBSTR(SQLERRM,1,200);
3835: END do_rma_accounting;
3836:
3837: END jai_rcv_rgm_claims_pkg;