[Home] [Help]
1: PACKAGE BODY jai_po_tax_pkg AS
2: /* $Header: jai_po_tax.plb 120.50.12020000.7 2013/01/16 01:08:32 wenzhou ship $ */
3:
4: /* --------------------------------------------------------------------------------------
5: Filename:
412: FROM JAI_PO_TAXES
413: WHERE Line_Location_Id = linelocid -- For Blanket Rel Line Loc Id is passed in place of header id.
414: AND Tax_Type <> jai_constants.tax_type_tds ; --'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
415:
416: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.calculate_tax';
417: BEGIN
418:
419: /*------------------------------------------------------------------------------------------
420: FILENAME: ja_in_cal_tax_p.sql
2808:
2809: IF lv_process_flag IN ('EE', 'UE')
2810: THEN
2811: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2812: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALCULATE_TAX.Err:'||lv_process_message);
2813: app_exception.raise_exception;
2814: END IF;
2815: END IF;
2816: --=====================================================
2834:
2835: IF lv_process_flag IN ('EE', 'UE')
2836: THEN
2837: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2838: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALCULATE_TAX.Err:'||lv_process_message);
2839: app_exception.raise_exception;
2840: END IF;
2841: END IF;
2842: --=====================================================
2927: spid V$PROCESS.SPID%TYPE;
2928: v_name1 V$DATABASE.NAME%TYPE;
2929:
2930:
2931: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.batch_quot_taxes_copy';
2932: BEGIN
2933:
2934: /*------------------------------------------------------------------------------------------------------------------------
2935: S.No DATE Author AND Details
3023: CLOSE c_line_location_qty;
3024:
3025: -- Call the procedure which defaults the taxes from Quotation to PO and inserts shipment lines into
3026: -- JA tables
3027: jai_po_tax_pkg.copy_quot_taxes(
3028: errbuf => v_errbuf,
3029: retcode => v_retcode,
3030: p_line_loc_id => v_line_location_id,
3031: p_po_hdr_id => c_rec.po_header_id,
3044: );
3045:
3046: -- check whether called procedure returned any error
3047: IF ( v_errbuf IS NOT NULL) THEN
3048: v_error_mesg := 'Error from called unit jai_po_tax_pkg.copy_quot_taxes -> '||v_errbuf;
3049: RAISE error_from_called_unit;
3050: END IF;
3051:
3052: DELETE FROM JAI_PO_QUOT_LINES_T
3058: EXCEPTION
3059:
3060: WHEN OTHERS THEN
3061: IF v_error_mesg IS NULL THEN
3062: -- the exception condition is not because of returned error from jai_po_tax_pkg.copy_quot_taxes
3063: v_error_mesg := 'Error in main for loop. SQLERRM -> '|| SQLERRM;
3064: END IF;
3065:
3066: ROLLBACK;
3204: CURSOR Fetch_UOMCode_Cur IS SELECT Uom_Code
3205: FROM Mtl_Units_Of_Measure
3206: WHERE Unit_Of_Measure = v_uom;
3207:
3208: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_reqn_taxes';
3209: BEGIN
3210:
3211: /*--------------------------------------------------------------------------------------------------------------------------
3212: CHANGE HISTORY for FILENAME - jai_po_tax_pkg.copy_reqn_taxes.sql
3208: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_reqn_taxes';
3209: BEGIN
3210:
3211: /*--------------------------------------------------------------------------------------------------------------------------
3212: CHANGE HISTORY for FILENAME - jai_po_tax_pkg.copy_reqn_taxes.sql
3213: S.No Date Author and Details
3214: -------------------------------------------------------------------------------------------------------------------------
3215: 1 22/11/2001 sriram bug# Version#115.1
3216:
3405: ELSE
3406: flag := 'I';
3407: END IF;
3408:
3409: jai_po_tax_pkg.Ja_In_Po_Case2( v_Type_Lookup_Code,
3410: v_Quot_Class_Code,
3411: v_Vendor_Id,
3412: v_Vendor_Site_Id,
3413: --p_Currency_Code,
3433: , pn_gst_assessable_value => ln_gst_assessable_value ); --Added by Jia for GST Bug#10043656 on 2010/09/10
3434: */
3435: );
3436:
3437: /** jai_po_tax_pkg.calculate_tax( 'STANDARDPO', v_po_hdr_id , v_Po_Line_Id1, v_line_location_id,
3438: v_qty, v_price*v_qty, v_uom, v_assessable_value,
3439: NVL( v_assessable_value, v_price*v_qty ), NULL, nvl(v_conv_rate,1));
3440:
3441:
3736: FROM po_lines_all plines, mtl_units_of_measure units
3737: WHERE plines.po_line_id = p_line_id
3738: AND units.unit_of_measure = plines.unit_meas_lookup_code;
3739:
3740: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.calc_tax';
3741:
3742: /* Bug 5243532. Added by Lakshmi Gopalsami
3743: * Defined variable for implementing caching logic.
3744: */
3947: CHANGE HISTORY for FILENAME - ja_in_po_calc_tax_p.sql
3948: S.No Date Author and Details
3949: -------------------------------------------------
3950: 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
3951: This procedure is created with this bug by getting the source from jai_po_tax_pkg.calculate_tax procedure with some modifications
3952: commented with bug number. Basically two new parameters were added.
3953: Initially all the plsql tables are filled with PO Currency data and finally when updating these into the database
3954: they are converted back to the tax currency if there is any change in the currency.
3955:
6428:
6429: IF lv_process_flag IN ('EE', 'UE')
6430: THEN
6431: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
6432: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALC_TAX.Err:'||lv_process_message);
6433: app_exception.raise_exception;
6434: END IF;
6435: END IF;
6436: --=====================================================
6671: OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
6672:
6673: -- Cursor variable for Fetch_Po_Ln_Loc_Cur
6674: fetch_ja_in_po_ln_loc_rec FETCH_JA_IN_PO_LN_LOC_CUR%ROWTYPE;
6675: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_source_taxes';
6676: BEGIN
6677:
6678: /*-----------------------------------------------------------------------------------------------------------------------
6679: CHANGE HISTORY: FILENAME: ja_in_po_copydoc_dflt_p.sql
6881: ln_gst_assessable_value NUMBER; --Added by Jia for GST Bug#10043656 on 2010/09/10
6882: */
6883:
6884: v_tax_category_id_holder JAI_PO_LINE_LOCATIONS.tax_category_id%TYPE; -- cbabu for EnhancementBug# 2427465
6885: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_quot_taxes';
6886: ------------------------------>
6887:
6888: CURSOR tax_cur IS
6889: SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
7104: SET tax_category_id = v_tax_category_id_holder
7105: WHERE line_focus_id = v_line_focus_id;
7106: EXCEPTION
7107: WHEN OTHERS THEN
7108: RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_po_tax_pkg.copy_quot_taxes : '||SQLERRM, TRUE);
7109: END;
7110: -- End, cbabu for EnhancementBug# 2427465
7111: */
7112: OPEN cur_vendor;
7142: */
7143: -----------------------------------------------------------
7144: -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
7145:
7146: jai_po_tax_pkg.calculate_tax
7147: ( 'STANDARDPO', p_po_hdr_id , p_po_line_id, p_line_loc_id,
7148: p_qty, p_price*p_qty, p_unit_code, v_tax_amt, p_assessable_value, ln_vat_assess_value*p_qty, NULL -- Ravi for VAT
7149: /* for bug 16013918 by anupgupt
7150: , pn_gst_assessable_value => ln_gst_assessable_value*p_qty); -- Added by Jia for GST Bug#10043656 on 2010/09/10
7406:
7407: /* end 6134628
7408: */
7409:
7410: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_agreement_taxes';
7411: BEGIN
7412:
7413: /*-----------------------------------------------------------------------------------------------------------------------
7414: CHANGE HISTORY: FILENAME: ja_in_po_conc_process_p.sql
7558: 'ln_vat_assess_value ' || ln_vat_assess_value||
7559: 'v_rate, 1 ) ' || v_rate );
7560: */
7561:
7562: jai_po_tax_pkg.Ja_In_Po_Case2( lv_type_lookup_code ,
7563: lv_quot_class_code ,
7564: v_vendor_id,
7565: v_vendor_site_id,
7566: lv_curr,
7720: -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
7721:
7722: jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, DUMMY, v_doc_curr, v_curr_conv_factor ) ;
7723:
7724: jai_po_tax_pkg.calculate_tax
7725: ( 'STANDARDPO', v_po_hdr_id , v_po_line_id, v_line_loc_id,
7726: v_qty, v_qty*v_temp_price, v_uom_code,
7727: v_assessable_value, v_assessable_value, NULL, v_curr_conv_factor
7728: /* for bug 16013918 by anupgupt
7937: SET tax_category_id = v_tax_category_id_holder
7938: WHERE line_focus_id = p_seq_val;
7939: EXCEPTION
7940: WHEN OTHERS THEN
7941: RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_po_tax_pkg.copy_agreement_taxes: '||SQLERRM, TRUE);
7942: END;
7943: -- End, cbabu for EnhancementBug# 2427465
7944: */
7945:
8019: jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, DUMMY, v_doc_curr, v_curr_conv_factor ) ;
8020: Tax_Amount := v_unit_price * p_qty; --Add by Xiao for bug#12338084.
8021:
8022: -- Pass Line_Location_Id in place of Header_Id in procedure Cal_Tax.
8023: jai_po_tax_pkg.calculate_tax( 'RELEASE', p_line_loc_id, p_line_id, v_line_loc_id,
8024: p_qty, v_unit_price * p_qty, v_uom_code,
8025: Tax_Amount, v_assessable_value,ln_vat_assess_value, NULL, v_curr_conv_factor
8026: , pv_retroprice_changed --Added by Kevin Cheng for Retroactive Price 2008/01/10
8027: /* for bug 16013918 by anupgupt
8044: CHANGE HISTORY for FILENAME - ja_in_po_default_taxes_pkg.sql
8045: S.No Date Author and Details
8046: -------------------------------------------------
8047: 1 5/12/2002 Vijay Shankar for Bug# 2695844, FileVersion: 615.1
8048: when making a call to jai_po_tax_pkg.calculate_tax procedure, v_uom is going as NULL and so the procedure is making a call to po_lines_all
8049: table to fetch the uom and which is giving the mutating error.
8050: Created a new variable v_uom_code and passing the same to jai_po_tax_pkg.calculate_tax that will not make a query on po_lines_all
8051:
8052: 2 6/12/2002 Vijay Shankar for EnhancementBug# 2427465, FileVersion# 615.2
8046: -------------------------------------------------
8047: 1 5/12/2002 Vijay Shankar for Bug# 2695844, FileVersion: 615.1
8048: when making a call to jai_po_tax_pkg.calculate_tax procedure, v_uom is going as NULL and so the procedure is making a call to po_lines_all
8049: table to fetch the uom and which is giving the mutating error.
8050: Created a new variable v_uom_code and passing the same to jai_po_tax_pkg.calculate_tax that will not make a query on po_lines_all
8051:
8052: 2 6/12/2002 Vijay Shankar for EnhancementBug# 2427465, FileVersion# 615.2
8053: tax_category_id column is populated into PO and SO localization tables, which will be used to
8054: identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into the
8247: lv_adhoc_flag VARCHAR2(1) ;
8248: ln_tax_id NUMBER ;
8249: ln_quot_qty NUMBER ;
8250: --End Added for Forward Porting kundan kumar
8251: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_case1';
8252:
8253: BEGIN
8254:
8255: --File.Sql.35 Cbabu
8399: END IF ;
8400:
8401: --End Added by kundan kumar for forward porting to R12
8402:
8403: jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
8404: v_seq_val, v_line_loc_id,
8405: v_tax_line_no, v_po_line_id, v_po_hdr_id,
8406: v_prec1, v_prec2, v_prec3, v_prec4, v_prec5, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
8407: v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
8469: END IF ;
8470: --End , added by rchandan for bug#4591242
8471: ---Added the above lines of code for forward porting to R12 ;kundan kumar
8472:
8473: jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
8474: v_seq_val, v_line_loc_id,
8475: v_tax_line_no, v_po_line_id, v_po_hdr_id,
8476: v_prec1, v_prec2, v_prec3, v_prec4, v_prec5, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
8477: v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
8539: If ( ( v_type_lookup_code <> 'QUOTATION' AND v_quot_class_code <> 'CATALOG' )
8540: OR v_type_lookup_code <> 'BLANKET' )
8541: THEN
8542:
8543: jai_po_tax_pkg.calculate_tax( 'STANDARDPO', v_po_hdr_id , v_po_line_id, v_line_loc_id,
8544: -- v_qty, v_price*v_qty, v_uom, v_tax_amt, v_assessable_value, v_item_id ); -- cbabu for Bug# 2695844
8545: v_qty, v_price*v_qty, v_uom_code, v_tax_amt, v_assessable_value,ln_vat_assess_value, -- added, Harshita for bug #4245062
8546: v_item_id
8547: /* for bug 16013918 by anupgupt
8682: FROM JAI_PO_TAXES
8683: WHERE Line_Location_Id = v_line_loc_id
8684: AND Tax_Type <> jai_constants.tax_type_tds; -- 'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
8685:
8686: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_case2';
8687:
8688: --added by peng.zheng for bug 10043656, begins
8689: /* for bug 16013918 by anupgupt
8690: lv_enable_gst_flag VARCHAR2(3);
9043: WHERE tax_id = cp_tax_id;
9044:
9045: lv_adhoc_flag varchar2(1); --rchandan for bug#4591242
9046: --End kundan kumar
9047: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_insert';
9048: BEGIN
9049: --Added the following cursor opening for forward porting to R12
9050: OPEN cur_taxes_adhoc(v_taxid); -- added rchandan for bug#4591242
9051: FETCH cur_taxes_adhoc INTO lv_adhoc_flag;
9093: FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
9094: app_exception.raise_exception;
9095:
9096: END Ja_In_Po_Insert;
9097: END jai_po_tax_pkg;