eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AR_ARXCCS_XMLP_PKG dependencies on AR_RECEIVABLE_APPLICATIONS

Line 202: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

198: l_aging_convert_credit:= '';
199: l_aging_in_collection:= 0 ;
200: l_aging_convert_collection:= '';
201:
202: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
203: -AMOUNT_APPLIED, 0)),
204: 0) on_account,
205: nvl(max(decode(ar_receivable_applications.status, 'ACC',
206: decode(ar_cash_receipts.currency_code, functional_currency, ' ',

Line 205: nvl(max(decode(ar_receivable_applications.status, 'ACC',

201:
202: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
203: -AMOUNT_APPLIED, 0)),
204: 0) on_account,
205: nvl(max(decode(ar_receivable_applications.status, 'ACC',
206: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
207: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
208: ' ')), ' ') account_convert,
209: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

Line 209: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

205: nvl(max(decode(ar_receivable_applications.status, 'ACC',
206: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
207: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
208: ' ')), ' ') account_convert,
209: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
210: -amount_applied, 0)),
211: 0) unapplied,
212: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
213: decode(ar_cash_receipts.currency_code, functional_currency, ' ',

Line 212: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',

208: ' ')), ' ') account_convert,
209: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
210: -amount_applied, 0)),
211: 0) unapplied,
212: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
213: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
214: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
215: ' ')), ' ') unapp_convert
216: into l_aging_on_account,

Line 220: from ar_receivable_applications,

216: into l_aging_on_account,
217: l_aging_convert_on_account,
218: l_aging_unapplied,
219: l_aging_convert_unapplied
220: from ar_receivable_applications,
221: ar_cash_receipts
222: where ar_receivable_applications.cash_receipt_id =
223: ar_cash_receipts.cash_receipt_id
224: and ar_cash_receipts.pay_from_customer = customer_id

Line 222: where ar_receivable_applications.cash_receipt_id =

218: l_aging_unapplied,
219: l_aging_convert_unapplied
220: from ar_receivable_applications,
221: ar_cash_receipts
222: where ar_receivable_applications.cash_receipt_id =
223: ar_cash_receipts.cash_receipt_id
224: and ar_cash_receipts.pay_from_customer = customer_id
225: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
226: and ar_cash_receipts.currency_code = Currency_Bucket

Line 227: and ar_receivable_applications.gl_date <= sysdate

223: ar_cash_receipts.cash_receipt_id
224: and ar_cash_receipts.pay_from_customer = customer_id
225: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
226: and ar_cash_receipts.currency_code = Currency_Bucket
227: and ar_receivable_applications.gl_date <= sysdate
228: and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y'
229: ;
230:
231: c_aging_on_account:= l_aging_on_account ;

Line 228: and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y'

224: and ar_cash_receipts.pay_from_customer = customer_id
225: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
226: and ar_cash_receipts.currency_code = Currency_Bucket
227: and ar_receivable_applications.gl_date <= sysdate
228: and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y'
229: ;
230:
231: c_aging_on_account:= l_aging_on_account ;
232: c_aging_unapplied:= l_aging_unapplied ;

Line 612: select nvl(sum(ar_receivable_applications.earned_discount_taken ), 0) earned,

