[Home] [Help]
15: x Number; --File.Sql.35 Cbabu := 0;
16:
17: Cursor del_count IS
18: Select 1
19: From JAI_OM_WSH_LINES_ALL
20: Where delivery_detail_id = v_delivery_detail_id;
21:
22:
23: BEGIN
94:
95: If nvl(x,0) <> 1 then
96: Return;
97: Else
98: DELETE JAI_OM_WSH_LINES_ALL
99: WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
100:
101: DELETE JAI_OM_WSH_LINE_TAXES
102: WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
464: jai_constants.tax_type_exc_other,
465: jai_constants.tax_type_exc_edu_cess,
466: jai_constants.tax_type_sh_exc_edu_cess);
467:
468: CURSOR cur_chk_org_loc_setup (cp_organization_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
469: cp_location_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE
470: )
471: IS
472: SELECT 1
465: jai_constants.tax_type_exc_edu_cess,
466: jai_constants.tax_type_sh_exc_edu_cess);
467:
468: CURSOR cur_chk_org_loc_setup (cp_organization_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
469: cp_location_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE
470: )
471: IS
472: SELECT 1
473: FROM jai_rgm_parties rgmpt,
1251: REM 02/11/2006 for Bug 5228046 by SACSETHI, File version 120.2
1252: REM Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
1253: REM This bug has datamodel and spec changes.
1254: REM 16-Apr-2010 modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement).
1255: REM modified population logic of table jai_om_wsh_lines_all to populate column
1256: REM shippable_flag as 'Y'.
1257: REM 28-Apr-2010 modified by Allen Yang for bug 9666476
1258: REM logic of shippable_flag population is changed back. For shippable lines,
1259: REM shippable_flag will be populated with NULL.
1319: v_raise_error_flag VARCHAR2(1);
1320: v_bonded_flag VARCHAR2(1);
1321: v_register_code VARCHAR2(30);
1322: v_fin_year NUMBER;
1323: v_old_register JAI_OM_WSH_LINES_ALL.register%TYPE;
1324: v_old_excise_invoice_no VARCHAR2(200);
1325: v_reg_type VARCHAR2(10);
1326: v_rg_type VARCHAR2(1);
1327: v_exc_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
1323: v_old_register JAI_OM_WSH_LINES_ALL.register%TYPE;
1324: v_old_excise_invoice_no VARCHAR2(200);
1325: v_reg_type VARCHAR2(10);
1326: v_rg_type VARCHAR2(1);
1327: v_exc_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
1328: v_tot_excise_amt NUMBER;
1329: v_tot_basic_ed_amt NUMBER;
1330: v_tot_addl_ed_amt NUMBER;
1331: v_tot_oth_ed_amt NUMBER;
1364: -- end of addition by subbu
1365:
1366: -- Added by Brathod for Bug#4215808
1367: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1368: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1369: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1370: ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1371: lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1372: -- End of Bug#4215808
1365:
1366: -- Added by Brathod for Bug#4215808
1367: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1368: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1369: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1370: ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1371: lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1372: -- End of Bug#4215808
1373:
1366: -- Added by Brathod for Bug#4215808
1367: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1368: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1369: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1370: ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1371: lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1372: -- End of Bug#4215808
1373:
1374:
1367: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1368: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1369: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1370: ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1371: lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1372: -- End of Bug#4215808
1373:
1374:
1375: CURSOR Get_Status_Cur IS
1495: AND b.Tax_Id = A.Tax_Id
1496: AND b.Tax_Type <> 'TDS';
1497: CURSOR Get_Delivery_Line_Count_Cur IS
1498: SELECT COUNT(*)
1499: FROM JAI_OM_WSH_LINES_ALL
1500: WHERE Delivery_Detail_Id = v_delivery_detail_id
1501: AND Delivery_id = v_delivery_id; /* Added delivery_id by mmurtuza for bug 13885787*/
1502: CURSOR Location_Cursor IS
1503: SELECT NVL(Location_id,0),
1542: A.receipt_id IS NOT NULL;
1543:
1544: CURSOR ja_in_so_picking_exc_check(p_delivery_id NUMBER) IS
1545: SELECT DISTINCT register
1546: FROM JAI_OM_WSH_LINES_ALL
1547: WHERE delivery_id = p_delivery_id;
1548:
1549: --2001/07/10 Anuradha Parthasarathy
1550: CURSOR item_trading_cur IS
1721: v_asst_register_id Number;
1722: v_reg_exp_date Date;
1723: v_lou_flag Varchar2(1);
1724:
1725: CURSOR c_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1726: SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1727: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1728: JAI_CMN_TAXES_ALL jtc
1729: WHERE jtc.tax_id = jsptl.tax_id
1728: JAI_CMN_TAXES_ALL jtc
1729: WHERE jtc.tax_id = jsptl.tax_id
1730: AND delivery_detail_id in
1731: (SELECT delivery_detail_id
1732: FROM JAI_OM_WSH_LINES_ALL
1733: WHERE delivery_id = cp_delivery_id
1734: )
1735: AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1736:
1739:
1740: ln_sh_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1741: /* Cursor is responsible to get secondary and higher cess */
1742:
1743: CURSOR c_sh_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1744: SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1745: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1746: JAI_CMN_TAXES_ALL jtc
1747: WHERE jtc.tax_id = jsptl.tax_id
1746: JAI_CMN_TAXES_ALL jtc
1747: WHERE jtc.tax_id = jsptl.tax_id
1748: AND delivery_detail_id in
1749: (SELECT delivery_detail_id
1750: FROM JAI_OM_WSH_LINES_ALL
1751: WHERE delivery_id = cp_delivery_id
1752: )
1753: AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_sh_exc_edu_cess),
1754: upper(jai_constants.tax_type_sh_cvd_edu_cess)
1767: ln_vat_proc_cnt NUMBER DEFAULT 0 ;
1768: ln_regime_id JAI_RGM_ORG_REGNS_V.REGIME_ID%TYPE;
1769: lv_regns_num JAI_RGM_ORG_REGNS_V.ATTRIBUTE_VALUE%TYPE;
1770:
1771: CURSOR cur_chk_vat_exists (cp_del_det_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
1772: IS
1773: SELECT 1
1774: FROM JAI_OM_WSH_LINE_TAXES jsptl,
1775: JAI_CMN_TAXES_ALL jtc
1791: AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
1792: AND attribute_code = jai_constants.attr_code_regn_no
1793: AND regime_code = jai_constants.vat_regime;
1794:
1795: CURSOR cur_chk_vat_proc_entry (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)
1796: IS
1797: SELECT 1
1798: FROM JAI_RGM_INVOICE_GEN_T
1799: WHERE delivery_id = cp_delivery_id;
1803: /*
1804: || Added by csahoo for bug#5680459
1805: || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
1806: */
1807: CURSOR c_chk_vat_reversal (cp_del_det_id JAI_OM_WSH_LINES_ALL.delivery_detail_id%TYPE,
1808: cp_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE )
1809: IS
1810: SELECT 1
1811: FROM JAI_OM_WSH_LINE_TAXES jsptl,
1887: 6 03/10/2001 Anuradha Parthasarathy
1888: Tax Calculation as per the Inventory Uom
1889:
1890: 7 08/05/2002 Sriram SJ Bug # 2330055
1891: Insert of non zero selling price and assessable value in JAI_OM_WSH_LINES_ALL even though
1892: tax lines are not there.
1893:
1894: 8 03/07/2002 Nagaraj.s - For Enh#2415656.
1895: Cursors pref_cur - Incorporated v_export_oriented_unit also in the select clause
1913: is carried over to AR , but the taxes are not present in Shiping Localization tables ,
1914: causing lot of Data fix requirements.
1915:
1916: 12 09/01/2003 cbabu for Bug# 2736191 - File Version# 615.4
1917: For trading functionality JAI_OM_WSH_LINES_ALL.excise_amount is getting populated NULL always, if one
1918: of 'Excise', 'Addl. Excise', 'Other Excise' tax components is missing. ED_CUR cursor is modified to fetch
1919: 0 if query does not retreive any data.
1920:
1921: 13. 12/03/2003 Arun Iyer Bug # 2828927 615.5
2050: on the quantity causing the taxes to be calculated as less than that of the actual value
2051:
2052: Solution :- Issue has been resolved by doing the following :
2053: The apportion based on quantity shipped has been removed and instead the tax amount from
2054: the JAI_CMN_MATCH_TAXES table is used for population into JAI_OM_WSH_LINES_ALL
2055: and jain_so_picking_tax_lines table
2056:
2057: Dependency : None
2058:
2103: jai_om_wsh_pkg.get_excise_register_with_bal (version 115.1) also packaged as part of this bug.
2104:
2105: 23. 2005/03/15 brathod - Bug#4215808- File version 115.5
2106: Trigger modified for VAT Implementation.
2107: New VAT fields in JAI_OM_WSH_LINES_ALL are populated by fetching them
2108: from JAI_OM_OE_SO_LINES table. Also populated jai_vat_preocessing_t temporery
2109: table for VAT invoice number generation
2110:
2111: 24 26/04/2005 Brathod for Bug# 4299606 File Version 116.1
2188: Further modified the procedure ARU_T2. Added a check the if it a non inventory item, then
2189: the error should not be shown.
2190:
2191: 35 16-Apr-2010 modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement).
2192: modified population logic of table jai_om_wsh_lines_all to populate column
2193: shippable_flag as 'Y'.
2194: File Version: 120.9.12010000.12
2195:
2196: 36 28-Apr-2010 modified by Allen Yang for bug 9666476
2664: END IF;
2665:
2666:
2667: -- Accumulate the respective types of Excise Duties
2668: -- for inserting into JAI_OM_WSH_LINES_ALL Table.
2669: IF rec.tax_type = 'Excise' THEN --3
2670: v_basic_excise_duty_amount := NVL(v_basic_excise_duty_amount,0) + v_tax_amount ;
2671: ELSIF rec.tax_type = 'Addl. Excise' THEN --3
2672: v_add_excise_duty_amount := NVL(v_add_excise_duty_amount,0) + v_tax_amount ;
2949: to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
2950: The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
2951: hence applying the check that
2952: v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
2953: Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
2954: 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
2955: */
2956:
2957:
2970: FETCH ed_cur INTO v_oth_excise_duty_amount;
2971: CLOSE ed_cur;
2972: END IF;
2973: --Get Cumulative Excise amount
2974: --for inserting into JAI_OM_WSH_LINES_ALL Table.
2975: v_excise_amount := (v_basic_excise_duty_amount + v_add_excise_duty_amount +
2976: v_oth_excise_duty_amount);
2977: -- Get Total Tax Amount for the Line
2978: -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
2974: --for inserting into JAI_OM_WSH_LINES_ALL Table.
2975: v_excise_amount := (v_basic_excise_duty_amount + v_add_excise_duty_amount +
2976: v_oth_excise_duty_amount);
2977: -- Get Total Tax Amount for the Line
2978: -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
2979: OPEN Get_Tot_Tax_Amount_Cur;
2980: FETCH Get_Tot_Tax_Amount_Cur INTO v_tot_tax_amount;
2981: CLOSE Get_Tot_Tax_Amount_Cur;
2982: -- Check for Delivery lines existence in JAI_OM_WSH_LINES_ALL Table
2978: -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
2979: OPEN Get_Tot_Tax_Amount_Cur;
2980: FETCH Get_Tot_Tax_Amount_Cur INTO v_tot_tax_amount;
2981: CLOSE Get_Tot_Tax_Amount_Cur;
2982: -- Check for Delivery lines existence in JAI_OM_WSH_LINES_ALL Table
2983: OPEN Get_Delivery_Line_Count_Cur ;
2984: FETCH Get_Delivery_Line_Count_Cur INTO v_delivery_line_count;
2985: CLOSE Get_Delivery_Line_Count_Cur ;
2986: IF v_delivery_line_count = 0 THEN
2984: FETCH Get_Delivery_Line_Count_Cur INTO v_delivery_line_count;
2985: CLOSE Get_Delivery_Line_Count_Cur ;
2986: IF v_delivery_line_count = 0 THEN
2987: --5
2988: INSERT INTO JAI_OM_WSH_LINES_ALL(
2989: Delivery_Detail_Id,
2990: Order_Header_Id,
2991: Order_Line_Id,
2992: split_from_delivery_detail_id,
3202: END IF;
3203: -- End of Bug#4215808
3204:
3205: ELSE
3206: UPDATE JAI_OM_WSH_LINES_ALL
3207: SET quantity = v_shipped_quantity,
3208: tax_amount = v_tot_tax_amount,
3209: order_line_id = v_source_line_id,
3210: excise_amount = v_excise_amount,