[Home] [Help]
1: PACKAGE BODY jai_po_tax_pkg AS
2: /* $Header: jai_po_tax.plb 120.36.12010000.4 2009/02/06 10:04:29 nprashar ship $ */
3:
4: /* --------------------------------------------------------------------------------------
5: Filename:
362: FROM JAI_PO_TAXES
363: WHERE Line_Location_Id = linelocid -- For Blanket Rel Line Loc Id is passed in place of header id.
364: AND Tax_Type <> jai_constants.tax_type_tds ; --'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
365:
366: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.calculate_tax';
367: BEGIN
368:
369: /*------------------------------------------------------------------------------------------
370: FILENAME: ja_in_cal_tax_p.sql
2087:
2088: IF lv_process_flag IN ('EE', 'UE')
2089: THEN
2090: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2091: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALCULATE_TAX.Err:'||lv_process_message);
2092: app_exception.raise_exception;
2093: END IF;
2094: END IF;
2095: --=====================================================
2113:
2114: IF lv_process_flag IN ('EE', 'UE')
2115: THEN
2116: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2117: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALCULATE_TAX.Err:'||lv_process_message);
2118: app_exception.raise_exception;
2119: END IF;
2120: END IF;
2121: --=====================================================
2206: spid V$PROCESS.SPID%TYPE;
2207: v_name1 V$DATABASE.NAME%TYPE;
2208:
2209:
2210: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.batch_quot_taxes_copy';
2211: BEGIN
2212:
2213: /*------------------------------------------------------------------------------------------------------------------------
2214: S.No DATE Author AND Details
2302: CLOSE c_line_location_qty;
2303:
2304: -- Call the procedure which defaults the taxes from Quotation to PO and inserts shipment lines into
2305: -- JA tables
2306: jai_po_tax_pkg.copy_quot_taxes(
2307: errbuf => v_errbuf,
2308: retcode => v_retcode,
2309: p_line_loc_id => v_line_location_id,
2310: p_po_hdr_id => c_rec.po_header_id,
2323: );
2324:
2325: -- check whether called procedure returned any error
2326: IF ( v_errbuf IS NOT NULL) THEN
2327: v_error_mesg := 'Error from called unit jai_po_tax_pkg.copy_quot_taxes -> '||v_errbuf;
2328: RAISE error_from_called_unit;
2329: END IF;
2330:
2331: DELETE FROM JAI_PO_QUOT_LINES_T
2337: EXCEPTION
2338:
2339: WHEN OTHERS THEN
2340: IF v_error_mesg IS NULL THEN
2341: -- the exception condition is not because of returned error from jai_po_tax_pkg.copy_quot_taxes
2342: v_error_mesg := 'Error in main for loop. SQLERRM -> '|| SQLERRM;
2343: END IF;
2344:
2345: ROLLBACK;
2480: CURSOR Fetch_UOMCode_Cur IS SELECT Uom_Code
2481: FROM Mtl_Units_Of_Measure
2482: WHERE Unit_Of_Measure = v_uom;
2483:
2484: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_reqn_taxes';
2485: BEGIN
2486:
2487: /*--------------------------------------------------------------------------------------------------------------------------
2488: CHANGE HISTORY for FILENAME - jai_po_tax_pkg.copy_reqn_taxes.sql
2484: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_reqn_taxes';
2485: BEGIN
2486:
2487: /*--------------------------------------------------------------------------------------------------------------------------
2488: CHANGE HISTORY for FILENAME - jai_po_tax_pkg.copy_reqn_taxes.sql
2489: S.No Date Author and Details
2490: -------------------------------------------------------------------------------------------------------------------------
2491: 1 22/11/2001 sriram bug# Version#115.1
2492:
2652: ELSE
2653: flag := 'I';
2654: END IF;
2655:
2656: jai_po_tax_pkg.Ja_In_Po_Case2( v_Type_Lookup_Code,
2657: v_Quot_Class_Code,
2658: v_Vendor_Id,
2659: v_Vendor_Site_Id,
2660: --p_Currency_Code,
2676: NVL( v_assessable_value, -9999 ),
2677: ln_vat_assess_value,
2678: NVL( v_conv_rate, 1 ) );
2679:
2680: /** jai_po_tax_pkg.calculate_tax( 'STANDARDPO', v_po_hdr_id , v_Po_Line_Id1, v_line_location_id,
2681: v_qty, v_price*v_qty, v_uom, v_assessable_value,
2682: NVL( v_assessable_value, v_price*v_qty ), NULL, nvl(v_conv_rate,1));
2683:
2684:
2964: FROM po_lines_all plines, mtl_units_of_measure units
2965: WHERE plines.po_line_id = p_line_id
2966: AND units.unit_of_measure = plines.unit_meas_lookup_code;
2967:
2968: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.calc_tax';
2969:
2970: /* Bug 5243532. Added by Lakshmi Gopalsami
2971: * Defined variable for implementing caching logic.
2972: */
3157: CHANGE HISTORY for FILENAME - ja_in_po_calc_tax_p.sql
3158: S.No Date Author and Details
3159: -------------------------------------------------
3160: 1. 30/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
3161: This procedure is created with this bug by getting the source from jai_po_tax_pkg.calculate_tax procedure with some modifications
3162: commented with bug number. Basically two new parameters were added.
3163: Initially all the plsql tables are filled with PO Currency data and finally when updating these into the database
3164: they are converted back to the tax currency if there is any change in the currency.
3165:
5039:
5040: IF lv_process_flag IN ('EE', 'UE')
5041: THEN
5042: FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
5043: FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_TAX_PKG.CALC_TAX.Err:'||lv_process_message);
5044: app_exception.raise_exception;
5045: END IF;
5046: END IF;
5047: --=====================================================
5282: OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
5283:
5284: -- Cursor variable for Fetch_Po_Ln_Loc_Cur
5285: fetch_ja_in_po_ln_loc_rec FETCH_JA_IN_PO_LN_LOC_CUR%ROWTYPE;
5286: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_source_taxes';
5287: BEGIN
5288:
5289: /*-----------------------------------------------------------------------------------------------------------------------
5290: CHANGE HISTORY: FILENAME: ja_in_po_copydoc_dflt_p.sql
5489: dummy NUMBER;
5490: ln_vat_assess_value NUMBER;
5491:
5492: v_tax_category_id_holder JAI_PO_LINE_LOCATIONS.tax_category_id%TYPE; -- cbabu for EnhancementBug# 2427465
5493: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_quot_taxes';
5494: ------------------------------>
5495:
5496: CURSOR tax_cur IS
5497: SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
5710: SET tax_category_id = v_tax_category_id_holder
5711: WHERE line_focus_id = v_line_focus_id;
5712: EXCEPTION
5713: WHEN OTHERS THEN
5714: RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_po_tax_pkg.copy_quot_taxes : '||SQLERRM, TRUE);
5715: END;
5716: -- End, cbabu for EnhancementBug# 2427465
5717: */
5718: OPEN cur_vendor;
5733: p_party_type => 'V'
5734: ) ; -- Ravi for VAT
5735:
5736:
5737: jai_po_tax_pkg.calculate_tax
5738: ( 'STANDARDPO', p_po_hdr_id , p_po_line_id, p_line_loc_id,
5739: p_qty, p_price*p_qty, p_unit_code, v_tax_amt, p_assessable_value, ln_vat_assess_value*p_qty, NULL );
5740: -- Ravi for VAT
5741:
5990:
5991: /* end 6134628
5992: */
5993:
5994: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.copy_agreement_taxes';
5995: BEGIN
5996:
5997: /*-----------------------------------------------------------------------------------------------------------------------
5998: CHANGE HISTORY: FILENAME: ja_in_po_conc_process_p.sql
6140: 'ln_vat_assess_value ' || ln_vat_assess_value||
6141: 'v_rate, 1 ) ' || v_rate );
6142: */
6143:
6144: jai_po_tax_pkg.Ja_In_Po_Case2( lv_type_lookup_code ,
6145: lv_quot_class_code ,
6146: v_vendor_id,
6147: v_vendor_site_id,
6148: lv_curr,
6276:
6277:
6278: jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, DUMMY, v_doc_curr, v_curr_conv_factor ) ;
6279:
6280: jai_po_tax_pkg.calculate_tax
6281: ( 'STANDARDPO', v_po_hdr_id , v_po_line_id, v_line_loc_id,
6282: v_qty, v_qty*v_temp_price, v_uom_code,
6283: v_assessable_value, v_assessable_value, NULL, v_curr_conv_factor
6284: );
6490: SET tax_category_id = v_tax_category_id_holder
6491: WHERE line_focus_id = p_seq_val;
6492: EXCEPTION
6493: WHEN OTHERS THEN
6494: RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_po_tax_pkg.copy_agreement_taxes: '||SQLERRM, TRUE);
6495: END;
6496: -- End, cbabu for EnhancementBug# 2427465
6497: */
6498:
6543:
6544: jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, DUMMY, v_doc_curr, v_curr_conv_factor ) ;
6545:
6546: -- Pass Line_Location_Id in place of Header_Id in procedure Cal_Tax.
6547: jai_po_tax_pkg.calculate_tax( 'RELEASE', p_line_loc_id, p_line_id, v_line_loc_id,
6548: p_qty, v_unit_price * p_qty, v_uom_code,
6549: Tax_Amount, v_assessable_value,ln_vat_assess_value, NULL, v_curr_conv_factor
6550: , pv_retroprice_changed --Added by Kevin Cheng for Retroactive Price 2008/01/10
6551: ); -- Ravi for VAT
6564: CHANGE HISTORY for FILENAME - ja_in_po_default_taxes_pkg.sql
6565: S.No Date Author and Details
6566: -------------------------------------------------
6567: 1 5/12/2002 Vijay Shankar for Bug# 2695844, FileVersion: 615.1
6568: 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
6569: table to fetch the uom and which is giving the mutating error.
6570: 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
6571:
6572: 2 6/12/2002 Vijay Shankar for EnhancementBug# 2427465, FileVersion# 615.2
6566: -------------------------------------------------
6567: 1 5/12/2002 Vijay Shankar for Bug# 2695844, FileVersion: 615.1
6568: 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
6569: table to fetch the uom and which is giving the mutating error.
6570: 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
6571:
6572: 2 6/12/2002 Vijay Shankar for EnhancementBug# 2427465, FileVersion# 615.2
6573: tax_category_id column is populated into PO and SO localization tables, which will be used to
6574: identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into the
6764: lv_adhoc_flag VARCHAR2(1) ;
6765: ln_tax_id NUMBER ;
6766: ln_quot_qty NUMBER ;
6767: --End Added for Forward Porting kundan kumar
6768: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_case1';
6769:
6770: BEGIN
6771:
6772: --File.Sql.35 Cbabu
6889: END IF ;
6890:
6891: --End Added by kundan kumar for forward porting to R12
6892:
6893: jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
6894: v_seq_val, v_line_loc_id,
6895: v_tax_line_no, v_po_line_id, v_po_hdr_id,
6896: 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 )
6897: v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
6959: END IF ;
6960: --End , added by rchandan for bug#4591242
6961: ---Added the above lines of code for forward porting to R12 ;kundan kumar
6962:
6963: jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
6964: v_seq_val, v_line_loc_id,
6965: v_tax_line_no, v_po_line_id, v_po_hdr_id,
6966: 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 )
6967: v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
7029: If ( ( v_type_lookup_code <> 'QUOTATION' AND v_quot_class_code <> 'CATALOG' )
7030: OR v_type_lookup_code <> 'BLANKET' )
7031: THEN
7032:
7033: jai_po_tax_pkg.calculate_tax( 'STANDARDPO', v_po_hdr_id , v_po_line_id, v_line_loc_id,
7034: -- v_qty, v_price*v_qty, v_uom, v_tax_amt, v_assessable_value, v_item_id ); -- cbabu for Bug# 2695844
7035: v_qty, v_price*v_qty, v_uom_code, v_tax_amt, v_assessable_value,ln_vat_assess_value, -- added, Harshita for bug #4245062
7036: v_item_id );
7037:
7162: FROM JAI_PO_TAXES
7163: WHERE Line_Location_Id = v_line_loc_id
7164: AND Tax_Type <> jai_constants.tax_type_tds; -- 'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
7165:
7166: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_case2';
7167:
7168: BEGIN
7169:
7170: v_vendor_id := vendor_id;
7462: WHERE tax_id = cp_tax_id;
7463:
7464: lv_adhoc_flag varchar2(1); --rchandan for bug#4591242
7465: --End kundan kumar
7466: lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_insert';
7467: BEGIN
7468: --Added the following cursor opening for forward porting to R12
7469: OPEN cur_taxes_adhoc(v_taxid); -- added rchandan for bug#4591242
7470: FETCH cur_taxes_adhoc INTO lv_adhoc_flag;
7512: FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
7513: app_exception.raise_exception;
7514:
7515: END Ja_In_Po_Insert;
7516: END jai_po_tax_pkg;