JA India - ST Forms Customer - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - ST Forms Customer Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTCR_XML
DB package: JA_JAINSTCR_XMLP_PKG
Source: India - ST Forms Customer Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTCR_XML
DB package: JA_JAINSTCR_XMLP_PKG
Run
JA India - ST Forms Customer - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
From Date |
|
Date | |
To Date |
|
Date | |
Matching Info ? |
|
LOV Oracle | |
Party |
|
LOV Oracle | |
Party Site |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
Location |
|
LOV Oracle |