[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 ;
130: v_tot_excise_amt NUMBER ;
131: v_tot_basic_ed_amt NUMBER ;
132: v_tot_addl_ed_amt NUMBER ;
133: v_tot_oth_ed_amt NUMBER ;
134: v_old_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
135: v_old_excise_invoice_no VARCHAR2(200) ;
136: v_status_code WSH_NEW_DELIVERIES.STATUS_CODE%TYPE ;
137: v_org_id NUMBER ; --2001/04/01 Vijay
138: v_source_line_id_pick NUMBER ;
164: A.add_excise_duty_amount, A.oth_excise_duty_amount, A.excise_amount,
165: A.excise_exempt_type, A.selling_price, A.customer_id, A.ship_to_org_id,
166: A.order_type_id, A.subinventory, A.assessable_value,
167: A.EXCISE_EXEMPT_REFNO, A.org_id -- added a.org_id by vijay for multi org support
168: FROM JAI_OM_WSH_LINES_ALL A,
169: JAI_INV_ITM_SETUPS B
170: WHERE A.delivery_detail_id = p_delivery_detail_id
171: AND A.organization_id = b.organization_id
172: AND A.inventory_item_id = b.inventory_item_id
342: -- GROUP BY A.subinventory; -- cbabu for Bug# 2736191
343:
344: CURSOR shipped_qty_cur (p_picking_line_id IN NUMBER) IS
345: SELECT SUM(quantity) shipped_quantity
346: FROM JAI_OM_WSH_LINES_ALL
347: WHERE delivery_detail_id = p_picking_line_id;
348:
349: CURSOR Header_id (p_picking_line_id IN NUMBER) IS
350: SELECT source_line_id, source_header_id
399: round(nvl((sum(add_excise_duty_amount * cp_conversion_rate)),0),2) ,
400: round(nvl((sum(oth_excise_duty_amount * cp_conversion_rate)),0),2) ,
401: round(nvl((sum(excise_amount * cp_conversion_rate)),0),2) /*Added round function by mmurtuza for bug 16343067 / 16611281*/
402: FROM
403: JAI_OM_WSH_LINES_ALL
404: WHERE
405: delivery_id = p_delivery_id
406: and excise_exempt_type is null; -- sriram - 5th nov - bug # 3207685
407: -- End of bug #3448674
408:
409: -- following cursor added by sriram - bug # 3207685
410: cursor c_get_modvat_records (p_delivery_id NUMBER) IS
411: select delivery_detail_id , quantity , assessable_Value , excise_exempt_type
412: from JAI_OM_WSH_LINES_ALL
413: where delivery_id = p_delivery_id
414: and excise_exempt_type is not null;
415:
416: v_mod_basic_ed_amt number:=0;
416: v_mod_basic_ed_amt number:=0;
417:
418: CURSOR get_prev_del_dtl(p_delivery_id NUMBER) IS
419: SELECT register, EXCISE_INVOICE_NO
420: FROM JAI_OM_WSH_LINES_ALL
421: WHERE delivery_id = p_delivery_id
422: AND register IS NOT NULL;
423:
424: --Anuradha Parthasarathy
465: -- additions by sriram ends here
466:
467: /*
468: || Added by aiyer for the bug 3446362.
469: || Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
470: || Even records with null values are fine.
471: */
472: CURSOR c_ct3_flag_exists
473: IS
473: IS
474: SELECT
475: count(1)
476: FROM
477: JAI_OM_WSH_LINES_ALL
478: WHERE
479: delivery_id = p_delivery_id AND
480: nvl(excise_exempt_type,'$$') <> 'CT3';
481:
516: CURSOR c_excise_base_tax_amt(cp_delivery_detail_id JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE) IS
517: SELECT sum(NVL(JSPTL.base_tax_amount,0) / JOWLA.quantity)
518: FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
519: JAI_CMN_TAXES_ALL JTC,
520: JAI_OM_WSH_LINES_ALL JOWLA
521: WHERE JSPTL.TAX_ID = JTC.TAX_ID
522: AND JSPTL.delivery_detail_id = cp_delivery_detail_id
523: AND JSPTL.delivery_detail_id = JOWLA.delivery_detail_id
524: AND UPPER(JTC.TAX_TYPE) = 'EXCISE';
526: ln_total_of_base_tax NUMBER;
527:
528: /*End additions by mmurtuza for bug 15971482*/
529:
530: CURSOR c_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
531: SELECT sum(jsptl.func_tax_amount) tax_amount
532: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
533: JAI_CMN_TAXES_ALL jtc
534: WHERE jtc.tax_id = jsptl.tax_id
533: JAI_CMN_TAXES_ALL jtc
534: WHERE jtc.tax_id = jsptl.tax_id
535: AND delivery_detail_id in
536: (SELECT delivery_detail_id
537: FROM JAI_OM_WSH_LINES_ALL
538: WHERE delivery_id = cp_delivery_id
539: )
540: 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*/
541: -- AND upper(jtc.tax_type) in ('CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS');
540: 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*/
541: -- AND upper(jtc.tax_type) in ('CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS');
542:
543: /*Cursor added by ssawant for bug 5989740 */
544: CURSOR c_sh_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
545: SELECT sum(jsptl.func_tax_amount) tax_amount
546: FROM JAI_OM_WSH_LINE_TAXES jsptl ,
547: JAI_CMN_TAXES_ALL jtc
548: WHERE jtc.tax_id = jsptl.tax_id
547: JAI_CMN_TAXES_ALL jtc
548: WHERE jtc.tax_id = jsptl.tax_id
549: AND delivery_detail_id in
550: (SELECT delivery_detail_id
551: FROM JAI_OM_WSH_LINES_ALL
552: WHERE delivery_id = cp_delivery_id
553: )
554: AND upper(jtc.tax_type) in (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
555:
567: ||Code added by aiyer for the bug 4566054
568: ||Get the total cess amount at the delivery detail level
569: ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
570: */
571: CURSOR cur_get_del_det_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
572: IS
573: SELECT
574: sum(func_tax_amount) cess_amount
575: FROM
572: IS
573: SELECT
574: sum(func_tax_amount) cess_amount
575: FROM
576: jai_om_wsh_lines_all jspl ,
577: jai_om_wsh_line_taxes jsptl,
578: jai_cmn_taxes_all jtc
579: WHERE
580: jspl.delivery_detail_id = jsptl.delivery_detail_id AND
585: ln_del_det_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE;
586: /* End of bug 4566054 */
587:
588: /*START, Bgowrava for forward porting bug#5989740*/
589: CURSOR cur_get_del_det_sh_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
590: IS
591: SELECT
592: sum(func_tax_amount) cess_amount
593: FROM
590: IS
591: SELECT
592: sum(func_tax_amount) cess_amount
593: FROM
594: JAI_OM_WSH_LINES_ALL jspl,
595: JAI_OM_WSH_LINE_TAXES jsptl,
596: JAI_CMN_TAXES_ALL jtc
597: WHERE
598: jspl.delivery_detail_id = jsptl.delivery_detail_id AND
840: 2) if other than CT3 type of excise exemption is chosen in the sales order
841: both quantity and amount registers get hit.
842:
843: 3) Please note that in cases where amount registers are not hit because of CT3 excise exemption, the
844: register column in the JAI_OM_WSH_LINES_ALL table will show NULL. this is
845: also the change which i have incorporated.
846:
847: Dependency Due to This Bug:-
848: None
897:
898: Fix:-
899: =====
900: The fix done is that instead of setting the v_reg_type to null when a 'CT3' exemption is found and later
901: updating this variable into JAI_OM_WSH_LINES_ALL.register, handled this condition though a decode
902: statement, instead of directly updating the v_reg_type variable.
903: 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'.
904: This has rectified the problem.
905:
921: =====
922: The fix done is that instead of condition - NVL(wdd.inv_interfaced_flag,'N') = 'N'
923: The new condition is used - NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
924: This has rectified the problem.
925: While updating the JAI_OM_WSH_LINES_ALL, new columns are added -
926: last_update_date, last_updated_by, last_update_login, as these were not previously updated
927:
928: Dependency Due to This Bug:-
929: None
1011: The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
1012: as 'WSH' from jai_om_wsh.plb procedure Shipment.
1013: Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
1014: 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
1015: exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
1016: becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
1017:
1018: Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
1019: This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
1061: ENH: Handling Secondary and Higher Education Cess
1062: Added the new cess types jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess
1063:
1064: 54. 28/05/2007 CSahoo for bug#6077065, File version 120.10
1065: Issue: The excise invoice num and excise_invoice_date was not getting updated in the JAI_OM_WSH_LINES_ALL table.
1066: Fix:added the following line in the code lv_exc_inv_gen_for_dlry_flag := 'Y'.
1067:
1068:
1069: 55. 05/07/2007 kunkumar for Bug#6121833 File version 120.11
1118: Description: RG1 REGISTER ENTRY FORM SHOWING THE ASSESSABLE VALUE IN FOREIGN CURRENCY
1119: Fix : removed the v_converted_rate which is multiplying with ln_total_of_base_tax in a call jai_om_rg_pkg.ja_in_rg_i_entry
1120:
1121: 64. 19-FEB-2013 nkodakan for the bug 16353242
1122: Description : TIME STAMP FOR EXCISE INVOICE IS NOT COMING IN JAI_OM_WSH_LINES_ALL TABLE
1123: Fix : Removed the TRUNC function for v_actual_shipment_date
1124:
1125: 65. 05-Mar-2013 mmurtuza for bug 16308603
1126: Description: INDIA EXCISE INVOICE GENERATION PROGRAM ,PICKS ALL DELIVERIES ACROSS OU
1811: and lv_exc_inv_gen_for_dlry_flag = 'Y'
1812: then
1813:
1814: UPDATE
1815: JAI_OM_WSH_LINES_ALL
1816: SET
1817: excise_invoice_no = v_exc_invoice_no,
1818: --excise_invoice_date = TRUNC(v_actual_shipment_date), --replaced v_date for bug#7354983
1819: -- commented the above and added below for the bug 16353242
2008: END IF;
2009: -- Start of Bug 3446362
2010: /*
2011: Added by aiyer for the bug 3446362.
2012: Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
2013: Even records with null values are fine.
2014: If the count of rows with CT3 excise_exemption_type is greater than zero meaning
2015: some records are present which do not have CT3 excise exemption, then only in such a scenario let the
2016: amount registers be hit.
3036:
3037: ELSE
3038: FOR temp_rec IN ( Select distinct joogtt.delivery_id
3039: From JAI_OM_OE_GEN_TAXINV_T joogtt,
3040: jai_om_wsh_lines_all jowla
3041: where joogtt.delivery_id = jowla.delivery_id
3042: and mo_global.check_access(jowla.org_id) = 'Y'
3043: ) /*Added join of table jai_om_wsh_lines_all and check_access by mmurtuza for bug 16308603*/
3044: LOOP
3039: From JAI_OM_OE_GEN_TAXINV_T joogtt,
3040: jai_om_wsh_lines_all jowla
3041: where joogtt.delivery_id = jowla.delivery_id
3042: and mo_global.check_access(jowla.org_id) = 'Y'
3043: ) /*Added join of table jai_om_wsh_lines_all and check_access by mmurtuza for bug 16308603*/
3044: LOOP
3045: BEGIN
3046: fnd_file.put_line ( fnd_file.log , ' Calling jai_om_wsh_pkg.process_delivery with delivery id => ' || temp_rec.delivery_id );
3047: jai_om_wsh_pkg.process_delivery(errbuf ,