JA India - Cenvat Recovery(On Capital Goods) - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Cenvat Recovery(On Capital Goods) Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINCVAT_XML
DB package: JA_JAINCVAT_XMLP_PKG
SELECT
	 a.reference_num,
	 a.excise_invoice_no,
       a.excise_invoice_date,
       a.vendor_id,
       pov.vendor_name,
       a.vendor_site_id,
       povs.vendor_site_code,
       jpovs.ec_code, 
       rt.quantity,
       rt.unit_of_measure,
       rsh.receipt_num,
       NVL(b.cenvat_amount, 0)  Duty_Payable,
	   NVL(b.other_cenvat_amt,0) Cess_Payable,
       b.cenvat_claimed_ptg,
       NVL(a.cr_basic_ed, 0) + NVL(a.cr_additional_ed, 0) + NVL(a.cr_other_ed, 0) cenvat_credit_taken,
       NVL(a.cr_additional_cvd,0) additional_cvd,   
       a.transaction_date, 
       TO_NUMBER(a.receipt_ref) receipt_id,
       NVL(cenvat_amount, 0) - NVL(cenvat_claimed_amt, 0) balance_credit,
	   NVL(b.other_cenvat_amt,0) - NVL(b.other_cenvat_claimed_amt,0) Balance_Cess,
        a.organization_id,
        a.location_id,
        mtl_sys.description,
        a.slno,
        org.organization_name,
        loc.location_code,
        a.register_id,
	b.transaction_id transaction_id, 
	JA_JAINCVAT_XMLP_PKG.cf_cenvat_credit_takenformula(a.register_id, a.excise_invoice_no, NVL ( a.cr_basic_ed , 0 ) + NVL ( a.cr_additional_ed , 0 ) + NVL ( a.cr_other_ed , 0 ), NVL ( a.cr_additional_cvd , 0 ), NVL ( cenvat_amount , 0 ) - NVL ( cenvat_claimed_amt , 0 )) CF_CENVAT_CREDIT_TAKEN,
	JA_JAINCVAT_XMLP_PKG.cf_percentformula(NVL ( a.cr_basic_ed , 0 ) + NVL ( a.cr_additional_ed , 0 ) + NVL ( a.cr_other_ed , 0 ), NVL ( b.cenvat_amount , 0 )) CF_percent,
	JA_JAINCVAT_XMLP_PKG.cf_cessformula(a.register_id, a.excise_invoice_no, NVL ( b.other_cenvat_amt , 0 ) - NVL ( b.other_cenvat_claimed_amt , 0 )) CF_Cess_Credit_Taken,
	JA_JAINCVAT_XMLP_PKG.cf_sh_cess_credit_takenformula(a.register_id, a.excise_invoice_no) CF_sh_cess_credit_taken, 
	JA_JAINCVAT_XMLP_PKG.cf_quantityformula(b.transaction_id, rt.quantity, a.reference_num, NVL ( b.cenvat_amount , 0 ), :CF_CENVAT_CREDIT_TAKEN, JA_JAINCVAT_XMLP_PKG.cf_cessformula(a.register_id, a.excise_invoice_no, NVL ( b.other_cenvat_amt , 0 ) - NVL ( b.other_cenvat_claimed_amt , 0 )), JA_JAINCVAT_XMLP_PKG.cf_sh_cess_credit_takenformula(a.register_id, a.excise_invoice_no)) CF_QUANTITY,
	JA_JAINCVAT_XMLP_PKG.cp_cenvat_credit_taken_p cp_cenvat_credit_taken,
	JA_JAINCVAT_XMLP_PKG.cp_duty_payable_p cp_duty_payable,
	JA_JAINCVAT_XMLP_PKG.CP_Balance_Cess_p CP_Balance_Cess,
	JA_JAINCVAT_XMLP_PKG.CP_Balance_Credit_p CP_Balance_Credit,
	JA_JAINCVAT_XMLP_PKG.CP_balance_sh_cess_p CP_balance_sh_cess,
	JA_JAINCVAT_XMLP_PKG.CP_additional_cvd_p CP_additional_cvd,
	JA_JAINCVAT_XMLP_PKG.cp_cess_payable_p cp_cess_payable,
	JA_JAINCVAT_XMLP_PKG.CP_sh_cess_payable_p CP_sh_cess_payable,
	JA_JAINCVAT_XMLP_PKG.cp_cess_credit_taken_p cp_cess_credit_taken,
	JA_JAINCVAT_XMLP_PKG.CP_sh_cess_credit_taken_p CP_sh_cess_credit_taken
FROM   JAI_CMN_RG_23AC_II_TRXS a, 
       JAI_RCV_CENVAT_CLAIMS b,
       rcv_transactions rt,
       rcv_shipment_headers rsh,
       JAI_INV_ITM_SETUPS c,
       po_vendors pov,
       po_vendor_sites_all povs,
       JAI_CMN_VENDOR_SITES jpovs,
       mtl_system_items mtl_sys,
       org_organization_definitions org,
       hr_locations loc
WHERE  TO_NUMBER(a.receipt_ref) = b.transaction_id
AND    b.transaction_id = rt.transaction_id
AND    rt.shipment_header_id = rsh.shipment_header_id
AND    a.organization_id = c.organization_id
AND    a.inventory_item_id = c.inventory_item_id
AND    a.vendor_id = pov.vendor_id
AND    a.vendor_site_id = povs.vendor_site_id
AND    (povs.org_id = :p_org_id OR povs.org_id IS NULL)
AND    jpovs.vendor_id = pov.vendor_id
AND    jpovs.vendor_site_id = povs.vendor_site_id
AND    mtl_sys.inventory_item_id = a.inventory_item_id
AND    mtl_sys.organization_id = a.organization_id
AND    org.organization_id = a.organization_id
AND    loc.location_id = a.location_id
AND    a.organization_id = NVL(:p_organization_id, a.organization_id)
AND    a.location_id = NVL(:p_location_id, a.location_id)
AND    b.cenvat_claimed_ptg < 100
AND    c.modvat_flag = 'Y'
AND    TRUNC(a.transaction_date) <= (NVL(TRUNC(:p_post_thru_date), TRUNC(a.transaction_date)))  
ORDER BY rsh.receipt_num, a.slno
Parameter NameSQL textValidation
Org ID
 
Number
As Of Date
 
Date
Location
 
LOV Oracle
Organization Name
 
LOV Oracle