[Home] [Help]
530: --
531: -- PARAMETERS:
532: -- In: pn_shipment_line_id NUMBER tax identifier
533: -- pn_transaction_id NUMBER transaction identifier
534: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
535: -- DESIGN REFERENCES:
536: -- JAI_Retroprice_TDD.doc
537: --
538: -- CHANGE HISTORY:
573: , nvl(jtc.rounding_factor, 0) rnd
574: FROM
575: jai_retro_tax_changes jrtc
576: , jai_cmn_taxes_all jtc
577: , jai_retro_line_changes jrlc
578: WHERE jrlc.doc_line_id = pn_shipment_line_id
579: AND jrtc.line_change_id = jrlc.line_change_id
580: AND jrlc.doc_type = 'RECEIPT'
581: AND jtc.tax_id = jrtc.tax_id
1398: -- xv_vat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1399: -- pn_cenvat_amount NUMBER recoverable vat tax amount
1400: -- xv_cenvat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1401: -- pn_version_number NUMBER receipt version number
1402: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1403: -- DESIGN REFERENCES:
1404: -- JAI_Retroprice_TDD.doc
1405: --
1406: -- CHANGE HISTORY:
1668: END IF; --l_proc_level>=l_dbg_level
1669:
1670: IF xv_cenvat_action = 'UNCLAIMED'
1671: THEN
1672: --update jai_retro_line_changes to modify excise_action to 'UNCLAIM'
1673: UPDATE
1674: Jai_Retro_Line_Changes
1675: SET
1676: Excise_Action = 'UNCLAIM'
1670: IF xv_cenvat_action = 'UNCLAIMED'
1671: THEN
1672: --update jai_retro_line_changes to modify excise_action to 'UNCLAIM'
1673: UPDATE
1674: Jai_Retro_Line_Changes
1675: SET
1676: Excise_Action = 'UNCLAIM'
1677: WHERE line_change_id =pn_line_change_id;
1678: END IF;
1683: END IF; --l_proc_level>=l_dbg_level
1684:
1685: IF xv_vat_action = 'UNCLAIMED'
1686: THEN
1687: --update jai_retro_line_changes to modify vat_action to 'UNCLAIM'
1688: UPDATE
1689: Jai_Retro_Line_Changes
1690: SET
1691: Vat_Action = 'UNCLAIM'
1685: IF xv_vat_action = 'UNCLAIMED'
1686: THEN
1687: --update jai_retro_line_changes to modify vat_action to 'UNCLAIM'
1688: UPDATE
1689: Jai_Retro_Line_Changes
1690: SET
1691: Vat_Action = 'UNCLAIM'
1692: WHERE line_change_id = pn_line_change_id;
1693: END IF;
1733: -- pn_vat_amount NUMBER recoverable vat tax amount
1734: -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
1735: -- pd_supp_vat_inv_date DATE supplementary invoice date
1736: -- pn_version_number NUMBER receipt version number
1737: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1738: -- DESIGN REFERENCES:
1739: -- JAI_Retroprice_TDD.doc
1740: --
1741: -- CHANGE HISTORY:
2178: AND claimed_amount IS NOT NULL;
2179:
2180: END LOOP; -- claimed records
2181:
2182: -- UPDATE jai_retro_line_changes to modify vat_action as 'CLAIM' for the current receipt line
2183: UPDATE
2184: jai_retro_line_changes
2185: SET
2186: vat_action = 'CLAIM'
2180: END LOOP; -- claimed records
2181:
2182: -- UPDATE jai_retro_line_changes to modify vat_action as 'CLAIM' for the current receipt line
2183: UPDATE
2184: jai_retro_line_changes
2185: SET
2186: vat_action = 'CLAIM'
2187: WHERE line_change_id = pn_line_change_id;
2188:
2221: -- pn_shipment_line_id NUMBER shipemnt line identifier,
2222: -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
2223: -- pd_supp_vat_inv_date DATE supplementary invoice date
2224: -- pn_version_number NUMBER receipt version number
2225: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
2226: -- DESIGN REFERENCES:
2227: -- JAI_Retroprice_TDD.doc
2228: --
2229: -- CHANGE HISTORY:
2319: FND_FILE.PUT_LINE(fnd_file.log, 'Total receipt fail to pass cenvat prossing validation,for the reason: '||lv_process_message);
2320:
2321:
2322: UPDATE
2323: jai_retro_line_changes
2324: SET
2325: excise_action ='UNCLAIM'
2326: WHERE
2327: line_change_id = pn_line_change_id;
2519: THEN
2520: raise_application_error(-20120,'CENVAT Claim accounting retruned with error : '||lv_process_message);
2521: END IF;
2522:
2523: --UPDATE jai_retro_line_changes to modify excise_action as 'CLAIM'
2524: UPDATE
2525: jai_retro_line_changes
2526: SET
2527: excise_action = 'CLAIM'
2521: END IF;
2522:
2523: --UPDATE jai_retro_line_changes to modify excise_action as 'CLAIM'
2524: UPDATE
2525: jai_retro_line_changes
2526: SET
2527: excise_action = 'CLAIM'
2528: WHERE line_change_id = pn_line_change_id;
2529:
2634: ln_diff_nonre_tax_amount NUMBER; --added by eric on Apr 10,2008 for bug 6957519/6958938/6968839
2635:
2636: CURSOR get_rcv_transactions_cur
2637: ( pn_line_location_id rcv_transactions.po_line_location_id%TYPE
2638: , pn_line_change_id jai_retro_line_changes.line_change_id%TYPE
2639: )
2640: IS
2641: SELECT
2642: *
2647: AND creation_date >= NVL(ld_from_eff_date,creation_date)-- eric changed according to review comment #36
2648: AND NOT EXISTS ( SELECT
2649: 'X'
2650: FROM
2651: jai_retro_line_changes jrlc
2652: WHERE jrlc.doc_line_id = rt.shipment_line_id
2653: AND jrlc.source_line_change_id = pn_line_change_id
2654: AND jrlc.doc_type = 'RECEIPT'
2655: );
2695: , doc_number
2696: , vendor_id
2697: , vendor_site_id
2698: FROM
2699: jai_retro_line_changes jrlc
2700: WHERE vendor_id = NVL(pn_vendor_id,vendor_id)
2701: AND vendor_site_id = NVL(pn_vendor_site_id,vendor_site_id)
2702: AND ( (doc_type = 'RELEASE' AND doc_header_id = NVL(pn_po_header_id ,doc_header_id))
2703: OR (doc_type = 'STANDARD PO' AND from_header_id = NVL(pn_po_header_id ,from_header_id))
2705: AND NVL(receipt_processed_flag,'N') <>jai_constants.yes
2706: --AND creation_date <= NVL(ld_from_eff_date,creation_date) , eric remomved according to review comment #36
2707: AND (original_unit_price < modified_unit_price OR pv_process_downward = jai_constants.yes )
2708: AND doc_version_number =( SELECT MAX(doc_version_number)
2709: FROM jai_retro_line_changes a
2710: WHERE a.line_location_id = jrlc.line_location_id
2711: ) ;
2712: lv_procedure_name VARCHAR2(40):='Process_Retroactive_Update';
2713: ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2858: ln_skip_rcpt_cnt := ln_skip_rcpt_cnt + 1;
2859: ELSE
2860: --get the retro line change id the receipt from a sequence
2861: SELECT
2862: jai_retro_line_changes_s.nextval
2863: INTO
2864: ln_retro_line_changes_id
2865: FROM
2866: DUAL;
2876: NVL(MAX(doc_version_number),0) + 1 /*rchandan. Moved +1 outside NVL*/
2877: INTO
2878: ln_retro_line_changes_version
2879: FROM
2880: jai_retro_line_changes
2881: WHERE doc_header_id = jai_rcv_lines_rec.shipment_header_id
2882: AND doc_line_id = jai_rcv_lines_rec.shipment_line_id
2883: AND doc_type = 'RECEIPT';
2884:
2886: THEN
2887: FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_version : '|| ln_retro_line_changes_version);
2888: END IF; --l_proc_level>=l_dbg_level
2889:
2890: --insert data into jai_retro_line_changes table
2891: INSERT INTO jai_retro_line_changes
2892: ( line_change_id
2893: , doc_type
2894: , doc_header_id
2887: FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_version : '|| ln_retro_line_changes_version);
2888: END IF; --l_proc_level>=l_dbg_level
2889:
2890: --insert data into jai_retro_line_changes table
2891: INSERT INTO jai_retro_line_changes
2892: ( line_change_id
2893: , doc_type
2894: , doc_header_id
2895: , doc_line_id
2919: , last_updated_by
2920: , object_version_number
2921: )
2922: VALUES
2923: ( ln_retro_line_changes_id --=> jai_retro_line_changes_s.nextval
2924: , 'RECEIPT' --=> 'RECEIPT'
2925: , jai_rcv_lines_rec.shipment_header_id --=> shipment_header_id from jai_rcv_lines
2926: , jai_rcv_lines_rec.shipment_line_id --=> shipment_Line_Id from jai_rcv_lines
2927: , NULL --=> NULL
2929: , jai_retro_line_change_rec.line_change_id --=> source_line_change_id ,The line_change_id of latest shipment
2930: , SYSDATE --=> Sysdate
2931: , jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
2932: , jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
2933: , jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
2934: , jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
2935: , 'Y' --=> receipt_processed_flag :'Y'
2936: , pv_cenvat_action --=> pv_cenvat_action
2937: , pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
2930: , SYSDATE --=> Sysdate
2931: , jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
2932: , jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
2933: , jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
2934: , jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
2935: , 'Y' --=> receipt_processed_flag :'Y'
2936: , pv_cenvat_action --=> pv_cenvat_action
2937: , pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
2938: , ld_supp_exc_inv_date --=> pd_supp_exc_inv_date
2952: );
2953:
2954: IF (ln_proc_level >= ln_dbg_level)
2955: THEN
2956: FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_line_changes inserted ');
2957: END IF; --l_proc_level>=l_dbg_level
2958:
2959: FOR jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur (rcv_transactions_rec.transaction_id)
2960: LOOP
2980: , object_version_number
2981: )
2982: VALUES
2983: ( jai_retro_tax_changes_s.nextval --=> jai_retro_tax_changes_s.nextval
2984: , ln_retro_line_changes_id --=> from jai_retro_line_changes
2985: , jai_rcv_line_taxes_rec.tax_line_no --=> from jai_rcv_line_taxes
2986: , jai_rcv_line_taxes_rec.tax_id --=> from jai_rcv_line_taxes
2987: , jai_rcv_line_taxes_rec.tax_name --=> from jai_rcv_line_taxes
2988: , jai_rcv_line_taxes_rec.tax_type --=> from jai_rcv_line_taxes
3260:
3261: IF (ln_skip_rcpt_cnt =0)
3262: THEN
3263: UPDATE
3264: jai_retro_line_changes
3265: SET
3266: receipt_processed_flag = 'Y'
3267: WHERE
3268: line_change_id = jai_retro_line_change_rec.line_change_id;
3370: pv_process_message := '';
3371:
3372: BEGIN
3373: SELECT
3374: JAI_RETRO_LINE_CHANGES_S.NEXTVAL
3375: INTO
3376: ln_retro_line_changes_id
3377: FROM dual;
3378: EXCEPTION
3392: NVL(max(doc_version_number), 0) + 1 /*added max and replaced -1 with 0*/
3393: INTO
3394: ln_revision_num
3395: FROM
3396: JAI_RETRO_LINE_CHANGES jrlc
3397: WHERE jrlc.doc_header_id = pr_old.po_header_id
3398: AND jrlc.doc_line_id = pr_old.po_line_id
3399: AND jrlc.line_location_id = pr_old.line_location_id /*added by rchandan*/
3400: AND jrlc.doc_type IN ('RELEASE', 'STANDARD PO');
3484:
3485: BEGIN
3486: IF pr_old.shipment_type = 'STANDARD'
3487: THEN
3488: INSERT INTO JAI_RETRO_LINE_CHANGES
3489: ( LINE_CHANGE_ID
3490: , DOC_TYPE
3491: , DOC_HEADER_ID
3492: , DOC_LINE_ID
3550: , NULL
3551: );
3552: ELSIF pr_old.shipment_type = 'BLANKET'
3553: THEN
3554: INSERT INTO JAI_RETRO_LINE_CHANGES
3555: ( LINE_CHANGE_ID
3556: , DOC_TYPE
3557: , DOC_HEADER_ID
3558: , DOC_LINE_ID
3746: WHERE tax_line_no = pn_line_no
3747: AND line_change_id = (SELECT
3748: line_change_id
3749: FROM
3750: JAI_RETRO_LINE_CHANGES jrpc
3751: WHERE jrpc.Line_location_id = pn_line_loc_id
3752: AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3753: AND jrpc.Doc_version_number = (SELECT
3754: MAX(Doc_version_number)
3752: AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3753: AND jrpc.Doc_version_number = (SELECT
3754: MAX(Doc_version_number)
3755: FROM
3756: JAI_RETRO_LINE_CHANGES jrpc1
3757: WHERE jrpc1.Line_location_id = pn_line_loc_id
3758: AND jrpc1.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3759: )
3760: );