[Home] [Help]
1: PACKAGE BODY jai_rcv_rgm_claims_pkg AS
2: /* $Header: jai_rcv_rgm_clm.plb 120.15.12010000.2 2008/10/14 10:17:08 lgopalsa 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)
226: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
227: 115.0 4248727 4245089 This is Part of VAT Enhancement, so dependent on VAT Enhancement
228: 115.2 4250171 4250171 There are changes done for OPM. So dependency is introduced
229: 115.3 4278511 4278511 There are changes done for OPM. So dependency is introduced
230: 115.4 4279050 4279050 jai_rcv_rgm_claims_s.sql 115.1 Sanjikum 07/04/2005
231: 115.4 4279050 4279050 ja_in_create_4279050.sql 115.0 Sanjikum 07/04/2005
232: --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
233: ****************************************************************************************************************************************************************************************/
234: p_process_status := jai_constants.successful;
348: p_location_id := ln_location_id;
349: EXCEPTION
350: WHEN OTHERS THEN
351: p_process_status := jai_constants.unexpected_error;
352: p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.get_location Error:'||SQLERRM,1,250);
353: END get_location;
354:
355: PROCEDURE generate_schedule(
356: p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
443: cp_tax_id IN NUMBER,
444: cp_schedule_id IN NUMBER)
445: IS
446: SELECT COUNT(*) count
447: FROM jai_rcv_rgm_claims
448: WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
449: AND transaction_id = cp_transaction_id
450: AND tax_id = cp_tax_id
451: AND installment_no IN ( SELECT installment_no
468: p_process_status := jai_constants.successful;
469: p_process_message := NULL;
470:
471: IF p_override = 'Y' THEN
472: DELETE jai_rcv_rgm_claims
473: WHERE shipment_header_id = NVL(p_shipment_header_id, shipment_header_id)
474: AND shipment_line_id = NVL(p_shipment_line_id, shipment_line_id)
475: AND transaction_id = NVL(p_transaction_id, transaction_id)
476: AND tax_type = NVL(p_tax_type, tax_type)
543: WHERE schedule_id = ln_schedule_id;
544:
545: IF p_simulate_flag = 'Y' THEN
546: p_process_status := jai_constants.expected_error;
547: p_process_message := 'Duplicate Records in jai_rcv_rgm_claims';
548: --This message text is being compared for duplication check, so don't change this.
549: --Or search for the same text in this package and change it at all the places
550: RETURN;
551: ELSE
575: SET installment_amount = installment_amount*rec_txns.quantity_multiplier
576: WHERE schedule_id = ln_schedule_id;
577:
578: INSERT
579: INTO jai_rcv_rgm_claims
580: (
581: CLAIM_SCHEDULE_ID,
582: RCV_RGM_LINE_ID,
583: Shipment_header_id,
603: LAST_UPDATED_BY,
604: LAST_UPDATE_DATE,
605: LAST_UPDATE_LOGIN
606: )
607: SELECT jai_rcv_rgm_claims_s.NEXTVAL,
608: r_term.rcv_rgm_line_id,
609: rec_lines.shipment_header_id,
610: rec_lines.shipment_line_id,
611: jai_constants.vat_regime,
1047: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE)
1048: IS
1049: SELECT NVL(SUM(installment_amount),0) recoverable_amount,
1050: NVL(SUM(claimed_amount),0) recovered_amount
1051: FROM jai_rcv_rgm_claims
1052: WHERE shipment_header_id = cp_shipment_header_id
1053: AND shipment_line_id = cp_shipment_line_id;
1054:
1055: r_total_amount c_total_amount%ROWTYPE;
1242: RETURN;
1243: END IF;
1244:
1245: --this is just to check, if the transaction has already been processed by checking the records from
1246: --jai_rcv_rgm_claims for current transaction
1247: generate_schedule (
1248: p_shipment_header_id => r_trx.shipment_header_id,
1249: p_shipment_line_id => r_trx.shipment_line_id,
1250: p_transaction_id => r_trx.transaction_id,
1255: p_tax_id => NULL,
1256: p_override => NULL
1257: );
1258: IF lv_process_status = jai_constants.expected_error AND
1259: lv_process_message = 'Duplicate Records in jai_rcv_rgm_claims' THEN
1260: --This process message is being passed from the procedure generate_schedule
1261: --So shouldn't be changed OR should be changed at all the places in the in this package
1262: RETURN;
1263: END IF;
1318: p_correct_rtv_qty => ln_correct_rtv_qty,
1319: p_process_message => lv_process_message,
1320: p_process_status => lv_process_status,
1321: /* Added the following by Ramananda for bug#4530112 */
1322: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
1323: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
1324: );
1325:
1326: IF lv_process_status <> jai_constants.successful THEN
1319: p_process_message => lv_process_message,
1320: p_process_status => lv_process_status,
1321: /* Added the following by Ramananda for bug#4530112 */
1322: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
1323: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
1324: );
1325:
1326: IF lv_process_status <> jai_constants.successful THEN
1327: p_process_status := lv_process_status;
1492: p_rtv_qty => r_trx.quantity,
1493: --p_recoverable_amount => ln_total_cenvat_amount,
1494: p_process_message => lv_process_message,
1495: p_process_status => lv_process_status,
1496: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1497: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1498: );
1499:
1500: IF lv_process_status <> jai_constants.successful THEN
1493: --p_recoverable_amount => ln_total_cenvat_amount,
1494: p_process_message => lv_process_message,
1495: p_process_status => lv_process_status,
1496: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1497: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1498: );
1499:
1500: IF lv_process_status <> jai_constants.successful THEN
1501: p_process_status := lv_process_status;
1597: p_correct_receive_qty => r_base_trx.quantity,
1598: --p_recoverable_amount => ln_total_cenvat_amount,
1599: p_process_status => lv_process_status,
1600: p_process_message => lv_process_message,
1601: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1602: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1603: );
1604:
1605: IF lv_process_status <> jai_constants.successful THEN
1598: --p_recoverable_amount => ln_total_cenvat_amount,
1599: p_process_status => lv_process_status,
1600: p_process_message => lv_process_message,
1601: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1602: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1603: );
1604:
1605: IF lv_process_status <> jai_constants.successful THEN
1606: p_process_status := lv_process_status;
1709: p_shipment_line_id => r_base_trx.shipment_line_id,
1710: p_correct_rtv_qty => r_base_trx.quantity,
1711: p_process_message => lv_process_message,
1712: p_process_status => lv_process_status,
1713: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1714: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1715: );
1716:
1717: IF lv_process_status <> jai_constants.successful THEN
1710: p_correct_rtv_qty => r_base_trx.quantity,
1711: p_process_message => lv_process_message,
1712: p_process_status => lv_process_status,
1713: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
1714: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
1715: );
1716:
1717: IF lv_process_status <> jai_constants.successful THEN
1718: p_process_status := lv_process_status;
1809:
1810: CURSOR c_claim_schedule( cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
1811: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
1812: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1813: cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1814: IS
1815: SELECT MIN(claim_schedule_id) claim_schedule_id
1816: FROM jai_rcv_rgm_claims
1817: WHERE shipment_header_id = cp_shipment_header_id
1812: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE,
1813: cp_installment_no IN jai_rcv_rgm_claims.installment_no%TYPE)
1814: IS
1815: SELECT MIN(claim_schedule_id) claim_schedule_id
1816: FROM jai_rcv_rgm_claims
1817: WHERE shipment_header_id = cp_shipment_header_id
1818: AND shipment_line_id = cp_shipment_line_id
1819: AND tax_type = cp_tax_type
1820: AND installment_no = cp_installment_no
2067: pn_organization_id => rec_claims.organization_id,
2068: pn_location_id => rec_claims.location_id,
2069: pv_source => jai_constants.source_rcv,
2070: pv_source_trx_type => lv_source_trx_type,
2071: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
2072: pn_source_id => nvl(r_vat_invoice.tax_transaction_id, r_vat_invoice.transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay */
2073: pd_transaction_date => trunc(sysdate),
2074: pv_account_name => lv_account_name,
2075: pn_charge_account_id => ln_code_combination_id,
2135: p_simulate_flag => 'N',
2136: p_codepath => lv_code_path,
2137: p_process_message => lv_process_message,
2138: p_process_status => lv_process_status,
2139: p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2140: p_reference_id => r_claim_schedule.claim_schedule_id);
2141:
2142: IF lv_process_status <> jai_constants.successful THEN
2143: p_process_status := lv_process_status;
2188: p_simulate_flag => 'N',
2189: p_codepath => lv_code_path,
2190: p_process_message => lv_process_message,
2191: p_process_status => lv_process_status,
2192: p_reference_name => 'JAI_RCV_RGM_CLAIMS',
2193: p_reference_id => r_claim_schedule.claim_schedule_id);
2194:
2195: IF lv_process_status <> jai_constants.successful THEN
2196: p_process_status := lv_process_status;
2202: IF gv_debug THEN
2203: fnd_file.put_line(fnd_file.log, 'After Passing the Accounting Entry');
2204: END IF;
2205:
2206: UPDATE jai_rcv_rgm_claims
2207: SET status = 'Y',
2208: claimed_amount = installment_amount,
2209: claimed_date = TRUNC(SYSDATE)
2210: WHERE shipment_header_id = rec_claims.shipment_header_id
2218: p_shipment_line_id => rec_claims.shipment_line_id,
2219: p_recovered_amount => rec_claims.installment_amount,
2220: p_process_message => lv_process_message,
2221: p_process_status => lv_process_status,
2222: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
2223: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2224: );
2225:
2226: IF lv_process_status <> jai_constants.successful THEN
2219: p_recovered_amount => rec_claims.installment_amount,
2220: p_process_message => lv_process_message,
2221: p_process_status => lv_process_status,
2222: p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
2223: p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2224: );
2225:
2226: IF lv_process_status <> jai_constants.successful THEN
2227: p_process_status := lv_process_status;
2235:
2236: EXCEPTION
2237: WHEN OTHERS THEN
2238: p_process_status := jai_constants.unexpected_error;
2239: p_process_message := SUBSTR('jai_rcv_rgm_claims_pkg.process_claim Error:'||SQLERRM,1,200);
2240: END process_claim;
2241:
2242: /*PROCEDURE process_no_claim(
2243: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
2448: p_process_status := jai_constants.successful;
2449: p_process_message := NULL;
2450:
2451:
2452: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2453:
2454: OPEN c_regime(NULL, jai_constants.vat_regime);
2455: FETCH c_regime INTO r_regime;
2456: CLOSE c_regime;
2657: AND shipment_line_id = r_shipment_lines.shipment_line_id;
2658: --ABC--Update the other fields also, which are updated at the time of inserting the lines
2659:
2660: --Ideally, this shouldn't delete any rows.
2661: DELETE jai_rcv_rgm_claims
2662: WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2663: AND shipment_line_id = r_shipment_lines.shipment_line_id;
2664: END LOOP;
2665:
2662: WHERE shipment_header_id = r_shipment_lines.shipment_header_id
2663: AND shipment_line_id = r_shipment_lines.shipment_line_id;
2664: END LOOP;
2665:
2666: lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_no_claim', 'START');
2667: EXCEPTION
2668: WHEN OTHERS THEN
2669: p_process_status := jai_constants.unexpected_error;
2670: p_process_message := SUBSTR(SQLERRM,1,200);
2710:
2711: r_regime c_regime%ROWTYPE;
2712:
2713: BEGIN
2714: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'START');
2715:
2716:
2717: OPEN c_regime(p_regime_id, NULL);
2718: FETCH c_regime INTO r_regime;
2779: IF p_commit_switch = 'Y' THEN
2780: COMMIT;
2781: END IF;
2782:
2783: lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_rcv_rgm_claims_pkg.process_batch', 'END');
2784: END process_batch;
2785:
2786: PROCEDURE do_rtv_accounting(
2787: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2799: b.location_id,
2800: b.receipt_num,
2801: a.tax_type,
2802: (NVL(SUM(a.installment_amount),0) - NVL(SUM(a.claimed_amount),0))*-1 installment_amount
2803: FROM jai_rcv_rgm_claims a,
2804: jai_rcv_rgm_lines b
2805: WHERE a.rcv_rgm_line_id = b.rcv_rgm_line_id
2806: AND a.transaction_id = cp_transaction_id
2807: GROUP BY b.organization_id,
2810: a.tax_type;
2811:
2812: CURSOR c_min_installment_no(cp_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
2813: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2814: cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
2815: IS
2816: SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
2817: FROM jai_rcv_rgm_claims
2818: WHERE shipment_header_id = cp_shipment_header_id
2813: cp_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
2814: cp_transaction_type jai_rcv_rgm_claims.transaction_type%type)
2815: IS
2816: SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
2817: FROM jai_rcv_rgm_claims
2818: WHERE shipment_header_id = cp_shipment_header_id
2819: AND shipment_line_id = cp_shipment_line_id
2820: AND transaction_type = cp_transaction_type --'RECEIVE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2821: AND claimed_date IS NOT NULL;
2826: cp_tax_type IN VARCHAR2,
2827: cp_installment_no IN NUMBER)
2828: IS
2829: SELECT (NVL(SUM(installment_amount),0) - NVL(SUM(claimed_amount),0))*-1 installment_amount
2830: FROM jai_rcv_rgm_claims
2831: WHERE shipment_header_id = cp_shipment_header_id
2832: AND shipment_line_id = cp_shipment_line_id
2833: AND transaction_id = cp_transaction_id
2834: AND installment_no > cp_installment_no
2853: cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
2854: cp_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE)
2855: IS
2856: SELECT MIN(claim_schedule_id) claim_schedule_id
2857: FROM jai_rcv_rgm_claims
2858: WHERE shipment_header_id = cp_shipment_header_id
2859: AND shipment_line_id = cp_shipment_line_id
2860: AND tax_type = cp_tax_type
2861: AND transaction_id = cp_transaction_id
3144: pn_organization_id => rec_taxes.organization_id,
3145: pn_location_id => rec_taxes.location_id,
3146: pv_source => jai_constants.source_rcv,
3147: pv_source_trx_type => 'VAT CLAIM',
3148: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3149: pn_source_id => p_transaction_id, /* r_claim_schedule.claim_schedule_id, Vijay */
3150: pd_transaction_date => trunc(sysdate),
3151: pv_account_name => jai_constants.recovery,
3152: pn_charge_account_id => ln_rec_ccid,
3204: pn_organization_id => rec_taxes.organization_id,
3205: pn_location_id => rec_taxes.location_id,
3206: pv_source => jai_constants.source_rcv,
3207: pv_source_trx_type => 'VAT CLAIM for RTV',
3208: pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
3209: pn_source_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay*/
3210: pd_transaction_date => TRUNC(SYSDATE),
3211: pv_account_name => jai_constants.recovery,
3212: pn_charge_account_id => ln_rec_ccid,
3245: end if;
3246:
3247: END LOOP;
3248:
3249: UPDATE jai_rcv_rgm_claims
3250: SET claimed_amount = installment_amount,
3251: claimed_date = TRUNC(SYSDATE),
3252: status = 'Y'
3253: WHERE shipment_header_id = p_shipment_header_id
3490: p_process_status := jai_constants.unexpected_error;
3491: p_process_message := SUBSTR(SQLERRM,1,200);
3492: END do_rma_accounting;
3493:
3494: END jai_rcv_rgm_claims_pkg;