608: add_months(sysdate, -12) and sysdate
609: and nvl(ar_adjustments.postable, 'Y') = 'Y'
610: ;
611:
612: select nvl(sum(ar_receivable_applications.earned_discount_taken ), 0) earned,
613: nvl(max(decode(nvl(ar_receivable_applications.earned_discount_taken, 0),
614: 0, ' ',
615: decode(ar_payment_schedules.invoice_currency_code,
616: functional_currency, ' ',

Line 613: nvl(max(decode(nvl(ar_receivable_applications.earned_discount_taken, 0),

609: and nvl(ar_adjustments.postable, 'Y') = 'Y'
610: ;
611:
612: select nvl(sum(ar_receivable_applications.earned_discount_taken ), 0) earned,
613: nvl(max(decode(nvl(ar_receivable_applications.earned_discount_taken, 0),
614: 0, ' ',
615: decode(ar_payment_schedules.invoice_currency_code,
616: functional_currency, ' ',
617: decode(ar_payment_schedules.exchange_rate,

Line 619: nvl(sum(ar_receivable_applications.unearned_discount_taken), 0) unearned,

615: decode(ar_payment_schedules.invoice_currency_code,
616: functional_currency, ' ',
617: decode(ar_payment_schedules.exchange_rate,
618: NULL, '*', ' ')))), ' ') earned_cvt,
619: nvl(sum(ar_receivable_applications.unearned_discount_taken), 0) unearned,
620: nvl(max(decode(nvl(ar_receivable_applications.unearned_discount_taken, 0),
621: 0, ' ',
622: decode(ar_payment_schedules.invoice_currency_code,
623: functional_currency, ' ',

Line 620: nvl(max(decode(nvl(ar_receivable_applications.unearned_discount_taken, 0),

616: functional_currency, ' ',
617: decode(ar_payment_schedules.exchange_rate,
618: NULL, '*', ' ')))), ' ') earned_cvt,
619: nvl(sum(ar_receivable_applications.unearned_discount_taken), 0) unearned,
620: nvl(max(decode(nvl(ar_receivable_applications.unearned_discount_taken, 0),
621: 0, ' ',
622: decode(ar_payment_schedules.invoice_currency_code,
623: functional_currency, ' ',
624: decode(ar_payment_schedules.exchange_rate,

Line 626: decode(count(ar_receivable_applications.apply_date), 0, 0,

622: decode(ar_payment_schedules.invoice_currency_code,
623: functional_currency, ' ',
624: decode(ar_payment_schedules.exchange_rate,
625: NULL, '*', ' ')))), ' ') unearned_cvt,
626: decode(count(ar_receivable_applications.apply_date), 0, 0,
627: round(sum(ar_receivable_applications.apply_date -
628: ar_payment_schedules.trx_date) /
629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,

Line 627: round(sum(ar_receivable_applications.apply_date -

623: functional_currency, ' ',
624: decode(ar_payment_schedules.exchange_rate,
625: NULL, '*', ' ')))), ' ') unearned_cvt,
626: decode(count(ar_receivable_applications.apply_date), 0, 0,
627: round(sum(ar_receivable_applications.apply_date -
628: ar_payment_schedules.trx_date) /
629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -

Line 629: count(ar_receivable_applications.apply_date))) avgdays,

625: NULL, '*', ' ')))), ' ') unearned_cvt,
626: decode(count(ar_receivable_applications.apply_date), 0, 0,
627: round(sum(ar_receivable_applications.apply_date -
628: ar_payment_schedules.trx_date) /
629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -
632: ar_payment_schedules.due_date) /
633: count(ar_receivable_applications.apply_date))) avgdayslate,

Line 630: decode(count(ar_receivable_applications.apply_date), 0, 0,

626: decode(count(ar_receivable_applications.apply_date), 0, 0,
627: round(sum(ar_receivable_applications.apply_date -
628: ar_payment_schedules.trx_date) /
629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -
632: ar_payment_schedules.due_date) /
633: count(ar_receivable_applications.apply_date))) avgdayslate,
634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -

Line 631: round(sum(ar_receivable_applications.apply_date -

627: round(sum(ar_receivable_applications.apply_date -
628: ar_payment_schedules.trx_date) /
629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -
632: ar_payment_schedules.due_date) /
633: count(ar_receivable_applications.apply_date))) avgdayslate,
634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -
635: ar_payment_schedules.due_date),

Line 633: count(ar_receivable_applications.apply_date))) avgdayslate,

629: count(ar_receivable_applications.apply_date))) avgdays,
630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -
632: ar_payment_schedules.due_date) /
633: count(ar_receivable_applications.apply_date))) avgdayslate,
634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -
635: ar_payment_schedules.due_date),
636: 1, 1, 0)), 0) newlate,
637: nvl(sum( decode(sign(ar_receivable_applications.apply_date -

Line 634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -

630: decode(count(ar_receivable_applications.apply_date), 0, 0,
631: round(sum(ar_receivable_applications.apply_date -
632: ar_payment_schedules.due_date) /
633: count(ar_receivable_applications.apply_date))) avgdayslate,
634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -
635: ar_payment_schedules.due_date),
636: 1, 1, 0)), 0) newlate,
637: nvl(sum( decode(sign(ar_receivable_applications.apply_date -
638: ar_payment_schedules.due_date),

Line 637: nvl(sum( decode(sign(ar_receivable_applications.apply_date -

633: count(ar_receivable_applications.apply_date))) avgdayslate,
634: nvl(sum(decode(sign(ar_receivable_applications.apply_date -
635: ar_payment_schedules.due_date),
636: 1, 1, 0)), 0) newlate,
637: nvl(sum( decode(sign(ar_receivable_applications.apply_date -
638: ar_payment_schedules.due_date),
639: 1, 0, 1)), 0) newontime
640: into l_ytd_earned_discount_amount,
641: l_ytd_convert_earned_discount,

Line 648: from ar_receivable_applications, ar_payment_schedules

644: l_ytd_average_payment_days,
645: l_ytd_average_days_late,
646: l_ytd_late_payments_count,
647: l_ytd_on_time_payments_count
648: from ar_receivable_applications, ar_payment_schedules
649: where ar_receivable_applications.applied_payment_schedule_id =
650: ar_payment_schedules.payment_schedule_id
651: and ar_payment_schedules.customer_id = customer_id
652: and ar_payment_schedules.customer_site_use_id = site_use_id

Line 649: where ar_receivable_applications.applied_payment_schedule_id =

645: l_ytd_average_days_late,
646: l_ytd_late_payments_count,
647: l_ytd_on_time_payments_count
648: from ar_receivable_applications, ar_payment_schedules
649: where ar_receivable_applications.applied_payment_schedule_id =
650: ar_payment_schedules.payment_schedule_id
651: and ar_payment_schedules.customer_id = customer_id
652: and ar_payment_schedules.customer_site_use_id = site_use_id
653: and ar_payment_schedules.invoice_currency_code = Currency_Bucket

Line 654: and ar_receivable_applications.apply_date between

650: ar_payment_schedules.payment_schedule_id
651: and ar_payment_schedules.customer_id = customer_id
652: and ar_payment_schedules.customer_site_use_id = site_use_id
653: and ar_payment_schedules.invoice_currency_code = Currency_Bucket
654: and ar_receivable_applications.apply_date between
655: add_months(sysdate, -12) and sysdate
656: and ar_receivable_applications.status = 'APP'
657: and ar_receivable_applications.display = 'Y'
658: and nvl(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y'

Line 656: and ar_receivable_applications.status = 'APP'

652: and ar_payment_schedules.customer_site_use_id = site_use_id
653: and ar_payment_schedules.invoice_currency_code = Currency_Bucket
654: and ar_receivable_applications.apply_date between
655: add_months(sysdate, -12) and sysdate
656: and ar_receivable_applications.status = 'APP'
657: and ar_receivable_applications.display = 'Y'
658: and nvl(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y'
659: ;
660:

Line 657: and ar_receivable_applications.display = 'Y'

653: and ar_payment_schedules.invoice_currency_code = Currency_Bucket
654: and ar_receivable_applications.apply_date between
655: add_months(sysdate, -12) and sysdate
656: and ar_receivable_applications.status = 'APP'
657: and ar_receivable_applications.display = 'Y'
658: and nvl(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y'
659: ;
660:
661: select nvl(sum(ROUND(decode(ar_cash_receipts.status,'NSF', acrh.acctd_amount, 'STOP', acrh.acctd_amount, 0)

Line 1063: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

1059: l_aging_unapplied_profile:=0;
1060: l_loop:='N';
1061:
1062: SELECT
1063: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1064: AMOUNT_APPLIED, 0)),
1065: 0) on_account,
1066: nvl(max(decode(ar_receivable_applications.status, 'ACC',
1067: decode(ar_cash_receipts.currency_code,functional_currency, ' ',

Line 1066: nvl(max(decode(ar_receivable_applications.status, 'ACC',

1062: SELECT
1063: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1064: AMOUNT_APPLIED, 0)),
1065: 0) on_account,
1066: nvl(max(decode(ar_receivable_applications.status, 'ACC',
1067: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
1068: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
1069: ' ')), ' ') account_convert,
1070: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

Line 1070: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

1066: nvl(max(decode(ar_receivable_applications.status, 'ACC',
1067: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
1068: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
1069: ' ')), ' ') account_convert,
1070: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1071: amount_applied, 0)),
1072: 0) unapplied,
1073: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
1074: decode(ar_cash_receipts.currency_code,functional_currency, ' ',

Line 1073: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',

1069: ' ')), ' ') account_convert,
1070: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1071: amount_applied, 0)),
1072: 0) unapplied,
1073: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
1074: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
1075: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
1076: ' ')), ' ') unapp_convert
1077: into

Line 1083: ar_receivable_applications,

1079: l_aging_conv_on_ac_profile,
1080: l_aging_unapplied_profile,
1081: l_aging_conv_unap_prof
1082: from
1083: ar_receivable_applications,
1084: ar_cash_receipts
1085: where
1086: ar_receivable_applications.cash_receipt_id =
1087: ar_cash_receipts.cash_receipt_id

Line 1086: ar_receivable_applications.cash_receipt_id =

1082: from
1083: ar_receivable_applications,
1084: ar_cash_receipts
1085: where
1086: ar_receivable_applications.cash_receipt_id =
1087: ar_cash_receipts.cash_receipt_id
1088: and ar_cash_receipts.pay_from_customer = qc_customer
1089: and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1090: NVL(qc_site, ar_cash_receipts.customer_site_use_id)

Line 1092: and ar_receivable_applications.gl_date <= sysdate;

1088: and ar_cash_receipts.pay_from_customer = qc_customer
1089: and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1090: NVL(qc_site, ar_cash_receipts.customer_site_use_id)
1091: and ar_cash_receipts.currency_code = currency_credit
1092: and ar_receivable_applications.gl_date <= sysdate;
1093:
1094: for trx_rec in ps_trx loop
1095: curr_exists := instr(CP_related_currencies,trx_rec.invoice_currency_code);
1096: /*srw.message(593,'in loop - curr_exists = '||curr_exists);*/null;

Line 1108: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

1104: trx_curr := trx_rec.invoice_currency_code;
1105:
1106:
1107: SELECT
1108: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1109: AMOUNT_APPLIED, 0)), 0) on_account,
1110: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1111: amount_applied, 0)), 0) unapplied
1112: into

