SELECT
rg23.organization_id ,
rg23.location_id ,
rg23.excise_invoice_no ,
trunc(rg23.excise_invoice_date) excise_invoice_date ,
sum( nvl(rg23.cr_basic_ed,0) -
nvl(rg23.dr_basic_ed,0)
) cenvat ,
sum( nvl(rg23.cr_other_ed,0) -
nvl(rg23.dr_other_ed,0)
) sed ,
sum( nvl(rg23.cr_additional_ed,0) -
nvl(rg23.dr_additional_ed,0)
) additional_duty ,
sum( nvl(rg23.cr_additional_cvd,0) -
nvl(rg23.dr_additional_cvd,0)
) additional_cvd ,
max( nvl(rg23.cr_basic_ed,0) +
nvl(rg23.cr_other_ed,0) +
nvl(rg23.cr_additional_ed,0) +
nvl(rg23.cr_additional_cvd,0)
) max_transaction_amount ,
'AED(TTA)' ,
'AED(GSI)' ,
'OTHER',
max(transaction_source_num) transaction_id,
JA_JAINMCEN_XMLP_PKG.cf_rounded_cenvat_amtformula(rg23.excise_invoice_no, rg23.excise_invoice_date, sum ( nvl ( rg23.cr_basic_ed , 0 ) - nvl ( rg23.dr_basic_ed , 0 ) )) CF_rounded_cenvat_amt,
JA_JAINMCEN_XMLP_PKG.cf_sedformula(sum ( nvl ( rg23.cr_other_ed , 0 ) - nvl ( rg23.dr_other_ed , 0 ) )) CF_sed,
JA_JAINMCEN_XMLP_PKG.cf_addlcvdformula(sum ( nvl ( rg23.cr_additional_cvd , 0 ) - nvl ( rg23.dr_additional_cvd , 0 ) )) CF_addlcvd,
JA_JAINMCEN_XMLP_PKG.cf_addlformula(sum ( nvl ( rg23.cr_additional_ed , 0 ) - nvl ( rg23.dr_additional_ed , 0 ) )) CF_addl,
JA_JAINMCEN_XMLP_PKG.CF_POP_COUNTFORMULA CF_pop_count,
JA_JAINMCEN_XMLP_PKG.CP_sh_edu_p CP_sh_edu,
JA_JAINMCEN_XMLP_PKG.CP_edu_p CP_edu,
JA_JAINMCEN_XMLP_PKG.CP_addlcvd_p CP_addlcvd,
JA_JAINMCEN_XMLP_PKG.CP_addl_p CP_addl,
JA_JAINMCEN_XMLP_PKG.CP_sed_p CP_sed
FROM
JAI_CMN_RG_23AC_II_TRXS rg23
WHERE
rg23.register_type = :p_register_type AND
(rg23.opening_balance <= rg23.closing_balance
OR exists (select 1 from rcv_transactions aa
where (aa.transaction_id = rg23.receipt_ref OR to_char(aa.transaction_id) = rg23.reference_num)
and ( aa.transaction_type IN ('RECEIVE' , 'DELIVER', 'RETURN TO RECEIVING')
OR (aa.transaction_type ='CORRECT' and exists (select 1 from rcv_transactions where transaction_id = aa.parent_transaction_id
and transaction_type IN ('RECEIVE' , 'DELIVER', 'RETURN TO RECEIVING'))
)
)
)
) AND
rg23.organization_id = :p_organization_id AND
rg23.location_id = :p_location_id AND
to_date(trunc(rg23.creation_date),'DD-MON-YY') BETWEEN nvl(:p_trn_from_date,to_date(trunc(rg23.creation_date),'DD-MON-YY')) AND
nvl(:p_trn_to_date , to_date(trunc(sysdate),'DD-MON-YY')) AND
inventory_item_id <> 0 AND
nvl(rounding_id,9999) <> -1
GROUP BY
rg23.organization_id ,
rg23.location_id ,
rg23.excise_invoice_no ,
rg23.excise_invoice_date ,
'AED(TTA)' ,
'AED(GSI)' ,
'OTHER'
ORDER BY
rg23.excise_invoice_no ,
rg23.excise_invoice_date |