[Home] [Help]
525: --
526: -- PARAMETERS:
527: -- In: pn_shipment_line_id NUMBER tax identifier
528: -- pn_transaction_id NUMBER transaction identifier
529: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
530: -- DESIGN REFERENCES:
531: -- JAI_Retroprice_TDD.doc
532: --
533: -- CHANGE HISTORY:
568: , nvl(jtc.rounding_factor, 0) rnd
569: FROM
570: jai_retro_tax_changes jrtc
571: , jai_cmn_taxes_all jtc
572: , jai_retro_line_changes jrlc
573: WHERE jrlc.doc_line_id = pn_shipment_line_id
574: AND jrtc.line_change_id = jrlc.line_change_id
575: AND jrlc.doc_type = 'RECEIPT'
576: AND jtc.tax_id = jrtc.tax_id
1393: -- xv_vat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1394: -- pn_cenvat_amount NUMBER recoverable vat tax amount
1395: -- xv_cenvat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1396: -- pn_version_number NUMBER receipt version number
1397: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1398: -- DESIGN REFERENCES:
1399: -- JAI_Retroprice_TDD.doc
1400: --
1401: -- CHANGE HISTORY:
1663: END IF; --l_proc_level>=l_dbg_level
1664:
1665: IF xv_cenvat_action = 'UNCLAIMED'
1666: THEN
1667: --update jai_retro_line_changes to modify excise_action to 'UNCLAIM'
1668: UPDATE
1669: Jai_Retro_Line_Changes
1670: SET
1671: Excise_Action = 'UNCLAIM'
1665: IF xv_cenvat_action = 'UNCLAIMED'
1666: THEN
1667: --update jai_retro_line_changes to modify excise_action to 'UNCLAIM'
1668: UPDATE
1669: Jai_Retro_Line_Changes
1670: SET
1671: Excise_Action = 'UNCLAIM'
1672: WHERE line_change_id =pn_line_change_id;
1673: END IF;
1678: END IF; --l_proc_level>=l_dbg_level
1679:
1680: IF xv_vat_action = 'UNCLAIMED'
1681: THEN
1682: --update jai_retro_line_changes to modify vat_action to 'UNCLAIM'
1683: UPDATE
1684: Jai_Retro_Line_Changes
1685: SET
1686: Vat_Action = 'UNCLAIM'
1680: IF xv_vat_action = 'UNCLAIMED'
1681: THEN
1682: --update jai_retro_line_changes to modify vat_action to 'UNCLAIM'
1683: UPDATE
1684: Jai_Retro_Line_Changes
1685: SET
1686: Vat_Action = 'UNCLAIM'
1687: WHERE line_change_id = pn_line_change_id;
1688: END IF;
1728: -- pn_vat_amount NUMBER recoverable vat tax amount
1729: -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
1730: -- pd_supp_vat_inv_date DATE supplementary invoice date
1731: -- pn_version_number NUMBER receipt version number
1732: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1733: -- DESIGN REFERENCES:
1734: -- JAI_Retroprice_TDD.doc
1735: --
1736: -- CHANGE HISTORY:
2169: AND claimed_amount IS NOT NULL;
2170:
2171: END LOOP; -- claimed records
2172:
2173: -- UPDATE jai_retro_line_changes to modify vat_action as 'CLAIM' for the current receipt line
2174: UPDATE
2175: jai_retro_line_changes
2176: SET
2177: vat_action = 'CLAIM'
2171: END LOOP; -- claimed records
2172:
2173: -- UPDATE jai_retro_line_changes to modify vat_action as 'CLAIM' for the current receipt line
2174: UPDATE
2175: jai_retro_line_changes
2176: SET
2177: vat_action = 'CLAIM'
2178: WHERE line_change_id = pn_line_change_id;
2179:
2212: -- pn_shipment_line_id NUMBER shipemnt line identifier,
2213: -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
2214: -- pd_supp_vat_inv_date DATE supplementary invoice date
2215: -- pn_version_number NUMBER receipt version number
2216: -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
2217: -- DESIGN REFERENCES:
2218: -- JAI_Retroprice_TDD.doc
2219: --
2220: -- CHANGE HISTORY:
2310: FND_FILE.PUT_LINE(fnd_file.log, 'Total receipt fail to pass cenvat prossing validation,for the reason: '||lv_process_message);
2311:
2312:
2313: UPDATE
2314: jai_retro_line_changes
2315: SET
2316: excise_action ='UNCLAIM'
2317: WHERE
2318: line_change_id = pn_line_change_id;
2510: THEN
2511: raise_application_error(-20120,'CENVAT Claim accounting retruned with error : '||lv_process_message);
2512: END IF;
2513:
2514: --UPDATE jai_retro_line_changes to modify excise_action as 'CLAIM'
2515: UPDATE
2516: jai_retro_line_changes
2517: SET
2518: excise_action = 'CLAIM'
2512: END IF;
2513:
2514: --UPDATE jai_retro_line_changes to modify excise_action as 'CLAIM'
2515: UPDATE
2516: jai_retro_line_changes
2517: SET
2518: excise_action = 'CLAIM'
2519: WHERE line_change_id = pn_line_change_id;
2520:
2621: ln_diff_nonre_tax_amount NUMBER; --added by eric on Apr 10,2008 for bug 6957519/6958938/6968839
2622:
2623: CURSOR get_rcv_transactions_cur
2624: ( pn_line_location_id rcv_transactions.po_line_location_id%TYPE
2625: , pn_line_change_id jai_retro_line_changes.line_change_id%TYPE
2626: )
2627: IS
2628: SELECT
2629: *
2634: AND creation_date >= NVL(ld_from_eff_date,creation_date)-- eric changed according to review comment #36
2635: AND NOT EXISTS ( SELECT
2636: 'X'
2637: FROM
2638: jai_retro_line_changes jrlc
2639: WHERE jrlc.doc_line_id = rt.shipment_line_id
2640: AND jrlc.source_line_change_id = pn_line_change_id
2641: AND jrlc.doc_type = 'RECEIPT'
2642: );
2682: , doc_number
2683: , vendor_id
2684: , vendor_site_id
2685: FROM
2686: jai_retro_line_changes jrlc
2687: WHERE vendor_id = NVL(pn_vendor_id,vendor_id)
2688: AND vendor_site_id = NVL(pn_vendor_site_id,vendor_site_id)
2689: AND ( (doc_type = 'RELEASE' AND doc_header_id = NVL(pn_po_header_id ,doc_header_id))
2690: OR (doc_type = 'STANDARD PO' AND from_header_id = NVL(pn_po_header_id ,from_header_id))
2692: AND NVL(receipt_processed_flag,'N') <>jai_constants.yes
2693: --AND creation_date <= NVL(ld_from_eff_date,creation_date) , eric remomved according to review comment #36
2694: AND (original_unit_price < modified_unit_price OR pv_process_downward = jai_constants.yes )
2695: AND doc_version_number =( SELECT MAX(doc_version_number)
2696: FROM jai_retro_line_changes a
2697: WHERE a.line_location_id = jrlc.line_location_id
2698: ) ;
2699: lv_procedure_name VARCHAR2(40):='Process_Retroactive_Update';
2700: ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2845: ln_skip_rcpt_cnt := ln_skip_rcpt_cnt + 1;
2846: ELSE
2847: --get the retro line change id the receipt from a sequence
2848: SELECT
2849: jai_retro_line_changes_s.nextval
2850: INTO
2851: ln_retro_line_changes_id
2852: FROM
2853: DUAL;
2863: NVL(MAX(doc_version_number),0) + 1 /*rchandan. Moved +1 outside NVL*/
2864: INTO
2865: ln_retro_line_changes_version
2866: FROM
2867: jai_retro_line_changes
2868: WHERE doc_header_id = jai_rcv_lines_rec.shipment_header_id
2869: AND doc_line_id = jai_rcv_lines_rec.shipment_line_id
2870: AND doc_type = 'RECEIPT';
2871:
2873: THEN
2874: FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_version : '|| ln_retro_line_changes_version);
2875: END IF; --l_proc_level>=l_dbg_level
2876:
2877: --insert data into jai_retro_line_changes table
2878: INSERT INTO jai_retro_line_changes
2879: ( line_change_id
2880: , doc_type
2881: , doc_header_id
2874: FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_version : '|| ln_retro_line_changes_version);
2875: END IF; --l_proc_level>=l_dbg_level
2876:
2877: --insert data into jai_retro_line_changes table
2878: INSERT INTO jai_retro_line_changes
2879: ( line_change_id
2880: , doc_type
2881: , doc_header_id
2882: , doc_line_id
2906: , last_updated_by
2907: , object_version_number
2908: )
2909: VALUES
2910: ( ln_retro_line_changes_id --=> jai_retro_line_changes_s.nextval
2911: , 'RECEIPT' --=> 'RECEIPT'
2912: , jai_rcv_lines_rec.shipment_header_id --=> shipment_header_id from jai_rcv_lines
2913: , jai_rcv_lines_rec.shipment_line_id --=> shipment_Line_Id from jai_rcv_lines
2914: , NULL --=> NULL
2916: , jai_retro_line_change_rec.line_change_id --=> source_line_change_id ,The line_change_id of latest shipment
2917: , SYSDATE --=> Sysdate
2918: , jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
2919: , jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
2920: , jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
2921: , jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
2922: , 'Y' --=> receipt_processed_flag :'Y'
2923: , pv_cenvat_action --=> pv_cenvat_action
2924: , pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
2917: , SYSDATE --=> Sysdate
2918: , jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
2919: , jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
2920: , jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
2921: , jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
2922: , 'Y' --=> receipt_processed_flag :'Y'
2923: , pv_cenvat_action --=> pv_cenvat_action
2924: , pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
2925: , ld_supp_exc_inv_date --=> pd_supp_exc_inv_date
2939: );
2940:
2941: IF (ln_proc_level >= ln_dbg_level)
2942: THEN
2943: FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_line_changes inserted ');
2944: END IF; --l_proc_level>=l_dbg_level
2945:
2946: FOR jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur (rcv_transactions_rec.transaction_id)
2947: LOOP
2967: , object_version_number
2968: )
2969: VALUES
2970: ( jai_retro_tax_changes_s.nextval --=> jai_retro_tax_changes_s.nextval
2971: , ln_retro_line_changes_id --=> from jai_retro_line_changes
2972: , jai_rcv_line_taxes_rec.tax_line_no --=> from jai_rcv_line_taxes
2973: , jai_rcv_line_taxes_rec.tax_id --=> from jai_rcv_line_taxes
2974: , jai_rcv_line_taxes_rec.tax_name --=> from jai_rcv_line_taxes
2975: , jai_rcv_line_taxes_rec.tax_type --=> from jai_rcv_line_taxes
3215:
3216: IF (ln_skip_rcpt_cnt =0)
3217: THEN
3218: UPDATE
3219: jai_retro_line_changes
3220: SET
3221: receipt_processed_flag = 'Y'
3222: WHERE
3223: line_change_id = jai_retro_line_change_rec.line_change_id;
3325: pv_process_message := '';
3326:
3327: BEGIN
3328: SELECT
3329: JAI_RETRO_LINE_CHANGES_S.NEXTVAL
3330: INTO
3331: ln_retro_line_changes_id
3332: FROM dual;
3333: EXCEPTION
3347: NVL(max(doc_version_number), 0) + 1 /*added max and replaced -1 with 0*/
3348: INTO
3349: ln_revision_num
3350: FROM
3351: JAI_RETRO_LINE_CHANGES jrlc
3352: WHERE jrlc.doc_header_id = pr_old.po_header_id
3353: AND jrlc.doc_line_id = pr_old.po_line_id
3354: AND jrlc.line_location_id = pr_old.line_location_id /*added by rchandan*/
3355: AND jrlc.doc_type IN ('RELEASE', 'STANDARD PO');
3439:
3440: BEGIN
3441: IF pr_old.shipment_type = 'STANDARD'
3442: THEN
3443: INSERT INTO JAI_RETRO_LINE_CHANGES
3444: ( LINE_CHANGE_ID
3445: , DOC_TYPE
3446: , DOC_HEADER_ID
3447: , DOC_LINE_ID
3505: , NULL
3506: );
3507: ELSIF pr_old.shipment_type = 'BLANKET'
3508: THEN
3509: INSERT INTO JAI_RETRO_LINE_CHANGES
3510: ( LINE_CHANGE_ID
3511: , DOC_TYPE
3512: , DOC_HEADER_ID
3513: , DOC_LINE_ID
3701: WHERE tax_line_no = pn_line_no
3702: AND line_change_id = (SELECT
3703: line_change_id
3704: FROM
3705: JAI_RETRO_LINE_CHANGES jrpc
3706: WHERE jrpc.Line_location_id = pn_line_loc_id
3707: AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3708: AND jrpc.Doc_version_number = (SELECT
3709: MAX(Doc_version_number)
3707: AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3708: AND jrpc.Doc_version_number = (SELECT
3709: MAX(Doc_version_number)
3710: FROM
3711: JAI_RETRO_LINE_CHANGES jrpc1
3712: WHERE jrpc1.Line_location_id = pn_line_loc_id
3713: AND jrpc1.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3714: )
3715: );