Line 1110: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

1106:
1107: SELECT
1108: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
1109: AMOUNT_APPLIED, 0)), 0) on_account,
1110: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
1111: amount_applied, 0)), 0) unapplied
1112: into
1113: l_aging_on_account_profile,
1114: l_aging_unapplied_profile

Line 1116: ar_receivable_applications,

1112: into
1113: l_aging_on_account_profile,
1114: l_aging_unapplied_profile
1115: from
1116: ar_receivable_applications,
1117: ar_cash_receipts
1118: where
1119: ar_receivable_applications.cash_receipt_id =
1120: ar_cash_receipts.cash_receipt_id

Line 1119: ar_receivable_applications.cash_receipt_id =

1115: from
1116: ar_receivable_applications,
1117: ar_cash_receipts
1118: where
1119: ar_receivable_applications.cash_receipt_id =
1120: ar_cash_receipts.cash_receipt_id
1121: and ar_cash_receipts.pay_from_customer = qc_customer
1122: and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1123: NVL(qc_site, ar_cash_receipts.customer_site_use_id)

Line 1125: and ar_receivable_applications.gl_date <= sysdate;

1121: and ar_cash_receipts.pay_from_customer = qc_customer
1122: and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
1123: NVL(qc_site, ar_cash_receipts.customer_site_use_id)
1124: and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
1125: and ar_receivable_applications.gl_date <= sysdate;
1126:
1127: trx_amount := trx_rec.ammount_due -
1128: l_aging_unapplied_profile -
1129: l_aging_on_account_profile;

