[Home] [Help]
1: PACKAGE BODY jai_rcv_trx_processing_pkg AS
2: /* $Header: jai_rcv_trx_prc.plb 120.61.12020000.9 2013/02/19 01:50:44 vkaranam ship $ */
3: /*----------------------------------------------------------------------------------------------------------------------------
4: CHANGE HISTORY for FILENAME: jai_rcv_trx_processing_pkg.sql
5: S.No dd/mm/yyyy Author and Details
1: PACKAGE BODY jai_rcv_trx_processing_pkg AS
2: /* $Header: jai_rcv_trx_prc.plb 120.61.12020000.9 2013/02/19 01:50:44 vkaranam ship $ */
3: /*----------------------------------------------------------------------------------------------------------------------------
4: CHANGE HISTORY for FILENAME: jai_rcv_trx_processing_pkg.sql
5: S.No dd/mm/yyyy Author and Details
6: ------------------------------------------------------------------------------------------------------------------------------
7: 1 26/07/2004 Vijay Shankar for Bug# 3496408, Version:115.0
8: This Package is coded for Corrections Enhancement to invoke CENVAT and RG related insert APIs for PO Functionality.
618: -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
619: r_excise_invoice_no c_excise_invoice_no%ROWTYPE;
620:
621: /* Added by Ramananda for bug#4407165 */
622: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_preprocessor';
623:
624: -- added, Ramananda for bug # 4519719
625: CURSOR c_vat_exists(cp_shipment_line_id NUMBER )
626: IS
826: p_rtv_qty => null,
827: p_rtv_corr_qty => null,
828: p_excise_vendor_id => null,
829: p_excise_vendor_site_id => null,
830: p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
831: p_simulate_flag => p_simulate_flag,
832: p_process_status => p_process_status,
833: p_process_message => p_process_status
834: );
860: ln_cenvat_amount NUMBER;
861: ln_cenvat_claimed_ptg NUMBER;
862:
863: /* Added by Ramananda for bug#4407165 */
864: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.transaction_postprocessor';
865:
866: BEGIN
867:
868:
951: p_parent_transaction_type => r_trx.parent_transaction_type,
952: p_subinventory_type => r_trx.loc_subinventory_type,
953: p_transaction_quantity => r_trx.quantity,
954: p_transaction_uom_code => r_trx.uom_code,
955: p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
956: p_simulate_flag => p_simulate_flag,
957: p_process_status => p_process_status,
958: p_process_message => p_process_message
959: );
1292: );
1293:
1294: --start additions by vkaranam for bug#12817175
1295:
1296: FND_FILE.put_line(FND_FILE.log, 'Unclaim before jai_rcv_trx_processing_pkg.get_ancestor_id loop_trx.transaction_id '||loop_trx.transaction_id
1297: ||' r_trx.shipment_line_id '||r_trx.shipment_line_id);
1298: ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
1299: (
1300: loop_trx.transaction_id,
1294: --start additions by vkaranam for bug#12817175
1295:
1296: FND_FILE.put_line(FND_FILE.log, 'Unclaim before jai_rcv_trx_processing_pkg.get_ancestor_id loop_trx.transaction_id '||loop_trx.transaction_id
1297: ||' r_trx.shipment_line_id '||r_trx.shipment_line_id);
1298: ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
1299: (
1300: loop_trx.transaction_id,
1301: r_trx.shipment_line_id,
1302: 'DELIVER'
1301: r_trx.shipment_line_id,
1302: 'DELIVER'
1303: );
1304:
1305: FND_FILE.put_line(FND_FILE.log, 'Unclaim after jai_rcv_trx_processing_pkg.get_ancestor_id ln_dlry_trx_id '||ln_dlry_trx_id);
1306: open c_trx(ln_dlry_trx_id);
1307: fetch c_trx into r_dlry_trx;
1308: close c_trx;
1309: FND_FILE.put_line(FND_FILE.log, 'Unclaim r_dlry_trx.attribute1 '||r_dlry_trx.attribute1);
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
3684:
3685: BEGIN
3686:
4461: lv_statement_id := '6';
4462: if r_trx.organization_id is null then
4463: lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
4464: p_process_flag := 'O';
4465: p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
4466: goto end_of_process_flag_validation;
4467: end if;
4468:
4469: lv_statement_id := '7';
4484: lv_statement_id := '10';
4485: if r_taxes.total_cnt = 0 then
4486: lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
4487: p_process_flag := 'X';
4488: p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
4489: goto end_of_process_flag_validation;
4490: end if;
4491:
4492: lv_statement_id := '11';
4549: If nvl(ln_check_non_recover_tax,0) = 1 Then /*Added by nprashar for bug # 9767315*/
4550: if ln_account_count = 0 then
4551: lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
4552: p_process_flag := 'P';
4553: p_process_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-ACCT');
4554: goto end_of_process_flag_validation;
4555: end if;
4556: end if;
4557: end if;
4580: /* 1CENVAT_RG_FLAG. START of CENVAT_RG_FLAG BASIC VALIDATIONS */
4581: if r_trx.organization_id is null then
4582: lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
4583: p_cenvat_rg_flag := 'O';
4584: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-ORG');
4585:
4586: goto end_of_cenvat_flag_validation;
4587: end if;
4588:
4590: if r_trx.location_id = 0 then
4591: lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
4592: p_cenvat_rg_flag := 'O';
4593: p_process_flag :='E' ; /*Added by nprashar for bug # 9864327*/
4594: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
4595: goto end_of_cenvat_flag_validation;
4596: end if;
4597: /* 1CENVAT_RG_FLAG. END of CENVAT_RG_FLAG BASIC VALIDATIONS */
4598:
4610: lv_statement_id := '18';
4611: if r_taxes.total_cnt = 0 then
4612: lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
4613: p_cenvat_rg_flag := 'X';
4614: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-TAXES');
4615: goto end_of_cenvat_flag_validation;
4616: end if;
4617:
4618: lv_statement_id := '19';
4647: --Reverted back the changes done for bug #9019561 for fixing bug #14503581
4648: if r_trx.organization_type in ('M') AND ln_excise_tax_count = 0 then
4649: lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
4650: p_cenvat_rg_flag := 'X';
4651: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-EXCISE-TAXES');
4652: goto end_of_cenvat_flag_validation;
4653: end if;
4654:
4655: if r_trx.organization_type = 'T' AND lv_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING') then
4736: if r_trx.organization_type = 'T' then
4737: if r_trx.item_trading_flag <> 'Y' then
4738: lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath);
4739: p_cenvat_rg_flag := 'O';
4740: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-TRADING-NO');
4741: goto end_of_cenvat_flag_validation;
4742: elsif r_trx.item_excisable <> 'Y' then
4743: lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath);
4744: p_cenvat_rg_flag := 'O';
4750: lv_statement_id := '26';
4751: if r_trx.item_class in ('OTIN','OTEX') then
4752: lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath);
4753: p_cenvat_rg_flag := 'O';
4754: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-OTIN');
4755: goto end_of_cenvat_flag_validation;
4756: elsif r_trx.item_class NOT IN ('RMIN', 'RMEX', 'CGIN', 'CGEX', 'CCIN', 'CCEX', 'FGIN', 'FGEX') then
4757: lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
4758: p_cenvat_rg_flag := 'O';
4755: goto end_of_cenvat_flag_validation;
4756: elsif r_trx.item_class NOT IN ('RMIN', 'RMEX', 'CGIN', 'CGEX', 'CCIN', 'CCEX', 'FGIN', 'FGEX') then
4757: lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath);
4758: p_cenvat_rg_flag := 'O';
4759: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CLASS-NULL');
4760: goto end_of_cenvat_flag_validation;
4761: end if;
4762:
4763: lv_statement_id := '27';
4777: lv_statement_id := '28';
4778: if r_trx.organization_type ='M' and r_trx.item_cenvatable = 'N' then
4779: lv_codepath := jai_general_pkg.plot_codepath(29, lv_codepath);
4780: p_cenvat_rg_flag := 'O';
4781: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('ITEM-CENVATABLE-NO');
4782: goto end_of_cenvat_flag_validation;
4783: end if;
4784:
4785: lv_statement_id := '29';
4822: --Commented for bug #15907740
4823: /* if to_char(ld_parent_rg_entry_date, 'YYYYMM') <> to_char(SYSDATE, 'YYYYMM') THEN
4824: lv_codepath := jai_general_pkg.plot_codepath(33, lv_codepath);
4825: p_cenvat_rg_flag := 'XT';
4826: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4827: goto end_of_cenvat_flag_validation;
4828: end if; */
4829:
4830: else
4838: --Commented for bug #15907740
4839: /* if to_char(r_trx.transaction_date, 'YYYYMM') <> to_char(r_parent_trx.transaction_date, 'YYYYMM') THEN
4840: lv_codepath := jai_general_pkg.plot_codepath(35, lv_codepath);
4841: p_cenvat_rg_flag := 'XT';
4842: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('BASE-MONTH-DIFFERENT');
4843: goto end_of_cenvat_flag_validation;
4844: end if; */
4845: end if;
4846: end if;
4894: if r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type in ('RECEIVE', 'RETURN TO VENDOR') then
4895: if ld_parent_rg_entry_date IS NULL then
4896: lv_codepath := jai_general_pkg.plot_codepath(39, lv_codepath);
4897: p_cenvat_rg_flag := 'P';
4898: p_cenvat_rg_message := jai_rcv_trx_processing_pkg.get_message('NO-BASE-RG');
4899: goto end_of_cenvat_flag_validation;
4900: end if;
4901: end if;
4902:
4976:
4977: if r_trx.location_id = 0 then
4978: lv_codepath := jai_general_pkg.plot_codepath(42.1, lv_codepath);
4979: p_process_vat_flag := 'O';
4980: p_process_vat_message := jai_rcv_trx_processing_pkg.get_message('NO-LOC-ORG-SETUP');
4981: goto end_of_vat_validation;
4982: end if;
4983:
4984: IF lv_transaction_type NOT IN ( 'RECEIVE', 'RETURN TO VENDOR') THEN
5157:
5158: lb_process_iso_transaction BOOLEAN; --File.Sql.35 Cbabu := true;
5159:
5160: /* Added by Ramananda for bug#4407165 */
5161: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.process_iso_transaction';
5162:
5163: ln_excise_tax_cnt NUMBER;
5164: r_shp_line c_shp_line%ROWTYPE;
5165: r_shp_hdr c_shp_hdr%ROWTYPE;
5283: /*
5284: Transaction can have two Currencies 1. Functional(INR) 2. Transactional (Non INR Currency in case of foreign Trxn)
5285: If p_return_in_inr_curr = 'Y' then Functional tax amount is returned otherwise in transactional currency in returned
5286: Tax amount returned is to the tune of TRANSACTION Quantity.
5287: eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_tax_amount(14108, 10626, 50, 'N') amount from dual;
5288: */
5289:
5290: -- This cursor gives tax_amount in FOREIGN Currency
5291: CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER) IS
5330: ) RETURN NUMBER IS
5331:
5332: /*
5333: This Always Returns Total Cenvat amount in INR Currency to the tune of transaction quantity, uom
5334: eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_cenvat_amount(14108, 10626, 50) amount from dual;
5335: */
5336:
5337: -- This cursor gives tax_amount in FOREIGN Currency
5338: CURSOR c_tax_amount(cp_shipment_line_id IN NUMBER, cp_curr_conv_rate IN NUMBER, cp_organization_type IN VARCHAR2) IS
5394: then this function returns (18*1/5*12)=0.3
5395: */
5396:
5397: /* Added by Ramananda for bug#4407165 */
5398: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_apportion_factor';
5399:
5400: CURSOR c_ja_in_receipt_lines_qty(cp_shipment_line_id IN NUMBER) IS
5401: SELECT qty_received, transaction_id
5402: FROM JAI_RCV_LINES
5414: ln_apportion_factor NUMBER;
5415:
5416: BEGIN
5417:
5418: -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
5419: -- So, Check whether the required values are populated or not before proceding further
5420: OPEN c_trx(p_transaction_id);
5421: FETCH c_trx INTO r_trx;
5422: CLOSE c_trx;
5488: ln_uom_conv_rate NUMBER;
5489: ln_apportion_factor NUMBER;
5490:
5491: /* Added by Ramananda for bug#4407165 */
5492: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_equivalent_qty_of_receive';
5493:
5494: BEGIN
5495:
5496: -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
5492: lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_trx_processing_pkg.get_equivalent_qty_of_receive';
5493:
5494: BEGIN
5495:
5496: -- if this is called from jai_rcv_trx_processing_pkg.populate_details then most of the fields are NULL.
5497: -- So, Check whether the required values are populated or not before proceding further
5498: OPEN c_trx(p_transaction_id);
5499: FETCH c_trx INTO r_trx;
5500: CLOSE c_trx;
5631: RETURN nvl(lv_accrue_on_receipt_flag, 'N');
5632:
5633: END get_accrue_on_receipt;
5634:
5635: END jai_rcv_trx_processing_pkg;