[Home] [Help]
254: BEGIN
255: SELECT sum(amount_settled), sum(ded_count)
256: INTO l_deduction_amount, l_deduction_count
257: FROM (
258: SELECT round(gl_currency_api.convert_amount(currency_code,
259: p_limit_currency, sysdate,
260: p_exchange_rate_type,
261: sum(amount_settled)),2) amount_settled,
262: count(*) ded_count
312: BEGIN
313: SELECT sum(amount_settled), sum(ded_count)
314: INTO l_deduction_amount, l_deduction_count
315: FROM (
316: SELECT round(gl_currency_api.convert_amount(currency_code,
317: p_limit_currency, sysdate,
318: p_exchange_rate_type,
319: sum(amount_settled)),2) amount_settled,
320: count(*) ded_count
347: BEGIN
348: SELECT sum(amount_settled), sum(ded_count)
349: INTO l_deduction_amount, l_deduction_count
350: FROM (
351: SELECT round(gl_currency_api.convert_amount(currency_code,
352: p_limit_currency, sysdate,
353: p_exchange_rate_type,
354: sum(amount_settled)),2) amount_settled,
355: count(*) ded_count
477:
478: l_party_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
479: largest_inv_amount
480: from ( select as_of_date LARGEST_INV_DATE,
481: gl_currency_api.convert_amount(currency,
482: :1,sysdate,
483: :2,
484: largest_inv_amount)largest_inv_amount,
485: RANK() OVER (PARTITION BY currency
519:
520: l_account_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
521: largest_inv_amount
522: from ( select as_of_date LARGEST_INV_DATE,
523: gl_currency_api.convert_amount(currency,
524: :1,sysdate,
525: :2,
526: largest_inv_amount)largest_inv_amount,
527: RANK() OVER (PARTITION BY cust_account_id,currency
539:
540: l_site_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
541: largest_inv_amount
542: from ( select as_of_date LARGEST_INV_DATE,
543: gl_currency_api.convert_amount(currency,
544: :1,sysdate,
545: :2,
546: largest_inv_amount)largest_inv_amount,
547: RANK() OVER (PARTITION BY cust_account_id, site_use_id,currency
559: part of bug fix 3557539
560: l_high_credit_party_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
561: op_bal_high_watermark
562: from ( select as_of_date op_bal_high_watermark_date,
563: gl_currency_api.convert_amount(currency,
564: :1,sysdate,
565: :2,
566: op_bal_high_watermark)op_bal_high_watermark,
567: RANK() OVER (PARTITION BY currency
591:
592: l_high_credit_account_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
593: op_bal_high_watermark
594: from ( select as_of_date op_bal_high_watermark_date,
595: gl_currency_api.convert_amount(currency,
596: :1,sysdate,
597: :2,
598: op_bal_high_watermark)op_bal_high_watermark,
599: RANK() OVER (PARTITION BY cust_account_id,currency,org_id
611:
612: l_high_credit_site_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
613: op_bal_high_watermark
614: from ( select as_of_date op_bal_high_watermark_date,
615: gl_currency_api.convert_amount(currency,
616: :1,sysdate,
617: :2,
618: op_bal_high_watermark)op_bal_high_watermark,
619: RANK() OVER (PARTITION BY cust_account_id, site_use_id,currency
746: logic returned the raw amount (in AR_TRX_SUMMARY currency)
747: rather than the converted amount -- I considered this a bug
748: and now return the converted amount (to CF currency) */
749: SELECT max(ltrim(
750: to_char( gl_currency_api.convert_amount(currency,
751: p_limit_currency,sysdate,
752: p_exchange_rate_type,
753: NVL(op_bal_high_watermark,0)),
754: '0999999999999999999D00')) || '~' ||
805: END;
806: ELSE -- p_analysis_level A or S (both)
807: BEGIN
808: SELECT max(ltrim(
809: to_char( gl_currency_api.convert_amount(currency,
810: p_limit_currency,sysdate,
811: p_exchange_rate_type,
812: NVL(op_bal_high_watermark,0)),
813: '0999999999999999999D00')) || '~' ||
1013: BEGIN
1014: -- if everything is ok then create record in case folder tables
1015: -- convert last payment amount to limit currency amount
1016: IF l_last_payment_currency IS NOT NULL THEN
1017: l_last_payment_amount_conv := gl_currency_api.convert_amount
1018: (l_last_payment_currency,
1019: p_limit_currency,sysdate,
1020: p_exchange_rate_type,
1021: l_last_payment_amount);
1424: g_conversion_type := get_conversion_type(p_credit_request_id);
1425: END IF;
1426:
1427: l_total_exposure :=
1428: gl_currency_api.convert_amount(l_limit_currency_code,
1429: p_limit_currency_code, sysdate,
1430: g_conversion_type,
1431: l_total_exposure);
1432:
1430: g_conversion_type,
1431: l_total_exposure);
1432:
1433: l_ar_cmount :=
1434: gl_currency_api.convert_amount(l_limit_currency_code,
1435: p_limit_currency_code, sysdate,
1436: g_conversion_type,
1437: l_ar_cmount);
1438:
1436: g_conversion_type,
1437: l_ar_cmount);
1438:
1439: l_order_hold_amount :=
1440: gl_currency_api.convert_amount(l_limit_currency_code,
1441: p_limit_currency_code, sysdate,
1442: g_conversion_type,
1443: l_order_hold_amount);
1444: END IF;
4798: round(SUM(NVL(DAYS_CREDIT_GRANTED_SUM,0))/
4799: decode(SUM(NVL(TOTAL_INVOICES_VALUE,0)),0,1,
4800: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) days_credit_granted, -- days credit granted
4801: SUM(NVL(NSF_STOP_PAYMENT_COUNT,0)) nsf_payment_count, -- NSF/Stop Payment Count
4802: round(gl_currency_api.convert_amount(currency,
4803: l_limit_currency,sysdate,
4804: l_exchange_rate_type,
4805: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
4806: round(gl_currency_api.convert_amount(currency,
4802: round(gl_currency_api.convert_amount(currency,
4803: l_limit_currency,sysdate,
4804: l_exchange_rate_type,
4805: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
4806: round(gl_currency_api.convert_amount(currency,
4807: l_limit_currency,sysdate,
4808: l_exchange_rate_type,
4809: SUM(NVL(TOTAL_CREDIT_MEMOS_VALUE,0))),2) credit_memo_value, -- Credit memos value
4810: SUM(NVL(TOTAL_CREDIT_MEMOS_COUNT,0)) credit_memo_count, -- Credit memos count
4813: SUM(NVL(count_of_tot_inv_inst_paid,0)) x_tot_inv_inst_paid,
4814: SUM(NVL(count_of_inv_inst_paid_late,0)) x_tot_inv_inst_paid_late,
4815: SUM(NVL(count_of_disc_inv_inst,0)) x_tot_disc_inv_inst,
4816: /* End 8692948 */
4817: round(gl_currency_api.convert_amount(currency,
4818: l_limit_currency,sysdate,
4819: l_exchange_rate_type,
4820: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
4821: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
4818: l_limit_currency,sysdate,
4819: l_exchange_rate_type,
4820: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
4821: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
4822: round(gl_currency_api.convert_amount(currency,
4823: l_limit_currency,sysdate,
4824: l_exchange_rate_type,
4825: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
4826: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
4823: l_limit_currency,sysdate,
4824: l_exchange_rate_type,
4825: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
4826: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
4827: round(gl_currency_api.convert_amount(currency,
4828: l_limit_currency,sysdate,
4829: l_exchange_rate_type,
4830: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
4831: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
4828: l_limit_currency,sysdate,
4829: l_exchange_rate_type,
4830: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
4831: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
4832: (round(gl_currency_api.convert_amount(currency,
4833: l_limit_currency,sysdate,
4834: l_exchange_rate_type,
4835: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comment above
4836: SUM(NVL(TOTAL_CASH_RECEIPTS_COUNT,0)) total_cash_receipts_count,
4833: l_limit_currency,sysdate,
4834: l_exchange_rate_type,
4835: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comment above
4836: SUM(NVL(TOTAL_CASH_RECEIPTS_COUNT,0)) total_cash_receipts_count,
4837: round(gl_currency_api.convert_amount(currency,
4838: l_limit_currency,sysdate,
4839: l_exchange_rate_type,
4840: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
4841: SUM(TOTAL_INVOICES_COUNT) total_invoices_count,
4838: l_limit_currency,sysdate,
4839: l_exchange_rate_type,
4840: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
4841: SUM(TOTAL_INVOICES_COUNT) total_invoices_count,
4842: round(gl_currency_api.convert_amount(currency,
4843: l_limit_currency,sysdate,
4844: l_exchange_rate_type,
4845: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
4846: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
4843: l_limit_currency,sysdate,
4844: l_exchange_rate_type,
4845: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
4846: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
4847: round(gl_currency_api.convert_amount(currency,
4848: l_limit_currency,sysdate,
4849: l_exchange_rate_type,
4850: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
4851: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
4848: l_limit_currency,sysdate,
4849: l_exchange_rate_type,
4850: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
4851: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
4852: round(gl_currency_api.convert_amount(currency,
4853: l_limit_currency,sysdate,
4854: l_exchange_rate_type,
4855: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
4856: SUM(NVL(TOTAL_chargeback_COUNT,0)) TOTAL_chargeback_count ,
4853: l_limit_currency,sysdate,
4854: l_exchange_rate_type,
4855: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
4856: SUM(NVL(TOTAL_chargeback_COUNT,0)) TOTAL_chargeback_count ,
4857: round(gl_currency_api.convert_amount(currency,
4858: l_limit_currency,sysdate,
4859: l_exchange_rate_type,
4860: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
4861: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
4858: l_limit_currency,sysdate,
4859: l_exchange_rate_type,
4860: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
4861: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
4862: round(gl_currency_api.convert_amount(currency,
4863: l_limit_currency,sysdate,
4864: l_exchange_rate_type,
4865: SUM(NVL(TOTAL_DEPOSITS_VALUE,0))),2) total_deposits_value,
4866: SUM(NVL(TOTAL_deposits_COUNT,0)) TOTAL_deposits_count
4942: round(SUM(NVL(DAYS_CREDIT_GRANTED_SUM,0))/
4943: decode(SUM(NVL(TOTAL_INVOICES_VALUE,0)),0,1,
4944: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) days_credit_granted, -- days credit granted
4945: SUM(NVL(NSF_STOP_PAYMENT_COUNT,0)) nsf_payment_count, -- NSF/Stop Payment Count
4946: round(gl_currency_api.convert_amount(currency,
4947: l_limit_currency,sysdate,
4948: l_exchange_rate_type,
4949: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
4950: round(gl_currency_api.convert_amount(currency,
4946: round(gl_currency_api.convert_amount(currency,
4947: l_limit_currency,sysdate,
4948: l_exchange_rate_type,
4949: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
4950: round(gl_currency_api.convert_amount(currency,
4951: l_limit_currency,sysdate,
4952: l_exchange_rate_type,
4953: SUM(NVL(TOTAL_CREDIT_MEMOS_VALUE,0))),2) credit_memo_value, -- Credit memos value
4954: SUM(NVL(TOTAL_CREDIT_MEMOS_COUNT,0)) credit_memo_count, -- Credit memos count
4957: SUM(NVL(count_of_tot_inv_inst_paid,0)) x_tot_inv_inst_paid,
4958: SUM(NVL(count_of_inv_inst_paid_late,0)) x_tot_inv_inst_paid_late,
4959: SUM(NVL(count_of_disc_inv_inst,0)) x_tot_disc_inv_inst,
4960: /* End 8692948 */
4961: round(gl_currency_api.convert_amount(currency,
4962: l_limit_currency,sysdate,
4963: l_exchange_rate_type,
4964: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
4965: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
4962: l_limit_currency,sysdate,
4963: l_exchange_rate_type,
4964: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
4965: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
4966: round(gl_currency_api.convert_amount(currency,
4967: l_limit_currency,sysdate,
4968: l_exchange_rate_type,
4969: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
4970: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
4967: l_limit_currency,sysdate,
4968: l_exchange_rate_type,
4969: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
4970: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
4971: round(gl_currency_api.convert_amount(currency,
4972: l_limit_currency,sysdate,
4973: l_exchange_rate_type,
4974: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
4975: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
4972: l_limit_currency,sysdate,
4973: l_exchange_rate_type,
4974: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
4975: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
4976: (round(gl_currency_api.convert_amount(currency,
4977: l_limit_currency,sysdate,
4978: l_exchange_rate_type,
4979: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comments above
4980: SUM(NVL(TOTAL_CASH_RECEIPTS_COUNT,0)) total_cash_receipts_count,
4977: l_limit_currency,sysdate,
4978: l_exchange_rate_type,
4979: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comments above
4980: SUM(NVL(TOTAL_CASH_RECEIPTS_COUNT,0)) total_cash_receipts_count,
4981: round(gl_currency_api.convert_amount(currency,
4982: l_limit_currency,sysdate,
4983: l_exchange_rate_type,
4984: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
4985: SUM(NVL(TOTAL_INVOICES_COUNT,0)) total_invoices_count,
4982: l_limit_currency,sysdate,
4983: l_exchange_rate_type,
4984: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
4985: SUM(NVL(TOTAL_INVOICES_COUNT,0)) total_invoices_count,
4986: round(gl_currency_api.convert_amount(currency,
4987: l_limit_currency,sysdate,
4988: l_exchange_rate_type,
4989: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
4990: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
4987: l_limit_currency,sysdate,
4988: l_exchange_rate_type,
4989: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
4990: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
4991: round(gl_currency_api.convert_amount(currency,
4992: l_limit_currency,sysdate,
4993: l_exchange_rate_type,
4994: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
4995: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
4992: l_limit_currency,sysdate,
4993: l_exchange_rate_type,
4994: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
4995: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
4996: round(gl_currency_api.convert_amount(currency,
4997: l_limit_currency,sysdate,
4998: l_exchange_rate_type,
4999: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
5000: SUM(NVL(TOTAL_chargeback_COUNT,0)) TOTAL_chargeback_count ,
4997: l_limit_currency,sysdate,
4998: l_exchange_rate_type,
4999: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
5000: SUM(NVL(TOTAL_chargeback_COUNT,0)) TOTAL_chargeback_count ,
5001: round(gl_currency_api.convert_amount(currency,
5002: l_limit_currency,sysdate,
5003: l_exchange_rate_type,
5004: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
5005: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
5002: l_limit_currency,sysdate,
5003: l_exchange_rate_type,
5004: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
5005: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
5006: round(gl_currency_api.convert_amount(currency,
5007: l_limit_currency,sysdate,
5008: l_exchange_rate_type,
5009: SUM(NVL(TOTAL_DEPOSITS_VALUE,0))),2) total_deposits_value,
5010: SUM(NVL(TOTAL_deposits_COUNT,0)) TOTAL_deposits_count
5063: round(SUM(NVL(DAYS_CREDIT_GRANTED_SUM,0))/
5064: decode(SUM(NVL(TOTAL_INVOICES_VALUE,0)),0,1,
5065: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) days_credit_granted, -- days credit granted
5066: SUM(NVL(NSF_STOP_PAYMENT_COUNT,0)) nsf_payment_count, -- NSF/Stop Payment Count
5067: round(gl_currency_api.convert_amount(currency,
5068: l_limit_currency,sysdate,
5069: l_exchange_rate_type,
5070: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
5071: round(gl_currency_api.convert_amount(currency,
5067: round(gl_currency_api.convert_amount(currency,
5068: l_limit_currency,sysdate,
5069: l_exchange_rate_type,
5070: SUM(NVL(NSF_STOP_PAYMENT_AMOUNT,0))),2) nsf_payment_amount, -- NSF/Stop Payment Amount
5071: round(gl_currency_api.convert_amount(currency,
5072: l_limit_currency,sysdate,
5073: l_exchange_rate_type,
5074: SUM(NVL(TOTAL_CREDIT_MEMOS_VALUE,0))),2) credit_memo_value, -- Credit memos value
5075: SUM(NVL(TOTAL_CREDIT_MEMOS_COUNT,0)) credit_memo_count, -- Credit memos count
5078: SUM(NVL(count_of_tot_inv_inst_paid,0)) x_tot_inv_inst_paid,
5079: SUM(NVL(count_of_inv_inst_paid_late,0)) x_tot_inv_inst_paid_late,
5080: SUM(NVL(count_of_disc_inv_inst,0)) x_tot_disc_inv_inst,
5081: /* End 8692948 */
5082: round(gl_currency_api.convert_amount(currency,
5083: l_limit_currency,sysdate,
5084: l_exchange_rate_type,
5085: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
5086: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
5083: l_limit_currency,sysdate,
5084: l_exchange_rate_type,
5085: SUM(NVL(INV_PAID_AMOUNT,0))),2) inv_paid_amount, -- invoices paid amount
5086: SUM(NVL(count_of_tot_inv_inst_paid,0)) inv_paid_count, -- invoices paid count
5087: round(gl_currency_api.convert_amount(currency,
5088: l_limit_currency,sysdate,
5089: l_exchange_rate_type,
5090: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
5091: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
5088: l_limit_currency,sysdate,
5089: l_exchange_rate_type,
5090: SUM(NVL(TOTAL_EARNED_DISC_VALUE,0))),2) earned_disc_value, -- Earned Dscount Value
5091: SUM(NVL(TOTAL_EARNED_DISC_COUNT,0)) earned_disc_count, -- Earned Dscount count
5092: round(gl_currency_api.convert_amount(currency,
5093: l_limit_currency,sysdate,
5094: l_exchange_rate_type,
5095: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
5096: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
5093: l_limit_currency,sysdate,
5094: l_exchange_rate_type,
5095: SUM(NVL(TOTAL_UNEARNED_DISC_VALUE,0))),2) unearned_disc_value, -- UnEarned Dscount Value
5096: SUM(NVL(TOTAL_UNEARNED_DISC_COUNT,0)) unearned_disc_count, -- UnEarned Dscount count
5097: (round(gl_currency_api.convert_amount(currency,
5098: l_limit_currency,sysdate,
5099: l_exchange_rate_type,
5100: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comments above
5101: SUM(TOTAL_CASH_RECEIPTS_COUNT) total_cash_receipts_count,
5098: l_limit_currency,sysdate,
5099: l_exchange_rate_type,
5100: SUM(NVL(TOTAL_CASH_RECEIPTS_VALUE,0))),2) * -1) total_cash_receipts_value, -- see the comments above
5101: SUM(TOTAL_CASH_RECEIPTS_COUNT) total_cash_receipts_count,
5102: round(gl_currency_api.convert_amount(currency,
5103: l_limit_currency,sysdate,
5104: l_exchange_rate_type,
5105: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
5106: SUM(NVL(TOTAL_INVOICES_COUNT,0)) total_invoices_count,
5103: l_limit_currency,sysdate,
5104: l_exchange_rate_type,
5105: SUM(NVL(TOTAL_INVOICES_VALUE,0))),2) total_invoices_value,
5106: SUM(NVL(TOTAL_INVOICES_COUNT,0)) total_invoices_count,
5107: round(gl_currency_api.convert_amount(currency,
5108: l_limit_currency,sysdate,
5109: l_exchange_rate_type,
5110: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
5111: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
5108: l_limit_currency,sysdate,
5109: l_exchange_rate_type,
5110: SUM(NVL(TOTAL_BILLS_RECEIVABLES_VALUE,0))),2) total_bills_receivables_value,
5111: SUM(NVL(TOTAL_BILLS_RECEIVABLES_COUNT,0)) TOTAL_BILLS_RECEIVABLES_count,
5112: round(gl_currency_api.convert_amount(currency,
5113: l_limit_currency,sysdate,
5114: l_exchange_rate_type,
5115: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
5116: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
5113: l_limit_currency,sysdate,
5114: l_exchange_rate_type,
5115: SUM(NVL(TOTAL_DEBIT_MEMOS_VALUE,0))),2) total_debit_memos_value,
5116: SUM(NVL(TOTAL_DEBIT_MEMOS_COUNT,0)) TOTAL_debit_memos_count,
5117: round(gl_currency_api.convert_amount(currency,
5118: l_limit_currency,sysdate,
5119: l_exchange_rate_type,
5120: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
5121: SUM(TOTAL_chargeback_COUNT) TOTAL_chargeback_count ,
5118: l_limit_currency,sysdate,
5119: l_exchange_rate_type,
5120: SUM(NVL(TOTAL_CHARGEBACK_VALUE,0))),2) total_chargeback_value,
5121: SUM(TOTAL_chargeback_COUNT) TOTAL_chargeback_count ,
5122: round(gl_currency_api.convert_amount(currency,
5123: l_limit_currency,sysdate,
5124: l_exchange_rate_type,
5125: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
5126: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
5123: l_limit_currency,sysdate,
5124: l_exchange_rate_type,
5125: SUM(NVL(TOTAL_ADJUSTMENTS_VALUE,0))),2) total_adjustments_value,
5126: SUM(NVL(TOTAL_adjustments_COUNT,0)) TOTAL_adjustments_count ,
5127: round(gl_currency_api.convert_amount(currency,
5128: l_limit_currency,sysdate,
5129: l_exchange_rate_type,
5130: SUM(NVL(TOTAL_DEPOSITS_VALUE,0))),2) total_deposits_value,
5131: SUM(NVL(TOTAL_deposits_COUNT,0)) TOTAL_deposits_count
5170: '209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
5171: '210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
5172: FROM (
5173: SELECT
5174: gl_currency_api.convert_amount(currency,
5175: l_limit_currency,sysdate,
5176: l_exchange_rate_type,
5177: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5178: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5177: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5178: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5179: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
5180: nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
5181: gl_currency_api.convert_amount(currency,
5182: l_limit_currency,sysdate,
5183: l_exchange_rate_type,
5184: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5185: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5182: l_limit_currency,sysdate,
5183: l_exchange_rate_type,
5184: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5185: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5186: gl_currency_api.convert_amount(currency,
5187: l_limit_currency,sysdate,
5188: l_exchange_rate_type,
5189: SUM(past_due_inv_value)) past_due_inv_value,
5190: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5187: l_limit_currency,sysdate,
5188: l_exchange_rate_type,
5189: SUM(past_due_inv_value)) past_due_inv_value,
5190: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5191: gl_currency_api.convert_amount(currency,
5192: l_limit_currency,sysdate,
5193: l_exchange_rate_type,
5194: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5195: SUM(disputed_inv_count) disputed_inv_count,
5192: l_limit_currency,sysdate,
5193: l_exchange_rate_type,
5194: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5195: SUM(disputed_inv_count) disputed_inv_count,
5196: gl_currency_api.convert_amount(currency,
5197: l_limit_currency,sysdate,
5198: l_exchange_rate_type,
5199: SUM(pending_adj_value)) pending_adj_value,
5200: gl_currency_api.convert_amount(currency,
5196: gl_currency_api.convert_amount(currency,
5197: l_limit_currency,sysdate,
5198: l_exchange_rate_type,
5199: SUM(pending_adj_value)) pending_adj_value,
5200: gl_currency_api.convert_amount(currency,
5201: l_limit_currency,sysdate,
5202: l_exchange_rate_type,
5203: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5204: gl_currency_api.convert_amount(currency,
5200: gl_currency_api.convert_amount(currency,
5201: l_limit_currency,sysdate,
5202: l_exchange_rate_type,
5203: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5204: gl_currency_api.convert_amount(currency,
5205: l_limit_currency,sysdate,
5206: l_exchange_rate_type,
5207: SUM(op_invoices_value)) op_invoices_value,
5208: SUM(op_invoices_count) op_invoices_count,
5205: l_limit_currency,sysdate,
5206: l_exchange_rate_type,
5207: SUM(op_invoices_value)) op_invoices_value,
5208: SUM(op_invoices_count) op_invoices_count,
5209: gl_currency_api.convert_amount(currency,
5210: l_limit_currency,sysdate,
5211: l_exchange_rate_type,
5212: SUM(op_debit_memos_value)) op_debit_memos_value,
5213: SUM(op_debit_memos_count) op_debit_memos_count,
5210: l_limit_currency,sysdate,
5211: l_exchange_rate_type,
5212: SUM(op_debit_memos_value)) op_debit_memos_value,
5213: SUM(op_debit_memos_count) op_debit_memos_count,
5214: gl_currency_api.convert_amount(currency,
5215: l_limit_currency,sysdate,
5216: l_exchange_rate_type,
5217: SUM(op_deposits_value)) op_deposits_value,
5218: SUM(op_deposits_count) op_deposits_count,
5215: l_limit_currency,sysdate,
5216: l_exchange_rate_type,
5217: SUM(op_deposits_value)) op_deposits_value,
5218: SUM(op_deposits_count) op_deposits_count,
5219: gl_currency_api.convert_amount(currency,
5220: l_limit_currency,sysdate,
5221: l_exchange_rate_type,
5222: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5223: SUM(op_bills_receivables_count) op_bills_receivables_count,
5220: l_limit_currency,sysdate,
5221: l_exchange_rate_type,
5222: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5223: SUM(op_bills_receivables_count) op_bills_receivables_count,
5224: gl_currency_api.convert_amount(currency,
5225: l_limit_currency,sysdate,
5226: l_exchange_rate_type,
5227: SUM(op_chargeback_value)) op_chargeback_value,
5228: SUM(op_chargeback_count) op_chargeback_count,
5225: l_limit_currency,sysdate,
5226: l_exchange_rate_type,
5227: SUM(op_chargeback_value)) op_chargeback_value,
5228: SUM(op_chargeback_count) op_chargeback_count,
5229: gl_currency_api.convert_amount(currency,
5230: l_limit_currency,sysdate,
5231: l_exchange_rate_type,
5232: SUM(op_credit_memos_value)) op_credit_memos_value,
5233: SUM(op_credit_memos_count) op_credit_memos_count,
5230: l_limit_currency,sysdate,
5231: l_exchange_rate_type,
5232: SUM(op_credit_memos_value)) op_credit_memos_value,
5233: SUM(op_credit_memos_count) op_credit_memos_count,
5234: gl_currency_api.convert_amount(currency,
5235: l_limit_currency,sysdate,
5236: l_exchange_rate_type,
5237: SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
5238: SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
5292: '209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
5293: '210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
5294: FROM (
5295: SELECT
5296: gl_currency_api.convert_amount(currency,
5297: l_limit_currency,sysdate,
5298: l_exchange_rate_type,
5299: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5300: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5299: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5300: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5301: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
5302: nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
5303: gl_currency_api.convert_amount(currency,
5304: l_limit_currency,sysdate,
5305: l_exchange_rate_type,
5306: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5307: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5304: l_limit_currency,sysdate,
5305: l_exchange_rate_type,
5306: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5307: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5308: gl_currency_api.convert_amount(currency,
5309: l_limit_currency,sysdate,
5310: l_exchange_rate_type,
5311: SUM(past_due_inv_value)) past_due_inv_value,
5312: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5309: l_limit_currency,sysdate,
5310: l_exchange_rate_type,
5311: SUM(past_due_inv_value)) past_due_inv_value,
5312: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5313: gl_currency_api.convert_amount(currency,
5314: l_limit_currency,sysdate,
5315: l_exchange_rate_type,
5316: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5317: SUM(disputed_inv_count) disputed_inv_count,
5314: l_limit_currency,sysdate,
5315: l_exchange_rate_type,
5316: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5317: SUM(disputed_inv_count) disputed_inv_count,
5318: gl_currency_api.convert_amount(currency,
5319: l_limit_currency,sysdate,
5320: l_exchange_rate_type,
5321: SUM(pending_adj_value)) pending_adj_value,
5322: gl_currency_api.convert_amount(currency,
5318: gl_currency_api.convert_amount(currency,
5319: l_limit_currency,sysdate,
5320: l_exchange_rate_type,
5321: SUM(pending_adj_value)) pending_adj_value,
5322: gl_currency_api.convert_amount(currency,
5323: l_limit_currency,sysdate,
5324: l_exchange_rate_type,
5325: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5326: gl_currency_api.convert_amount(currency,
5322: gl_currency_api.convert_amount(currency,
5323: l_limit_currency,sysdate,
5324: l_exchange_rate_type,
5325: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5326: gl_currency_api.convert_amount(currency,
5327: l_limit_currency,sysdate,
5328: l_exchange_rate_type,
5329: SUM(op_invoices_value)) op_invoices_value,
5330: SUM(op_invoices_count) op_invoices_count,
5327: l_limit_currency,sysdate,
5328: l_exchange_rate_type,
5329: SUM(op_invoices_value)) op_invoices_value,
5330: SUM(op_invoices_count) op_invoices_count,
5331: gl_currency_api.convert_amount(currency,
5332: l_limit_currency,sysdate,
5333: l_exchange_rate_type,
5334: SUM(op_debit_memos_value)) op_debit_memos_value,
5335: SUM(op_debit_memos_count) op_debit_memos_count,
5332: l_limit_currency,sysdate,
5333: l_exchange_rate_type,
5334: SUM(op_debit_memos_value)) op_debit_memos_value,
5335: SUM(op_debit_memos_count) op_debit_memos_count,
5336: gl_currency_api.convert_amount(currency,
5337: l_limit_currency,sysdate,
5338: l_exchange_rate_type,
5339: SUM(op_deposits_value)) op_deposits_value,
5340: SUM(op_deposits_count) op_deposits_count,
5337: l_limit_currency,sysdate,
5338: l_exchange_rate_type,
5339: SUM(op_deposits_value)) op_deposits_value,
5340: SUM(op_deposits_count) op_deposits_count,
5341: gl_currency_api.convert_amount(currency,
5342: l_limit_currency,sysdate,
5343: l_exchange_rate_type,
5344: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5345: SUM(op_bills_receivables_count) op_bills_receivables_count,
5342: l_limit_currency,sysdate,
5343: l_exchange_rate_type,
5344: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5345: SUM(op_bills_receivables_count) op_bills_receivables_count,
5346: gl_currency_api.convert_amount(currency,
5347: l_limit_currency,sysdate,
5348: l_exchange_rate_type,
5349: SUM(op_chargeback_value)) op_chargeback_value,
5350: SUM(op_chargeback_count) op_chargeback_count,
5347: l_limit_currency,sysdate,
5348: l_exchange_rate_type,
5349: SUM(op_chargeback_value)) op_chargeback_value,
5350: SUM(op_chargeback_count) op_chargeback_count,
5351: gl_currency_api.convert_amount(currency,
5352: l_limit_currency,sysdate,
5353: l_exchange_rate_type,
5354: SUM(op_credit_memos_value)) op_credit_memos_value,
5355: SUM(op_credit_memos_count) op_credit_memos_count,
5352: l_limit_currency,sysdate,
5353: l_exchange_rate_type,
5354: SUM(op_credit_memos_value)) op_credit_memos_value,
5355: SUM(op_credit_memos_count) op_credit_memos_count,
5356: gl_currency_api.convert_amount(currency,
5357: l_limit_currency,sysdate,
5358: l_exchange_rate_type,
5359: SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
5360: SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
5396: '209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
5397: '210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
5398: FROM (
5399: SELECT
5400: gl_currency_api.convert_amount(currency,
5401: l_limit_currency,sysdate,
5402: l_exchange_rate_type,
5403: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5404: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5403: SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5404: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5405: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
5406: nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
5407: gl_currency_api.convert_amount(currency,
5408: l_limit_currency,sysdate,
5409: l_exchange_rate_type,
5410: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5411: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5408: l_limit_currency,sysdate,
5409: l_exchange_rate_type,
5410: SUM(NVL(UNRESOLVED_CASH_VALUE,0))) unapplied_cash_amount, -- unapplied case amount
5411: SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
5412: gl_currency_api.convert_amount(currency,
5413: l_limit_currency,sysdate,
5414: l_exchange_rate_type,
5415: SUM(past_due_inv_value)) past_due_inv_value,
5416: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5413: l_limit_currency,sysdate,
5414: l_exchange_rate_type,
5415: SUM(past_due_inv_value)) past_due_inv_value,
5416: SUM(past_due_inv_inst_count) past_due_inv_inst_count,
5417: gl_currency_api.convert_amount(currency,
5418: l_limit_currency,sysdate,
5419: l_exchange_rate_type,
5420: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5421: SUM(disputed_inv_count) disputed_inv_count,
5418: l_limit_currency,sysdate,
5419: l_exchange_rate_type,
5420: SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
5421: SUM(disputed_inv_count) disputed_inv_count,
5422: gl_currency_api.convert_amount(currency,
5423: l_limit_currency,sysdate,
5424: l_exchange_rate_type,
5425: SUM(pending_adj_value)) pending_adj_value,
5426: gl_currency_api.convert_amount(currency,
5422: gl_currency_api.convert_amount(currency,
5423: l_limit_currency,sysdate,
5424: l_exchange_rate_type,
5425: SUM(pending_adj_value)) pending_adj_value,
5426: gl_currency_api.convert_amount(currency,
5427: l_limit_currency,sysdate,
5428: l_exchange_rate_type,
5429: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5430: gl_currency_api.convert_amount(currency,
5426: gl_currency_api.convert_amount(currency,
5427: l_limit_currency,sysdate,
5428: l_exchange_rate_type,
5429: SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
5430: gl_currency_api.convert_amount(currency,
5431: l_limit_currency,sysdate,
5432: l_exchange_rate_type,
5433: SUM(op_invoices_value)) op_invoices_value,
5434: SUM(op_invoices_count) op_invoices_count,
5431: l_limit_currency,sysdate,
5432: l_exchange_rate_type,
5433: SUM(op_invoices_value)) op_invoices_value,
5434: SUM(op_invoices_count) op_invoices_count,
5435: gl_currency_api.convert_amount(currency,
5436: l_limit_currency,sysdate,
5437: l_exchange_rate_type,
5438: SUM(op_debit_memos_value)) op_debit_memos_value,
5439: SUM(op_debit_memos_count) op_debit_memos_count,
5436: l_limit_currency,sysdate,
5437: l_exchange_rate_type,
5438: SUM(op_debit_memos_value)) op_debit_memos_value,
5439: SUM(op_debit_memos_count) op_debit_memos_count,
5440: gl_currency_api.convert_amount(currency,
5441: l_limit_currency,sysdate,
5442: l_exchange_rate_type,
5443: SUM(op_deposits_value)) op_deposits_value,
5444: SUM(op_deposits_count) op_deposits_count,
5441: l_limit_currency,sysdate,
5442: l_exchange_rate_type,
5443: SUM(op_deposits_value)) op_deposits_value,
5444: SUM(op_deposits_count) op_deposits_count,
5445: gl_currency_api.convert_amount(currency,
5446: l_limit_currency,sysdate,
5447: l_exchange_rate_type,
5448: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5449: SUM(op_bills_receivables_count) op_bills_receivables_count,
5446: l_limit_currency,sysdate,
5447: l_exchange_rate_type,
5448: SUM(op_bills_receivables_value)) op_bills_receivables_value,
5449: SUM(op_bills_receivables_count) op_bills_receivables_count,
5450: gl_currency_api.convert_amount(currency,
5451: l_limit_currency,sysdate,
5452: l_exchange_rate_type,
5453: SUM(op_chargeback_value)) op_chargeback_value,
5454: SUM(op_chargeback_count) op_chargeback_count,
5451: l_limit_currency,sysdate,
5452: l_exchange_rate_type,
5453: SUM(op_chargeback_value)) op_chargeback_value,
5454: SUM(op_chargeback_count) op_chargeback_count,
5455: gl_currency_api.convert_amount(currency,
5456: l_limit_currency,sysdate,
5457: l_exchange_rate_type,
5458: SUM(op_credit_memos_value)) op_credit_memos_value,
5459: SUM(op_credit_memos_count) op_credit_memos_count,
5456: l_limit_currency,sysdate,
5457: l_exchange_rate_type,
5458: SUM(op_credit_memos_value)) op_credit_memos_value,
5459: SUM(op_credit_memos_count) op_credit_memos_count,
5460: gl_currency_api.convert_amount(currency,
5461: l_limit_currency,sysdate,
5462: l_exchange_rate_type,
5463: SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
5464: SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
5475: CURSOR c_party_numerator_dso IS
5476: SELECT SUM(dso) dso,
5477: SUM(delinquent_dso) delinquent_dso
5478: FROM (
5479: SELECT gl_currency_api.convert_amount(currency,
5480: l_limit_currency,sysdate,
5481: l_exchange_rate_type,
5482: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5483: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5482: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5483: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5484: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
5485: )*l_certified_dso_days)) dso,
5486: gl_currency_api.convert_amount(currency,
5487: l_limit_currency,sysdate,
5488: l_exchange_rate_type,
5489: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5490: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5523: CURSOR c_account_numerator_dso IS
5524: SELECT SUM(dso) dso,
5525: SUM(delinquent_dso) delinquent_dso
5526: FROM (
5527: SELECT gl_currency_api.convert_amount (currency,
5528: l_limit_currency,sysdate,
5529: l_exchange_rate_type,
5530: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5531: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5530: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5531: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5532: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
5533: )*l_certified_dso_days)) dso,
5534: gl_currency_api.convert_amount(currency,
5535: l_limit_currency,sysdate,
5536: l_exchange_rate_type,
5537: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5538: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5551: CURSOR c_site_numerator_dso IS
5552: SELECT SUM(dso) dso,
5553: SUM(delinquent_dso) delinquent_dso
5554: FROM (
5555: SELECT gl_currency_api.convert_amount(currency,
5556: l_limit_currency,sysdate,
5557: l_exchange_rate_type,
5558: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5559: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5558: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5559: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5560: nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
5561: nvl(UNRESOLVED_CASH_VALUE,0) )*l_certified_dso_days)) dso,
5562: gl_currency_api.convert_amount(currency,
5563: l_limit_currency,sysdate,
5564: l_exchange_rate_type,
5565: (SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
5566: nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
5577: CURSOR c_party_deno_dso IS
5578: SELECT SUM(dso) dso,
5579: SUM(delinquent_dso) delinquent_dso
5580: FROM (
5581: SELECT gl_currency_api.convert_amount(currency,
5582: l_limit_currency,sysdate,
5583: l_exchange_rate_type,
5584: (SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5585: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5585: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5586: nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
5587: nvl(TOTAL_ADJUSTMENTS_VALUE,0))
5588: )) dso,
5589: gl_currency_api.convert_amount(currency,
5590: l_limit_currency,sysdate,
5591: l_exchange_rate_type,
5592: ((SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5593: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5628: CURSOR c_account_deno_dso IS
5629: SELECT SUM(dso) dso,
5630: SUM(delinquent_dso) delinquent_dso
5631: FROM (
5632: SELECT gl_currency_api.convert_amount(currency,
5633: l_limit_currency,sysdate,
5634: l_exchange_rate_type,
5635: (SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5636: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5636: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5637: nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
5638: nvl(TOTAL_ADJUSTMENTS_VALUE,0))
5639: )) dso,
5640: gl_currency_api.convert_amount(currency,
5641: l_limit_currency,sysdate,
5642: l_exchange_rate_type,
5643: (SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5644: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5659: CURSOR c_site_deno_dso IS
5660: SELECT SUM(dso) dso,
5661: SUM(delinquent_dso) delinquent_dso
5662: FROM (
5663: SELECT gl_currency_api.convert_amount(currency,
5664: l_limit_currency,sysdate,
5665: l_exchange_rate_type,
5666: (SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5667: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5667: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5668: nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
5669: nvl(TOTAL_ADJUSTMENTS_VALUE,0))
5670: )) dso,
5671: gl_currency_api.convert_amount(currency,
5672: l_limit_currency,sysdate,
5673: l_exchange_rate_type,
5674: (SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
5675: nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
5855: for i in 1..l_curr_tbl.COUNT
5856: LOOP
5857: IF pg_wf_debug = 'Y'
5858: THEN
5859: IF gl_currency_api.rate_exists(
5860: l_limit_currency,
5861: l_curr_tbl(i).usage_curr_code,
5862: sysdate,
5863: g_conversion_type) = 'Y'
6256: activity within the specified number of days */
6257:
6258: -- Weighted average days paid late (5) and average payment days (4)
6259: SELECT Round(
6260: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6261: SYSDATE, l_exchange_rate_type,
6262: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6263: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6264: SYSDATE,l_exchange_rate_type,
6259: SELECT Round(
6260: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6261: SYSDATE, l_exchange_rate_type,
6262: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6263: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6264: SYSDATE,l_exchange_rate_type,
6265: (NVL(SUM_APP_AMT,0)))),0,1,
6266: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6267: SYSDATE,l_exchange_rate_type,
6262: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6263: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6264: SYSDATE,l_exchange_rate_type,
6265: (NVL(SUM_APP_AMT,0)))),0,1,
6266: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6267: SYSDATE,l_exchange_rate_type,
6268: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6269: Round(
6270: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6266: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6267: SYSDATE,l_exchange_rate_type,
6268: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6269: Round(
6270: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6271: SYSDATE, l_exchange_rate_type,
6272: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6273: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6274: SYSDATE,l_exchange_rate_type,
6269: Round(
6270: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6271: SYSDATE, l_exchange_rate_type,
6272: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6273: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6274: SYSDATE,l_exchange_rate_type,
6275: (NVL(SUM_APP_AMT,0)))),0,1,
6276: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6277: SYSDATE,l_exchange_rate_type,
6272: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6273: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6274: SYSDATE,l_exchange_rate_type,
6275: (NVL(SUM_APP_AMT,0)))),0,1,
6276: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6277: SYSDATE,l_exchange_rate_type,
6278: (NVL(SUM_APP_AMT,0))))),2) wt_average_pmt_days
6279: INTO l_wadpl,
6280: l_apd
6328: activity within the specified number of days */
6329:
6330: -- Weighted average days paid late (5) and average payment days (4)
6331: SELECT Round(
6332: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6333: SYSDATE, l_exchange_rate_type,
6334: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6335: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6336: SYSDATE,l_exchange_rate_type,
6331: SELECT Round(
6332: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6333: SYSDATE, l_exchange_rate_type,
6334: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6335: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6336: SYSDATE,l_exchange_rate_type,
6337: (NVL(SUM_APP_AMT,0)))),0,1,
6338: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6339: SYSDATE,l_exchange_rate_type,
6334: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6335: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6336: SYSDATE,l_exchange_rate_type,
6337: (NVL(SUM_APP_AMT,0)))),0,1,
6338: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6339: SYSDATE,l_exchange_rate_type,
6340: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6341: Round(
6342: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6338: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6339: SYSDATE,l_exchange_rate_type,
6340: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6341: Round(
6342: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6343: SYSDATE, l_exchange_rate_type,
6344: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6345: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6346: SYSDATE,l_exchange_rate_type,
6341: Round(
6342: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6343: SYSDATE, l_exchange_rate_type,
6344: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6345: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6346: SYSDATE,l_exchange_rate_type,
6347: (NVL(SUM_APP_AMT,0)))),0,1,
6348: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6349: SYSDATE,l_exchange_rate_type,
6344: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6345: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6346: SYSDATE,l_exchange_rate_type,
6347: (NVL(SUM_APP_AMT,0)))),0,1,
6348: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6349: SYSDATE,l_exchange_rate_type,
6350: (NVL(SUM_APP_AMT,0))))),2) wt_average_pmt_days
6351: INTO l_wadpl,
6352: l_apd
6379: activity within the specified number of days */
6380:
6381: -- Weighted average days paid late (5) and average payment days (4)
6382: SELECT Round(
6383: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6384: SYSDATE, l_exchange_rate_type,
6385: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6386: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6387: SYSDATE,l_exchange_rate_type,
6382: SELECT Round(
6383: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6384: SYSDATE, l_exchange_rate_type,
6385: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6386: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6387: SYSDATE,l_exchange_rate_type,
6388: (NVL(SUM_APP_AMT,0)))),0,1,
6389: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6390: SYSDATE,l_exchange_rate_type,
6385: (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
6386: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6387: SYSDATE,l_exchange_rate_type,
6388: (NVL(SUM_APP_AMT,0)))),0,1,
6389: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6390: SYSDATE,l_exchange_rate_type,
6391: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6392: Round(
6393: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6389: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6390: SYSDATE,l_exchange_rate_type,
6391: (NVL(SUM_APP_AMT,0))))),2) wt_average_days_paid_late,
6392: Round(
6393: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6394: SYSDATE, l_exchange_rate_type,
6395: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6396: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6397: SYSDATE,l_exchange_rate_type,
6392: Round(
6393: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6394: SYSDATE, l_exchange_rate_type,
6395: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6396: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6397: SYSDATE,l_exchange_rate_type,
6398: (NVL(SUM_APP_AMT,0)))),0,1,
6399: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6400: SYSDATE,l_exchange_rate_type,
6395: (NVL(INV_INST_PMT_DAYS_SUM,0)))) /
6396: Decode(SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6397: SYSDATE,l_exchange_rate_type,
6398: (NVL(SUM_APP_AMT,0)))),0,1,
6399: SUM(gl_currency_api.convert_amount(currency,l_limit_currency,
6400: SYSDATE,l_exchange_rate_type,
6401: (NVL(SUM_APP_AMT,0))))),2) wt_average_pmt_days
6402: INTO l_wadpl,
6403: l_apd