[Home] [Help]
17: (
18: CashReceiptId ar_cash_receipts.cash_receipt_id%TYPE,
19: ReceiptNumber ar_cash_receipts.receipt_number%TYPE,
20: PayFromCustomer ar_cash_receipts.pay_from_customer%TYPE,
21: CustomerNumber hz_cust_accounts.account_number%TYPE, -- Bug 3902175
22: CurrencyCode ar_cash_receipts.currency_code%TYPE,
23: ExchangeRate NUMBER
24: );
25: --
1168: cr.cash_receipt_id CashReceiptId,
1169: cr.receipt_number ReceiptNumber,
1170: cr.pay_from_customer PayFromCustomer,
1171: decode(ra.status,'UNID',null,
1172: hz_cust_accounts.account_number ) CustomerNumber, -- Bug 3902175
1173: cr.currency_code CurrencyCode,
1174: ra.receivable_application_id ReceivableApplicationId,
1175: ra.gl_date GlDate,
1176: ra.apply_date TrxDate,
1183: FROM ar_receivable_applications ra,
1184: igi_ar_rec_applications igira,
1185: ar_cash_receipts cr,
1186: hz_parties, -- Bug 3902175
1187: hz_cust_accounts, -- Bug 3902175
1188: ar_lookups l_cat,
1189: xla_ae_headers xah
1190: WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
1191: AND p_Report.GLDateTo
1194: AND nvl(ra.confirmed_flag,'Y') = 'Y'
1195: AND ra.status <> 'APP'
1196: AND cr.cash_receipt_id = ra.cash_receipt_id
1197: AND (
1198: (cr.pay_from_customer = hz_cust_accounts.cust_account_id -- bug 3902175
1199: AND ra.status <> 'UNID')
1200: OR
1201: ra.status = 'UNID'
1202: )
1199: AND ra.status <> 'UNID')
1200: OR
1201: ra.status = 'UNID'
1202: )
1203: AND hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
1204: AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1205: AND l_cat.lookup_code = decode(ra.amount_applied_from,
1206: null,'TRADE_APP','CCURR_APP')
1207: AND cr.currency_code = DECODE( p_Report.Currency,
1414: DECODE(
1415: ra.application_type,
1416: 'CM', ctcm.bill_to_customer_id,
1417: 'CASH',cr.pay_from_customer ) PayFromCustomer,
1418: hz_cust_accounts.account_number CustomerNumber, -- Bug 3902175
1419: ct.invoice_currency_code CurrencyCode,
1420: DECODE(
1421: ra.application_type,
1422: 'CM', NVL(ctcm.exchange_rate,1),
1478: ra_customer_trx ctcm,
1479: ar_lookups l,
1480: ar_lookups l_cat,
1481: hz_parties, -- Bug 3902175
1482: hz_cust_accounts, -- Bug 3902175
1483: xla_ae_headers xah
1484: WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
1485: AND p_Report.GLDateTo
1486: AND nvl(ra.postable,'Y') = 'Y'
1492: AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1493: AND ctcm.previous_customer_trx_id IS NULL
1494: AND ra.applied_customer_trx_id = ct.customer_trx_id
1495: AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1496: AND hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
1497: AND l.lookup_type = 'AR_CARTESIAN_JOIN'
1498: AND (
1499: ( l.lookup_code ='1' )
1500: OR
1501: ( l.lookup_code = '2'
1502: AND
1503: ra.application_type = 'CM' )
1504: )
1505: AND hz_cust_accounts.cust_account_id = DECODE( ra.application_type, -- Bug 3902175
1506: 'CM', ctcm.bill_to_customer_id,
1507: cr.pay_from_customer )
1508: AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1509: AND l_cat.lookup_code = decode( ra.application_type,
1704: d.source_type SourceType
1705: FROM ar_cash_receipt_history crh,
1706: igi_ar_cash_receipt_hist igicrh,
1707: ar_cash_receipts cr,
1708: (Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
1709: from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
1710: ar_lookups l_cat,
1711: ar_distributions d,
1712: xla_ae_headers xah
1705: FROM ar_cash_receipt_history crh,
1706: igi_ar_cash_receipt_hist igicrh,
1707: ar_cash_receipts cr,
1708: (Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
1709: from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
1710: ar_lookups l_cat,
1711: ar_distributions d,
1712: xla_ae_headers xah
1713: WHERE crh.gl_date BETWEEN p_Report.GlDateFrom