SELECT
hdr.party_id ,
hdr.party_site_id ,
hdr.org_id ,
hdr.form_type ,
dtl.header_id ,
dtl.tax_id ,
dtl.tax_line_no ,
dtl.invoice_id ,
dtl.tax_target_amount ,
dtl.order_flag ,
nvl(to_char(dtl.order_number),'N/A') ORDER_NUMBER ,
dtl.trx_number ,
dtl.organization_id ,
dtl.location_id ,
jtc.tax_rate ,
jtc.tax_type ,
ja_hr_org. st_reg_no ,
ja_hr_org.cst_reg_no ,
loc.address_line_1 || ',' || loc.address_line_2 || ',' || loc.address_line_3 LOC_ADDRESS ,
loc.country COUNTRY ,
j_ra_ctl.excise_invoice_no ,
j_ra_ctl.excise_invoice_date ,
hzca.account_number CUSTOMER_NUM ,
hzca.cust_account_id CUSTOMER_ID ,
hzca.account_name CUSTOMER_NAME ,
jcuad.st_reg_no CUST_ST_REG_NO,
jcuad.cst_reg_no CUST_CST_REG_NO ,
jcttl.tax_amount TAX_AMOUNT ,
hr_org.name ORGN_NAME ,
rac.trx_date INVOICE_DATE ,
j_ra_ctl.inventory_item_id ITEM_ID ,
mtl.segment1 ITEM_NAME ,
hzcsu.location PARTY_SITE_NAME ,
match.form_id FORM_ID ,
dtl.matched_amount MATCHED_AMOUNT ,
match.matched_amount FORM_MATCHED_AMOUNT,
JA_JAINSTCR_XMLP_PKG.cf_order_dateformula(dtl.header_id, dtl.order_flag) CF_ORDER_DATE,
JA_JAINSTCR_XMLP_PKG.cf_invoice_amountformula(dtl.invoice_id) CF_Invoice_amount,
JA_JAINSTCR_XMLP_PKG.cf_form_numberformula(match.form_id) cf_form_number,
JA_JAINSTCR_XMLP_PKG.cf_form_dateformula(match.form_id) cf_form_date
FROM
JAI_CMN_STFORM_HDRS_ALL HDR ,
JAI_CMN_ST_FORM_DTLS DTL ,
JAI_CMN_TAXES_ALL JTC ,
JAI_CMN_INVENTORY_ORGS JA_HR_ORG,
HR_ORGANIZATION_UNITS HR_ORG ,
HR_LOCATIONS LOC ,
JAI_AR_TRX_LINES J_RA_CTL ,
HZ_PARTIES HZP ,
HZ_PARTY_SITES HZPS ,
HZ_CUST_ACCOUNTS HZCA ,
HZ_CUST_ACCT_SITES_ALL HZCAS ,
HZ_CUST_SITE_USES_ALL HZCSU ,
JAI_CMN_CUS_ADDRESSES JCUAD ,
JAI_AR_TRX_TAX_LINES JCTTL ,
RA_CUSTOMER_TRX_ALL RAC ,
MTL_SYSTEM_ITEMS MTL ,
JAI_CMN_ST_MATCH_DTLS MATCH
WHERE hzp.party_id = hzca.party_id
AND hzps.party_id = hzp.party_id
AND hzps.party_site_id = hzcas.party_site_id
AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzca.cust_account_id = hzcas.cust_account_id
AND jcuad.customer_id = hzca.cust_account_id
AND jcuad.address_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = hdr.party_site_id
AND hdr.st_hdr_id = dtl.st_hdr_id
AND jtc.tax_id=dtl.tax_id
AND dtl.issue_receipt_flag = 'R'
AND hdr.party_type_flag = 'C'
AND hdr.party_id = jcuad.customer_id
AND dtl.invoice_id= j_ra_ctl.customer_trx_id
AND j_ra_ctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id
AND j_ra_ctl.customer_trx_line_id = dtl.line_id
AND jtc.tax_id = jcttl.tax_id
AND hr_org.organization_id =ja_hr_org.organization_id
AND loc.location_id = ja_hr_org.location_id
AND hr_org.organization_id = dtl.organization_id
AND loc.location_id = dtl.location_id
AND rac.customer_trx_id = j_ra_ctl.customer_trx_id
AND mtl.inventory_item_id = j_ra_ctl.inventory_item_id
AND mtl.organization_id = dtl.organization_id
AND match.st_dtl_id (+)= dtl.st_dtl_id
AND trunc(rac.trx_date) between :p_from_date and :p_to_date
&p_where_clause
ORDER BY 13 ASC,14 ASC,19 ASC,20 ASC,17 ASC,18 ASC,29 ASC,3 ASC,23 ASC,24 ASC,25 ASC,26 ASC,27 ASC,33 ASC,2 ASC,1 ASC,5 ASC,10 ASC,11 ASC,12 ASC,8 ASC,30 ASC,21 ASC,22 ASC,32 ASC,4 ASC,31 ASC,35 ASC,6 ASC,7 ASC,9 ASC,15 ASC,16 ASC,28 ASC , hr_org.name , hzca.cust_account_id , jcuad.st_reg_no , hdr.form_type , rac.trx_date |