Line 1314: RA_CUSTOMER_TRX TRX2, AR_RECEIVABLE_APPLICATIONS APP

1310: ROUND(TRUNC(SYSDATE) - TRX1.TRX_DATE),
1311: TRX2.CUSTOMER_TRX_ID,
1312: TRX1.CUSTOMER_TRX_ID
1313: FROM RA_CUST_TRX_TYPES TYPES, RA_CUSTOMER_TRX TRX1, AR_PAYMENT_SCHEDULES PS,
1314: RA_CUSTOMER_TRX TRX2, AR_RECEIVABLE_APPLICATIONS APP
1315: WHERE TRX1.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
1316: AND TRX1.BILL_TO_CUSTOMER_ID = CUSTOMER_ID_1
1317: AND APP.APPLIED_CUSTOMER_TRX_ID = TRX2.CUSTOMER_TRX_ID (+)
1318: AND APP.CUSTOMER_TRX_ID (+) = TRX1.CUSTOMER_TRX_ID

Line 1834: AR_RECEIVABLE_APPLICATIONS,

1830: RA_CUSTOMER_TRX.TRX_NUMBER
1831: FROM AR_LOOKUPS,
1832: AR_CASH_RECEIPTS,
1833: AR_CASH_RECEIPT_HISTORY CRH,
1834: AR_RECEIVABLE_APPLICATIONS,
1835: RA_CUSTOMER_TRX
1836: WHERE NVL(AR_CASH_RECEIPTS.TYPE, 'CASH') = AR_LOOKUPS.LOOKUP_CODE
1837: AND AR_LOOKUPS.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
1838: AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID

