ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0D33869E053B46B63588994 SELECT B.TRX_NUMBER, A.TRX_DATE, a.supp_inv_type, d.total_amount Amount, C.PARTY_NUMBER CUSTOMER_NUMBER, C.PARTY_NAME CUSTOMER_NAME, loc.location, DECODE(SUPP_INV_TYPE,'DB','Debit Memo','CR','Credit Memo','SI','Supplementary Invoice'), A.CUSTOMER_TRX_ID, ( (NVL(NEW_NET_SALES_AMT,0) -NVL( EXIST_NET_SALES_AMT,0)) + (NVL(NEW_EXCISE_AMT,0) - NVL(exist_EXCISE_AMT,0)) + (NVL(NEW_OTHER_AMT,0) - NVL(exist_OTHER_AMT,0))) Total, (NVL(NEW_NET_SALES_AMT,0) - NVL(EXIST_NET_SALES_AMT,0)) New_Sales, JA_JAINSITR_XMLP_PKG.serial_fformula() serial_f, JA_JAINSITR_XMLP_PKG.Serial_Number_p Serial_Number, JA_JAINSITR_XMLP_PKG.excise_taxformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Excise, JA_JAINSITR_XMLP_PKG.cf_5formula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Sales, JA_JAINSITR_XMLP_PKG.cstformula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Cst, JA_JAINSITR_XMLP_PKG.others_formula(A.CUSTOMER_TRX_ID, a.supp_inv_type) Others FROM JAI_AR_SUP_HDRS_ALL A, RA_CUSTOMER_TRX_ALL B, HZ_PARTIES C, HZ_CUST_ACCOUNTS HZCA, JAI_AR_TRXS d, HZ_CUST_SITE_USES_ALL loc WHERE B.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID AND C.PARTY_ID = HZCA.PARTY_ID AND HZCA.CUST_ACCOUNT_ID = A.CUSTOMER_ID and HZCA.CUST_ACCOUNT_ID = NVL(:CUSTOMERS,HZCA.CUST_ACCOUNT_ID ) and a.customer_site = loc.site_use_id AND trunc(A.PROCESS_DATE) =trunc( NVL(:PROCESS_DATE,A.PROCESS_DATE)) AND A.SUPP_INV_TYPE = NVL(decode(:SUPPLEMENTARY_TYPE,'Debit Memo','DB','Credit Memo','CR','Supplementary Inv','SI'),A.SUPP_INV_TYPE) AND B.TRX_NUMBER = NVL(:INVOICE_NO,B.TRX_NUMBER) and d.customer_trx_id = b.customer_trx_id and a.customer_site = nvl(:site_use_id,a.customer_site) --order by c.party_name ASC, a.trx_Date DESC --ORDER BY 5 ASC,6 ASC,7 ASC,2 DESC,1 ASC,4 ASC , c.party_name ASC , a.trx_Date DESC order by 5,6,7,2 desc, 1,4,8,11,10,9,3 JA JAINSITR_XML US JA India - Supplementary Transaction Details - draft Application: Asia/Pacific Localizations Source: India - Supplementary Transaction Details Report (XML) - Not Supported: Reserved For Future Use Short Name: JAINSITR_XML DB package: JA_JAINSITR_XMLP_PKG ZHS JA 印度 - 补充事务处理明细报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - 补充事务处理明细报表 (XML) - 不支持：已保留供将来使用 Short Name: JAINSITR_XML DB package: JA_JAINSITR_XMLP_PKG BI Publisher :customers :invoice_no :p_conc_request_id :process_date :serial_number :site_use_id :supplementary_type 1 10 :customers LOV Oracle JA_IN_Supp_Customer_Names Y select hzca.cust_account_id id, hzp.party_name value, null description from hz_parties hzp,hz_cust_accounts hzca where hzp.party_id=hzca.party_id order by value,description US Customer ZHS 客户 2 20 :site_use_id LOV Oracle JA_IN_Supplementary_Sites Y select hzcsu.site_use_id id, hzcsu.location value, null description from hz_cust_site_uses_all hzcsu where hzcsu.cust_acct_site_id in (select hzl.location_id from hz_cust_acct_sites_all hzcas, hz_party_sites hzps, hz_locations hzl where hzl.location_id=hzps.location_id and hzps.party_site_id=hzcas.party_site_id and hzcas.cust_account_id=:$flex$.ja_in_supp_customer_names) order by value,description US Customer Site ZHS 客户地点 3 30 :invoice_no LOV Oracle JA_IN_Invoice_No Y select trx_number id, trx_number value, null description from ra_customer_trx_all order by value,description US Invoice NO ZHS 发票编号 4 40 :process_date LOV Oracle JA_IN_Supplementary_Process_Date Y select process_date id, process_date value, null description from jai_ar_sup_hdrs_all where rowid in (select max(rowid) from jai_ar_sup_hdrs_all group by trunc(process_date)) order by value,description US Processed Date ZHS 处理日期 5 50 :supplementary_type LOV Oracle JA_IN_Supplementary_Type Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=1021178 order by ffvv.flex_value US Supplementary Type ZHS 补充类型 :$flex$.ja_in_supp_customer_names Customer Customer Site