ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EE993869E053B46B63588994Y SELECT pmt_sch.trx_number, pmt_sch.trx_type, ari_utilities.get_lookup_meaning('INV/CM/ADJ', pmt_sch.trx_type) trx_type_meaning, pmt_sch.TERMS_SEQUENCE_NUMBER, pmt_sch.trx_date, to_char(pmt_sch.trx_date, 'YYYY-MM-DD') StrTrxDate, pmt_sch.due_date, to_char(pmt_sch.due_date, 'YYYY-MM-DD') StrDueDate, pmt_sch.AR_LOOKUP_CODE_STATUS_MEANING, pmt_sch.ct_purchase_order, pmt_sch.INVOICE_CURRENCY_CODE, pmt_sch.amount_due_original, to_char(pmt_sch.amount_due_original, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Amount_Due_Original_Formatted, pmt_sch.AMOUNT_DUE_REMAINING, to_char(pmt_sch.AMOUNT_DUE_REMAINING, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Amount_due_remaining_Formatted, pmt_sch.AMOUNT_IN_DISPUTE, to_char(pmt_sch.AMOUNT_IN_DISPUTE, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Amount_in_dispute_Formatted, NULL AS disc_amt, NULL AS Disc_amt_Formatted, NULL AS discount_last_date, /* pmt_sch.disc_amt, to_char(pmt_sch.disc_amt, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Disc_amt_Formatted, decode(pmt_sch.disc_amt,0,null,to_char(pmt_sch.discount_last_date, 'YYYY-MM-DD')) discount_last_date, */ pmt_sch.customer_number, pmt_sch.customer_name, pmt_sch.bill_to_location, pmt_sch.amount_in_claim, to_char(pmt_sch.amount_in_claim, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Amount_in_Claim_Formatted, pmt_sch.ON_ACCOUNT_AMOUNT, to_char(pmt_sch.ON_ACCOUNT_AMOUNT, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30)) AS Amount_on_Account_Formatted, customerContact, sales_orders FROM ( SELECT ct.PURCHASE_ORDER AS CT_PURCHASE_ORDER, ArPaymentSchedulesV.TRX_NUMBER trx_number, ArPaymentSchedulesV.TRX_DATE trx_date, ArPaymentSchedulesV.AMOUNT_DUE_ORIGINAL amount_due_original, ArPaymentSchedulesV.CUSTOMER_ID, ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID, ArPaymentSchedulesV.class as trx_type, ArPaymentSchedulesV.TERMS_SEQUENCE_NUMBER, /* DECODE(ArPaymentSchedulesV.CLASS, 'PMT', ( select -sum(app.amount_applied) from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app where ps.customer_id = ArPaymentSchedulesV.customer_id AND decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID), NULL, nvl(ps.customer_site_use_id,-10), ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID) = nvl(ps.customer_site_use_id,-10) AND ps.invoice_currency_code = ArPaymentSchedulesV.INVOICE_CURRENCY_CODE AND app.cash_receipt_id = ps.cash_receipt_id AND nvl( app.confirmed_flag, 'Y' ) = 'Y' AND app.status = 'UNAPP' AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID ), ArPaymentSchedulesV.AMOUNT_DUE_REMAINING ) AS AMOUNT_DUE_REMAINING, */ ArPaymentSchedulesV.AMOUNT_DUE_REMAINING AS AMOUNT_DUE_REMAINING, (select case when to_char(:TemplateType)='ARICUSTMTCNTCT' then (select RTRIM ( XMLAGG ( XMLELEMENT ( E, XMLATTRIBUTES (sales_order|| ',' AS "Seg") ) ORDER BY sales_order ASC ).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'), ',' ) from ra_customer_trx_lines ral where ral.customer_trx_id = ct.customer_trx_id ) else (select null from dual) end from dual)AS sales_orders, (select case when to_char(:TemplateType)='ARICUSTMTCNTCT' then (SELECT party.person_first_name ||' '|| party.person_last_name customerContact FROM hz_cust_account_roles acct_role, hz_relationships rel, -- ra_customer_trx_all raall, hz_parties party WHERE acct_role.cust_account_role_id = ct.BILL_TO_CONTACT_ID AND acct_role.ROLE_TYPE = 'CONTACT' AND acct_role.party_id = rel.party_id AND rel.subject_id = party.party_id AND rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND rel.DIRECTIONAL_FLAG = 'F') else (select null from dual) end from dual)AS customerContact, NULL as disc_amt, NULL discount_last_date, /* DECODE(ArPaymentSchedulesV.CLASS,'PMT', 0, nvl(to_number(AR_IREC_PAYMENTS.get_discount_wrapper(ArPaymentSchedulesV.PAYMENT_SCHEDULE_ID, ArPaymentSchedulesV.AMOUNT_DUE_REMAINING)),0)) as disc_amt, ( select min( DECODE(TLD.DISCOUNT_DAYS ,NULL ,NVL(TLD.DISCOUNT_DATE, DECODE ( LEAST(TO_NUMBER(TO_CHAR(PS.TRX_DATE,'DD')), NVL(T.DUE_CUTOFF_DAY,32)) ,T.DUE_CUTOFF_DAY,LAST_DAY( ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD) ) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD+1)),'DD'))) , LAST_DAY(ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD-1)) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH ,TO_NUMBER(TO_CHAR(LAST_DAY( ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD)),'DD'))) ) ) , PS.TRX_DATE + TLD.DISCOUNT_DAYS) + TO_NUMBER(DECODE(AR_IREC_PAYMENTS.is_grace_days_enabled_wrapper,'Y', nvl(CUST_SITE_LEVEL.discount_grace_days, nvl(CUST_ACCT_LEVEL.discount_grace_days,0) ), 0))) last_date from AR_PAYMENT_SCHEDULES ps, RA_TERMS_LINES_DISCOUNTS TLD, RA_TERMS T, HZ_CUSTOMER_PROFILES CUST_SITE_LEVEL, HZ_CUSTOMER_PROFILES CUST_ACCT_LEVEL where ArPaymentSchedulesV.payment_schedule_id = ps.payment_schedule_id AND cust_site_level.cust_account_id(+) = ps.customer_id AND cust_site_level.site_use_id(+) = ps.customer_site_use_id AND cust_acct_level.cust_account_id = ps.customer_id AND cust_acct_level.site_use_id IS NULL AND t.term_id = ps.term_id AND tld.term_id = t.term_id AND tld.sequence_num = ps.terms_sequence_number and trunc( DECODE(TLD.DISCOUNT_DAYS ,NULL ,NVL(TLD.DISCOUNT_DATE, DECODE ( LEAST(TO_NUMBER(TO_CHAR(PS.TRX_DATE,'DD')), NVL(T.DUE_CUTOFF_DAY,32)) ,T.DUE_CUTOFF_DAY,LAST_DAY( ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD) ) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD+1)),'DD'))) , LAST_DAY(ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD-1)) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH ,TO_NUMBER(TO_CHAR(LAST_DAY( ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD)),'DD'))) ) ) , PS.TRX_DATE + TLD.DISCOUNT_DAYS) + TO_NUMBER(DECODE(AR_IREC_PAYMENTS.is_grace_days_enabled_wrapper,'Y', nvl(CUST_SITE_LEVEL.discount_grace_days, nvl(CUST_ACCT_LEVEL.discount_grace_days,0) ), 0)))>=trunc(sysdate) ) as discount_last_date, */ ArPaymentSchedulesV.PAYMENT_SCHEDULE_ID, ArPaymentSchedulesV.DUE_DATE, (SELECT nvl ((SELECT fnd_message.get_string('AR', 'ARI_STATUS_OVERDUE') FROM dual WHERE ( trunc(sysdate) > ArPaymentSchedulesV.due_date ) AND ArPaymentSchedulesV.CLASS <> 'PMT'AND ArPaymentSchedulesV.STATUS = 'OP'), ARI_UTILITIES.get_lookup_meaning('PAYMENT_SCHEDULE_STATUS', ArPaymentSchedulesV.STATUS)) FROM DUAL ) as AR_LOOKUP_CODE_STATUS_MEANING, ArPaymentSchedulesV.AMOUNT_IN_DISPUTE, acct.account_number customer_number, acct.account_name customer_name, billto_uses.Location bill_to_location, shipto_uses.Location ship_to_location, ArPaymentSchedulesV.CASH_RECEIPT_ID, ARI_UTILITIES.get_lookup_meaning('INV/CM/ADJ', ArPaymentSchedulesV.class) as ACCT_DTLS_VIEW_TRX_TYPE, ArPaymentSchedulesV.INVOICE_CURRENCY_CODE, DECODE(ArPaymentSchedulesV.CLASS, 'PMT', (select -sum(app.amount_applied) from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app where ps.customer_id = ArPaymentSchedulesV.customer_id AND decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID), NULL, nvl(ps.customer_site_use_id,-10), ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID) = nvl(ps.customer_site_use_id,-10) AND ps.invoice_currency_code = ArPaymentSchedulesV.INVOICE_CURRENCY_CODE AND app.cash_receipt_id = ps.cash_receipt_id and app.applied_payment_schedule_id = -4 AND nvl( app.confirmed_flag, 'Y' ) = 'Y' AND app.status = 'OTHER ACC' AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID), null) AS amount_in_claim, DECODE(ArPaymentSchedulesV.CLASS, 'PMT', ( select -sum(app.amount_applied) from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app where ps.customer_id = ArPaymentSchedulesV.customer_id AND decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID), NULL, nvl(ps.customer_site_use_id,-10), ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID) = nvl(ps.customer_site_use_id,-10) AND ps.invoice_currency_code = ArPaymentSchedulesV.INVOICE_CURRENCY_CODE AND app.cash_receipt_id = ps.cash_receipt_id AND nvl( app.confirmed_flag, 'Y' ) = 'Y' AND app.status = 'ACC' AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID ),null) AS ON_ACCOUNT_AMOUNT FROM AR_PAYMENT_SCHEDULES ArPaymentSchedulesV, ra_customer_trx ct, hz_cust_accounts acct,ra_terms t,ar_lookups al, ar_irec_user_acct_sites_all auasa, hz_cust_site_uses billto_uses, hz_cust_site_uses shipto_uses WHERE ArPaymentSchedulesV.customer_trx_id = ct.customer_trx_id(+) AND ArPaymentSchedulesV.STATUS = 'OP' and billto_uses.site_use_id (+)= ct.bill_to_site_use_id and shipto_uses.site_use_id (+)=ct.ship_to_site_use_id and shipto_uses.site_use_code(+)='SHIP_TO' AND acct.cust_account_id = ArPaymentSchedulesV.customer_id AND ArPaymentSchedulesV.customer_id = auasa.customer_id AND (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID = auasa.CUSTOMER_SITE_USE_ID OR (auasa.CUSTOMER_SITE_USE_ID = -1 AND ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID IS NULL)) AND ArPaymentSchedulesV.customer_id =:customerId AND ArPaymentSchedulesV.INVOICE_CURRENCY_CODE =:CurrencyCode AND auasa.user_id = FND_GLOBAL.USER_ID AND auasa.session_id =:sessionId AND ct.term_id = t.term_id(+) AND al.lookup_type='ARI_PMT_APPROVAL_STATUS' and al.lookup_code=nvl(ArPaymentSchedulesV.PAYMENT_APPROVAL,'PENDING') AND(TRUNC(ArPaymentSchedulesV.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedulesV.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER')))) AND (ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) OR ArPaymentSchedulesV.CLASS='PMT') )pmt_sch AR ARI_CUST_STMT AR AR تعريف بيانات كشوف حسابات العملاء في المقبوضات على الإنترنت Application: المقبوضات Source: Short Name: ARI_CUST_STMT DB package: D AR Kundenportal - Kundenauszug - Datendefinition Application: Debitoren Source: Short Name: ARI_CUST_STMT DB package: F AR iReceivables - Définition de données de relevé de client Application: Receivables Source: Short Name: ARI_CUST_STMT DB package: US AR iReceivables Customers Statement Data Definition Application: Receivables Source: Short Name: ARI_CUST_STMT DB package: ZHS AR iReceivables 客户对帐单数据定义 Application: 应收帐款 Source: Short Name: ARI_CUST_STMT DB package: BI Publisher :currencycode :customerid :sessionid :templatetype