Line 1841: AR_RECEIVABLE_APPLICATIONS.CASH_RECEIPT_ID

1837: AND AR_LOOKUPS.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
1838: AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID
1839: AND AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID = site_use_id
1840: AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID =
1841: AR_RECEIVABLE_APPLICATIONS.CASH_RECEIPT_ID
1842: AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
1843: AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
1844: AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =
1845: RA_CUSTOMER_TRX.CUSTOMER_TRX_ID (+)

Line 1844: AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =

1840: AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID =
1841: AR_RECEIVABLE_APPLICATIONS.CASH_RECEIPT_ID
1842: AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
1843: AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
1844: AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =
1845: RA_CUSTOMER_TRX.CUSTOMER_TRX_ID (+)
1846: ORDER BY AR_CASH_RECEIPTS.CREATION_DATE DESC,
1847: AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC,
1848: AR_RECEIVABLE_APPLICATIONS.CREATION_DATE DESC

Line 1848: AR_RECEIVABLE_APPLICATIONS.CREATION_DATE DESC

1844: AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =
1845: RA_CUSTOMER_TRX.CUSTOMER_TRX_ID (+)
1846: ORDER BY AR_CASH_RECEIPTS.CREATION_DATE DESC,
1847: AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC,
1848: AR_RECEIVABLE_APPLICATIONS.CREATION_DATE DESC
1849: ;
1850:
1851: BEGIN
1852:

Line 3061: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3057:
3058: end if;
3059:
3060:
3061: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3062: AMOUNT_APPLIED, 0)),
3063: 0) on_account,
3064: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3065: amount_applied, 0)),

Line 3064: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3060:
3061: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3062: AMOUNT_APPLIED, 0)),
3063: 0) on_account,
3064: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3065: amount_applied, 0)),
3066: 0) unapplied
3067: into l_aging_on_account_profile,
3068: l_aging_unapplied_profile

Line 3069: from ar_receivable_applications,

3065: amount_applied, 0)),
3066: 0) unapplied
3067: into l_aging_on_account_profile,
3068: l_aging_unapplied_profile
3069: from ar_receivable_applications,
3070: ar_cash_receipts
3071: where ar_receivable_applications.cash_receipt_id =
3072: ar_cash_receipts.cash_receipt_id
3073: and ar_cash_receipts.pay_from_customer = customer_id

Line 3071: where ar_receivable_applications.cash_receipt_id =

3067: into l_aging_on_account_profile,
3068: l_aging_unapplied_profile
3069: from ar_receivable_applications,
3070: ar_cash_receipts
3071: where ar_receivable_applications.cash_receipt_id =
3072: ar_cash_receipts.cash_receipt_id
3073: and ar_cash_receipts.pay_from_customer = customer_id
3074: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3075: and ar_cash_receipts.currency_code = trx_cur

Line 3076: and ar_receivable_applications.gl_date <= sysdate;

3072: ar_cash_receipts.cash_receipt_id
3073: and ar_cash_receipts.pay_from_customer = customer_id
3074: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3075: and ar_cash_receipts.currency_code = trx_cur
3076: and ar_receivable_applications.gl_date <= sysdate;
3077:
3078:
3079: cp_adjusted_amount := trx_amount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3080:

