SELECT trxrefs.invoice_id invoice_id2
,trxrefs.item_id item_id
,trxrefs.organization_id org_id
,TO_NUMBER(NULL) src_doc_id
, sum(trxrecs.credit_amount) tax_amount
,trxrefs.tax_rate st_rate
,sum(trxrefs.recoverable_amount) ST
,'AP' source_type2
,trxrefs.tax_type tax_type
, apa.invoice_amount value
, trxrecs.service_type_code service_type_code,
JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(trxrecs.service_type_code) CF_Service_type,
JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, trxrefs.tax_rate, sum ( trxrefs.recoverable_amount ), apa.invoice_amount) CF_VALUE,
JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), trxrefs.item_id, :SOURCE_TYPE1) CF_DESCRIPTION
FROM
jai_rgm_trx_records trxrecs,
jai_rgm_trx_refs trxrefs,
ap_invoices_all apa
WHERE trxrefs.reference_id = trxrecs.reference_id
AND trxrecs.source in ( 'AP')
AND trxrecs.regime_primary_regno = :p_regm_prmy_regn
AND trxrecs.regime_code = 'SERVICE'
AND trunc(trxrecs.transaction_date) between
trunc(:p_from_date) AND trunc (:p_to_date)
AND trxrefs.invoice_id = :invoice_id1
AND :source_type1 = 'AP'
AND apa.invoice_id = trxrefs.invoice_id
Group By
trxrefs.invoice_id
,trxrefs.item_id
,trxrefs.organization_id
,trunc(trxrefs.creation_date)
,trxrefs.tax_rate
,trxrefs.tax_type
,'AP'
,apa.invoice_amount
,trxrecs.service_type_code
UNION
SELECT
TO_NUMBER(NULL) invoice_id2 ,
TO_NUMBER(NULL) item_id ,
TO_NUMBER(NULL) org_id ,
jrtr.source_document_id src_doc_id ,
sum(jrtr.credit_amount) tax_amount ,
TO_NUMBER(NULL) st_rate ,
TO_NUMBER(NULL) ST ,
'DISTRIBUTION' source_type2 ,
jrtr.tax_type tax_type ,
TO_NUMBER(NULL) value ,
jrtr.service_type_code service_type_code,
JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(jrtr.service_type_code) CF_Service_type,
JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL)) CF_VALUE,
JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), TO_NUMBER(NULL), :SOURCE_TYPE1) CF_DESCRIPTION
FROM
jai_rgm_trx_records jrtr
WHERE
jrtr.source = 'SERVICE_DISTRIBUTE_IN'
AND jrtr.regime_code = 'SERVICE'
AND jrtr.regime_primary_regno = :p_regm_prmy_regn
AND trunc(jrtr.transaction_date) BETWEEN
trunc(:p_from_date) AND trunc (:p_to_date)
and :source_type1 = 'DISTRIBUTION'
and jrtr.source_document_id = :invoice_id1
GROUP BY to_number(null)
, jrtr.transaction_date
,jrtr.source_document_id
, jrtr.tax_type
, 'DISTRIBUTION'
,jrtr.service_type_code
UNION
SELECT
TO_NUMBER(NULL) invoice_id2 ,
TO_NUMBER(NULL) item_id ,
TO_NUMBER(NULL) org_id ,
jrtr.source_document_id src_doc_id ,
sum(jrtr.credit_amount) tax_amount ,
jrtr.tax_rate st_rate ,
TO_NUMBER(NULL) ST ,
'MANUAL' source_type2 ,
jrtr.tax_type tax_type ,
jrtr.assessable_value value ,
jrtr.service_type_code service_type_code,
JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(jrtr.service_type_code) CF_Service_type,
JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, jrtr.tax_rate, TO_NUMBER(NULL), jrtr.assessable_value) CF_VALUE,
JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), TO_NUMBER(NULL), :SOURCE_TYPE1) CF_DESCRIPTION
FROM
jai_rgm_trx_records jrtr ,
JAI_RGM_MANUAL_TRXS jmt ,
po_vendor_sites_all pvsa
WHERE
jrtr.source = 'MANUAL'
AND jrtr.regime_code = 'SERVICE'
AND jrtr.source_document_id = jmt.transaction_number
AND pvsa.vendor_id = jmt.party_id
AND pvsa.vendor_site_id = jmt.party_site_id
AND jrtr.source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
AND jrtr.regime_primary_regno = :p_regm_prmy_regn
AND trunc(jrtr.transaction_date) BETWEEN
trunc(:p_from_date) AND trunc(:p_to_date)
AND :source_type1 = 'MANUAL'
AND jrtr.source_document_id = :invoice_id1
GROUP BY
TO_NUMBER(NULL)
,jrtr.transaction_date
,jrtr.tax_type
,jrtr.assessable_value
,jrtr.source_document_id
,jrtr.tax_rate
,'MANUAL'
,jrtr.service_type_code
ORDER BY 9 desc |
