[Home] [Help]
27: --Anuradha Parthasarathy
28: v_inventory_item_id NUMBER ;
29: v_order_line_id NUMBER ;
30: v_location_id NUMBER ;
31: v_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
32: v_uom_code JAI_OM_WSH_LINES_ALL.UNIT_CODE%TYPE ;
33: v_shp_qty NUMBER ;
34: v_item_class JAI_INV_ITM_SETUPS.ITEM_CLASS%TYPE ;
35: v_excise_flag VARCHAR2(1) ;
28: v_inventory_item_id NUMBER ;
29: v_order_line_id NUMBER ;
30: v_location_id NUMBER ;
31: v_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
32: v_uom_code JAI_OM_WSH_LINES_ALL.UNIT_CODE%TYPE ;
33: v_shp_qty NUMBER ;
34: v_item_class JAI_INV_ITM_SETUPS.ITEM_CLASS%TYPE ;
35: v_excise_flag VARCHAR2(1) ;
36: v_basic_ed_amt NUMBER ;
35: v_excise_flag VARCHAR2(1) ;
36: v_basic_ed_amt NUMBER ;
37: v_addl_ed_amt NUMBER ;
38: v_oth_ed_amt NUMBER ;
39: v_excise_exempt_type JAI_OM_WSH_LINES_ALL.EXCISE_EXEMPT_TYPE%TYPE ;
40: v_selling_price NUMBER ;
41: v_set_of_books_id NUMBER ;
42: v_currency_code OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE%TYPE ;
43: v_conv_type_code OE_ORDER_HEADERS_ALL.CONVERSION_TYPE_CODE%TYPE ;
75: v_start_number NUMBER ;
76: v_end_number NUMBER ;
77: v_jump_by NUMBER ;
78: v_prefix VARCHAR2(50) ;
79: V_EXC_INVOICE_NO JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_NO%TYPE ;
80: v_excise_check NUMBER := 0 ; --added by Vijay on 2002/02/07
81: v_gp_1 NUMBER ;
82: v_gp_2 NUMBER ;
83: v_tax_rate NUMBER ;
125: v_tot_excise_amt NUMBER ;
126: v_tot_basic_ed_amt NUMBER ;
127: v_tot_addl_ed_amt NUMBER ;
128: v_tot_oth_ed_amt NUMBER ;
129: v_old_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
130: v_old_excise_invoice_no VARCHAR2(200) ;
131: v_status_code WSH_NEW_DELIVERIES.STATUS_CODE%TYPE ;
132: v_org_id NUMBER ; --2001/04/01 Vijay
133: v_source_line_id_pick NUMBER ;
159: A.add_excise_duty_amount, A.oth_excise_duty_amount, A.excise_amount,
160: A.excise_exempt_type, A.selling_price, A.customer_id, A.ship_to_org_id,
161: A.order_type_id, A.subinventory, A.assessable_value,
162: A.EXCISE_EXEMPT_REFNO, A.org_id -- added a.org_id by vijay for multi org support
163: FROM JAI_OM_WSH_LINES_ALL A,
164: JAI_INV_ITM_SETUPS B
165: WHERE A.delivery_detail_id = p_delivery_detail_id
166: AND A.organization_id = b.organization_id
167: AND A.inventory_item_id = b.inventory_item_id
337: -- GROUP BY A.subinventory; -- cbabu for Bug# 2736191
338:
339: CURSOR shipped_qty_cur (p_picking_line_id IN NUMBER) IS
340: SELECT SUM(quantity) shipped_quantity
341: FROM JAI_OM_WSH_LINES_ALL
342: WHERE delivery_detail_id = p_picking_line_id;
343:
344: CURSOR Header_id (p_picking_line_id IN NUMBER) IS
345: SELECT source_line_id, source_header_id
386: nvl(round(sum(add_excise_duty_amount * cp_conversion_rate)),0) ,
387: nvl(round(sum(oth_excise_duty_amount * cp_conversion_rate)),0) ,
388: nvl(round(sum(excise_amount * cp_conversion_rate)),0)
389: FROM
390: JAI_OM_WSH_LINES_ALL
391: WHERE
392: delivery_id = p_delivery_id
393: and excise_exempt_type is null; -- sriram - 5th nov - bug # 3207685
394: -- End of bug #3448674
395:
396: -- following cursor added by sriram - bug # 3207685
397: cursor c_get_modvat_records (p_delivery_id NUMBER) IS
398: select delivery_detail_id , quantity , assessable_Value , excise_exempt_type
399: from JAI_OM_WSH_LINES_ALL
400: where delivery_id = p_delivery_id
401: and excise_exempt_type is not null;
402:
403: v_mod_basic_ed_amt number:=0;
403: v_mod_basic_ed_amt number:=0;
404:
405: CURSOR get_prev_del_dtl(p_delivery_id NUMBER) IS
406: SELECT register, EXCISE_INVOICE_NO
407: FROM JAI_OM_WSH_LINES_ALL
408: WHERE delivery_id = p_delivery_id
409: AND register IS NOT NULL;
410:
411: --Anuradha Parthasarathy
452: -- additions by sriram ends here
453:
454: /*
455: || Added by aiyer for the bug 3446362.
456: || Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
457: || Even records with null values are fine.
458: */
459: CURSOR c_ct3_flag_exists
460: IS
460: IS
461: SELECT
462: count(1)
463: FROM
464: JAI_OM_WSH_LINES_ALL
465: WHERE
466: delivery_id = p_delivery_id AND
467: nvl(excise_exempt_type,'$$') <> 'CT3';
468:
498: || Added by Ramananda
499: || End of bug#4543424
500: */
501:
502: CURSOR c_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
503: SELECT sum(jsptl.func_tax_amount) tax_amount
504: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
505: JAI_CMN_TAXES_ALL jtc
506: WHERE jtc.tax_id = jsptl.tax_id
505: JAI_CMN_TAXES_ALL jtc
506: WHERE jtc.tax_id = jsptl.tax_id
507: AND delivery_detail_id in
508: (SELECT delivery_detail_id
509: FROM JAI_OM_WSH_LINES_ALL
510: WHERE delivery_id = cp_delivery_id
511: )
512: AND upper(jtc.tax_type) in (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
513: -- AND upper(jtc.tax_type) in ('CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS');
512: AND upper(jtc.tax_type) in (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
513: -- AND upper(jtc.tax_type) in ('CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS');
514:
515: /*Cursor added by ssawant for bug 5989740 */
516: CURSOR c_sh_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
517: SELECT sum(jsptl.func_tax_amount) tax_amount
518: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
519: JAI_CMN_TAXES_ALL jtc
520: WHERE jtc.tax_id = jsptl.tax_id
519: JAI_CMN_TAXES_ALL jtc
520: WHERE jtc.tax_id = jsptl.tax_id
521: AND delivery_detail_id in
522: (SELECT delivery_detail_id
523: FROM JAI_OM_WSH_LINES_ALL
524: WHERE delivery_id = cp_delivery_id
525: )
526: AND upper(jtc.tax_type) in (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
527:
539: ||Code added by aiyer for the bug 4566054
540: ||Get the total cess amount at the delivery detail level
541: ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
542: */
543: CURSOR cur_get_del_det_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
544: IS
545: SELECT
546: sum(func_tax_amount) cess_amount
547: FROM
544: IS
545: SELECT
546: sum(func_tax_amount) cess_amount
547: FROM
548: jai_om_wsh_lines_all jspl ,
549: jai_om_wsh_line_taxes jsptl,
550: jai_cmn_taxes_all jtc
551: WHERE
552: jspl.delivery_detail_id = jsptl.delivery_detail_id AND
557: ln_del_det_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE;
558: /* End of bug 4566054 */
559:
560: /*START, Bgowrava for forward porting bug#5989740*/
561: CURSOR cur_get_del_det_sh_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
562: IS
563: SELECT
564: sum(func_tax_amount) cess_amount
565: FROM
562: IS
563: SELECT
564: sum(func_tax_amount) cess_amount
565: FROM
566: JAI_OM_WSH_LINES_ALL jspl,
567: JAI_OM_WSH_LINE_TAXES jsptl,
568: JAI_CMN_TAXES_ALL jtc
569: WHERE
570: jspl.delivery_detail_id = jsptl.delivery_detail_id AND
812: 2) if other than CT3 type of excise exemption is chosen in the sales order
813: both quantity and amount registers get hit.
814:
815: 3) Please note that in cases where amount registers are not hit because of CT3 excise exemption, the
816: register column in the JAI_OM_WSH_LINES_ALL table will show NULL. this is
817: also the change which i have incorporated.
818:
819: Dependency Due to This Bug:-
820: None
869:
870: Fix:-
871: =====
872: The fix done is that instead of setting the v_reg_type to null when a 'CT3' exemption is found and later
873: updating this variable into JAI_OM_WSH_LINES_ALL.register, handled this condition though a decode
874: statement, instead of directly updating the v_reg_type variable.
875: The v_reg_type now still holds the value for the register, where as the table is updated with null when ever the exemption is 'CT3'.
876: This has rectified the problem.
877:
893: =====
894: The fix done is that instead of condition - NVL(wdd.inv_interfaced_flag,'N') = 'N'
895: The new condition is used - NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
896: This has rectified the problem.
897: While updating the JAI_OM_WSH_LINES_ALL, new columns are added -
898: last_update_date, last_updated_by, last_update_login, as these were not previously updated
899:
900: Dependency Due to This Bug:-
901: None
983: The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
984: as 'WSH' from jai_om_wsh.plb procedure Shipment.
985: Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
986: For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
987: exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
988: becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
989:
990: Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
991: This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
1033: ENH: Handling Secondary and Higher Education Cess
1034: Added the new cess types jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess
1035:
1036: 54. 28/05/2007 CSahoo for bug#6077065, File version 120.10
1037: Issue: The excise invoice num and excise_invoice_date was not getting updated in the JAI_OM_WSH_LINES_ALL table.
1038: Fix:added the following line in the code lv_exc_inv_gen_for_dlry_flag := 'Y'.
1039:
1040:
1041: 55. 05/07/2007 kunkumar for Bug#6121833 File version 120.11
1722: and lv_exc_inv_gen_for_dlry_flag = 'Y'
1723: then
1724:
1725: UPDATE
1726: JAI_OM_WSH_LINES_ALL
1727: SET
1728: excise_invoice_no = v_exc_invoice_no,
1729: excise_invoice_date = TRUNC(v_actual_shipment_date), --replaced v_date for bug#7591616
1730: register = DECODE(nvl(v_excise_exempt_type,'$$$'), 'CT3',NULL,v_reg_type), /*register should be updated as null incase of CT3 excise exemption Bug 3446362*/
1906: END IF;
1907: -- Start of Bug 3446362
1908: /*
1909: Added by aiyer for the bug 3446362.
1910: Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
1911: Even records with null values are fine.
1912: If the count of rows with CT3 excise_exemption_type is greater than zero meaning
1913: some records are present which do not have CT3 excise exemption, then only in such a scenario let the
1914: amount registers be hit.