Line 3163: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3159:
3160: end if;
3161:
3162:
3163: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3164: AMOUNT_APPLIED, 0)),
3165: 0) on_account,
3166: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3167: amount_applied, 0)),

Line 3166: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3162:
3163: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3164: AMOUNT_APPLIED, 0)),
3165: 0) on_account,
3166: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3167: amount_applied, 0)),
3168: 0) unapplied
3169: into l_aging_on_account_profile,
3170: l_aging_unapplied_profile

Line 3171: from ar_receivable_applications,

3167: amount_applied, 0)),
3168: 0) unapplied
3169: into l_aging_on_account_profile,
3170: l_aging_unapplied_profile
3171: from ar_receivable_applications,
3172: ar_cash_receipts
3173: where ar_receivable_applications.cash_receipt_id =
3174: ar_cash_receipts.cash_receipt_id
3175: and ar_cash_receipts.pay_from_customer = customer_id

Line 3173: where ar_receivable_applications.cash_receipt_id =

3169: into l_aging_on_account_profile,
3170: l_aging_unapplied_profile
3171: from ar_receivable_applications,
3172: ar_cash_receipts
3173: where ar_receivable_applications.cash_receipt_id =
3174: ar_cash_receipts.cash_receipt_id
3175: and ar_cash_receipts.pay_from_customer = customer_id
3176: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3177: and ar_cash_receipts.currency_code = trx_cur2

Line 3178: and ar_receivable_applications.gl_date <= sysdate;

3174: ar_cash_receipts.cash_receipt_id
3175: and ar_cash_receipts.pay_from_customer = customer_id
3176: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3177: and ar_cash_receipts.currency_code = trx_cur2
3178: and ar_receivable_applications.gl_date <= sysdate;
3179:
3180: /*srw.message(300, 'DEBUG: adjusted amount : '|| to_char(cp_adjusted_amount1));*/null;
3181:
3182: /*srw.message(300, 'DEBUG: unapplied profile : '|| to_char(l_aging_unapplied_profile));*/null;

Line 3281: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3277: l_loop:='N';
3278:
3279:
3280: SELECT
3281: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3282: AMOUNT_APPLIED, 0)),
3283: 0) on_account,
3284: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3285: decode(ar_cash_receipts.currency_code,functional_currency, ' ',

Line 3284: nvl(max(decode(ar_receivable_applications.status, 'ACC',

3280: SELECT
3281: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3282: AMOUNT_APPLIED, 0)),
3283: 0) on_account,
3284: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3285: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
3286: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3287: ' ')), ' ') account_convert,
3288: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

Line 3288: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3284: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3285: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
3286: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3287: ' ')), ' ') account_convert,
3288: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3289: amount_applied, 0)),
3290: 0) unapplied,
3291: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3292: decode(ar_cash_receipts.currency_code,functional_currency, ' ',

Line 3291: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',

3287: ' ')), ' ') account_convert,
3288: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3289: amount_applied, 0)),
3290: 0) unapplied,
3291: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3292: decode(ar_cash_receipts.currency_code,functional_currency, ' ',
3293: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3294: ' ')), ' ') unapp_convert
3295: into

Line 3300: from ar_receivable_applications,

3296: l_aging_on_account_profile,
3297: l_aging_conv_on_ac_profile,
3298: l_aging_unapplied_profile,
3299: l_aging_conv_unap_prof
3300: from ar_receivable_applications,
3301: ar_cash_receipts
3302: where ar_receivable_applications.cash_receipt_id =
3303: ar_cash_receipts.cash_receipt_id
3304:

Line 3302: where ar_receivable_applications.cash_receipt_id =

3298: l_aging_unapplied_profile,
3299: l_aging_conv_unap_prof
3300: from ar_receivable_applications,
3301: ar_cash_receipts
3302: where ar_receivable_applications.cash_receipt_id =
3303: ar_cash_receipts.cash_receipt_id
3304:
3305:
3306: and ar_cash_receipts.pay_from_customer = customer_id

Line 3309: and ar_receivable_applications.gl_date <= sysdate;

3305:
3306: and ar_cash_receipts.pay_from_customer = customer_id
3307: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3308: and ar_cash_receipts.currency_code = org_currency_code
3309: and ar_receivable_applications.gl_date <= sysdate;
3310:
3311:
3312: FOR trx_rec IN ps_trx
3313: LOOP

Line 3325: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3321: l_aging_on_account_profile:=0;
3322: l_aging_unapplied_profile:=0;
3323: l_loop:='Y';
3324:
3325: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3326: AMOUNT_APPLIED, 0)),
3327: 0) on_account,
3328: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3329: amount_applied, 0)),

Line 3328: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3324:
3325: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3326: AMOUNT_APPLIED, 0)),
3327: 0) on_account,
3328: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3329: amount_applied, 0)),
3330: 0) unapplied
3331: into l_aging_on_account_profile,
3332: l_aging_unapplied_profile

Line 3333: from ar_receivable_applications,

3329: amount_applied, 0)),
3330: 0) unapplied
3331: into l_aging_on_account_profile,
3332: l_aging_unapplied_profile
3333: from ar_receivable_applications,
3334: ar_cash_receipts
3335: where ar_receivable_applications.cash_receipt_id =
3336: ar_cash_receipts.cash_receipt_id
3337: and ar_cash_receipts.pay_from_customer = customer_id

Line 3335: where ar_receivable_applications.cash_receipt_id =

3331: into l_aging_on_account_profile,
3332: l_aging_unapplied_profile
3333: from ar_receivable_applications,
3334: ar_cash_receipts
3335: where ar_receivable_applications.cash_receipt_id =
3336: ar_cash_receipts.cash_receipt_id
3337: and ar_cash_receipts.pay_from_customer = customer_id
3338: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3339: and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code

Line 3340: and ar_receivable_applications.gl_date <= sysdate;

3336: ar_cash_receipts.cash_receipt_id
3337: and ar_cash_receipts.pay_from_customer = customer_id
3338: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3339: and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
3340: and ar_receivable_applications.gl_date <= sysdate;
3341:
3342: trx_amount := trx_rec.ammount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3343:
3344: base_amount := gl_currency_api.convert_amount_sql(trx_curr,limit_currency,SYSDATE,'Corporate',trx_amount);

Line 3454: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3450:
3451: end if;
3452:
3453:
3454: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3455: AMOUNT_APPLIED, 0)),
3456: 0) on_account,
3457: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3458: amount_applied, 0)),

Line 3457: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3453:
3454: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3455: AMOUNT_APPLIED, 0)),
3456: 0) on_account,
3457: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3458: amount_applied, 0)),
3459: 0) unapplied
3460: into l_aging_on_account_profile,
3461: l_aging_unapplied_profile

Line 3462: from ar_receivable_applications,

3458: amount_applied, 0)),
3459: 0) unapplied
3460: into l_aging_on_account_profile,
3461: l_aging_unapplied_profile
3462: from ar_receivable_applications,
3463: ar_cash_receipts
3464: where ar_receivable_applications.cash_receipt_id =
3465: ar_cash_receipts.cash_receipt_id
3466: and ar_cash_receipts.pay_from_customer = customer_id

Line 3464: where ar_receivable_applications.cash_receipt_id =

3460: into l_aging_on_account_profile,
3461: l_aging_unapplied_profile
3462: from ar_receivable_applications,
3463: ar_cash_receipts
3464: where ar_receivable_applications.cash_receipt_id =
3465: ar_cash_receipts.cash_receipt_id
3466: and ar_cash_receipts.pay_from_customer = customer_id
3467: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3468: and ar_cash_receipts.currency_code = org_trx_cur

Line 3469: and ar_receivable_applications.gl_date <= sysdate;

3465: ar_cash_receipts.cash_receipt_id
3466: and ar_cash_receipts.pay_from_customer = customer_id
3467: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3468: and ar_cash_receipts.currency_code = org_trx_cur
3469: and ar_receivable_applications.gl_date <= sysdate;
3470:
3471: /*srw.message(300, 'DEBUG: adjusted amount : '|| to_char(cp_adjusted_amount2));*/null;
3472:
3473: /*srw.message(300, 'DEBUG: unapplied profile : '|| to_char(l_aging_unapplied_profile));*/null;

Line 3743: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3739: l_aging_unapplied_profile:=0;
3740:
3741:
3742: SELECT
3743: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3744: AMOUNT_APPLIED, 0)),
3745: 0) on_account,
3746: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3747: decode(ar_cash_receipts.currency_code, functional_currency, ' ',

Line 3746: nvl(max(decode(ar_receivable_applications.status, 'ACC',

3742: SELECT
3743: NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3744: AMOUNT_APPLIED, 0)),
3745: 0) on_account,
3746: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3747: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
3748: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3749: ' ')), ' ') account_convert,
3750: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

Line 3750: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3746: nvl(max(decode(ar_receivable_applications.status, 'ACC',
3747: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
3748: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3749: ' ')), ' ') account_convert,
3750: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3751: amount_applied, 0)),
3752: 0) unapplied,
3753: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3754: decode(ar_cash_receipts.currency_code, functional_currency, ' ',

Line 3753: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',

3749: ' ')), ' ') account_convert,
3750: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3751: amount_applied, 0)),
3752: 0) unapplied,
3753: nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
3754: decode(ar_cash_receipts.currency_code, functional_currency, ' ',
3755: decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
3756: ' ')), ' ') unapp_convert
3757: into

Line 3762: from ar_receivable_applications,

3758: l_aging_on_account_profile,
3759: l_aging_conv_on_ac_profile,
3760: l_aging_unapplied_profile,
3761: l_aging_conv_unap_prof
3762: from ar_receivable_applications,
3763: ar_cash_receipts
3764: where ar_receivable_applications.cash_receipt_id =
3765: ar_cash_receipts.cash_receipt_id
3766:

Line 3764: where ar_receivable_applications.cash_receipt_id =

3760: l_aging_unapplied_profile,
3761: l_aging_conv_unap_prof
3762: from ar_receivable_applications,
3763: ar_cash_receipts
3764: where ar_receivable_applications.cash_receipt_id =
3765: ar_cash_receipts.cash_receipt_id
3766:
3767: and ar_cash_receipts.pay_from_customer = customer_id
3768: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id

Line 3770: and ar_receivable_applications.gl_date <= sysdate;

3766:
3767: and ar_cash_receipts.pay_from_customer = customer_id
3768: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3769: and ar_cash_receipts.currency_code = currency_credit1
3770: and ar_receivable_applications.gl_date <= sysdate;
3771:
3772:
3773: FOR trx_rec IN ps_trx
3774: LOOP

Line 3785: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',

3781: l_loop:='Y';
3782: l_aging_on_account_profile:=0;
3783: l_aging_unapplied_profile:=0;
3784:
3785: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3786: AMOUNT_APPLIED, 0)),
3787: 0) on_account,
3788: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3789: amount_applied, 0)),

Line 3788: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',

3784:
3785: SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
3786: AMOUNT_APPLIED, 0)),
3787: 0) on_account,
3788: nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
3789: amount_applied, 0)),
3790: 0) unapplied
3791: into l_aging_on_account_profile,
3792: l_aging_unapplied_profile

Line 3793: from ar_receivable_applications,

3789: amount_applied, 0)),
3790: 0) unapplied
3791: into l_aging_on_account_profile,
3792: l_aging_unapplied_profile
3793: from ar_receivable_applications,
3794: ar_cash_receipts
3795: where ar_receivable_applications.cash_receipt_id =
3796: ar_cash_receipts.cash_receipt_id
3797: and ar_cash_receipts.pay_from_customer = customer_id

Line 3795: where ar_receivable_applications.cash_receipt_id =

3791: into l_aging_on_account_profile,
3792: l_aging_unapplied_profile
3793: from ar_receivable_applications,
3794: ar_cash_receipts
3795: where ar_receivable_applications.cash_receipt_id =
3796: ar_cash_receipts.cash_receipt_id
3797: and ar_cash_receipts.pay_from_customer = customer_id
3798: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3799: and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code

Line 3800: and ar_receivable_applications.gl_date <= sysdate;

3796: ar_cash_receipts.cash_receipt_id
3797: and ar_cash_receipts.pay_from_customer = customer_id
3798: and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
3799: and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
3800: and ar_receivable_applications.gl_date <= sysdate;
3801:
3802: trx_amount := trx_rec.ammount_due - l_aging_unapplied_profile - l_aging_on_account_profile;
3803:
3804: base_amount := gl_currency_api.convert_amount_sql