[Home] [Help]
596: -- Begin fix bug #5261855-jypark-06/16/2006-change to based table for performance
597: -- iex_delinquencies del,
598: iex_delinquencies_all del,
599: -- End fix bug #5261855-jypark-06/16/2006-change to based table for performance
600: ar_system_parameters sp
601: WHERE ca.party_id = l_party_id
602: AND ps.customer_id = ca.cust_account_id
603: AND ps.status = 'OP'
604: AND del.payment_schedule_id(+) = ps.payment_schedule_id
673: into l_amount_in_dispute
674: from ra_cm_requests cm
675: where cm.customer_trx_id in (select distinct ps.customer_trx_id
676: from ar_payment_schedules ps,
677: ar_system_parameters parm
678: where ps.customer_id = p_object_id
679: and ps.org_id = parm.org_id
680: and ps.status = 'OP')
681: and cm.status='PENDING_APPROVAL';
687: OPEN c_balance FOR
688: SELECT SUM(NVL(acctd_amount_due_remaining,0))
689: FROM ar_payment_schedules ps,
690: -- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
691: ar_system_parameters parm
692: WHERE customer_id = p_object_id
693: AND ps.org_id = parm.org_id
694: -- End fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
695: AND ps.status = 'OP';
731: -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
732: 0) )
733: )
734: ), 0) /* DSO */
735: FROM ar_system_parameters sp,
736: ar_payment_schedules ps
737: WHERE ps.customer_id = l_cust_account_id
738: AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
739: AND ps.org_id=sp.org_id; --added for moac change
826: -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
827: 0) )
828: )
829: ), 0) /* DSO */
830: FROM ar_system_parameters sp,
831: ar_payment_schedules ps
832: WHERE NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
833: AND ps.payment_schedule_id = l_payment_schedule_id
834: AND ps.org_id=sp.org_id; --added for moac change
872: into l_amount_in_dispute
873: from ra_cm_requests cm
874: where cm.customer_trx_id in (select distinct ps.customer_trx_id
875: FROM ar_payment_schedules ps,
876: ar_system_parameters parm
877: WHERE ps.customer_site_use_id = p_object_id
878: AND ps.org_id = parm.org_id
879: AND ps.status = 'OP')
880: and cm.status='PENDING_APPROVAL';
886: OPEN c_balance FOR
887: SELECT SUM(NVL(acctd_amount_due_remaining,0))
888: FROM ar_payment_schedules ps,
889: -- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
890: ar_system_parameters parm
891: WHERE customer_site_use_id = p_object_id
892: AND ps.org_id = parm.org_id
893: AND status = 'OP';
894: -- SELECT SUM(DECODE(class, 'INV', acctd_amount_due_remaining,
939: -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
940: 0) )
941: )
942: ), 0) /* DSO */
943: FROM ar_system_parameters sp,
944: ar_payment_schedules ps
945: WHERE ps.customer_site_use_id = l_customer_site_use_id
946: AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
947: AND ps.org_id=sp.org_id; --added for moac change
1089: l_pk_where := 'where ca.CUST_ACCOUNT_ID = TRX_SUM.CUST_ACCOUNT_ID ';
1090: l_pk_group := 'group by ca.party_id, TRX_SUM.CUST_ACCOUNT_ID, TRX_SUM.SITE_USE_ID, TRX_SUM.ORG_ID, TRX_SUM.CURRENCY ';
1091:
1092: -- Begin - Bug#5358461 - Andre Araujo - 07/07/06 - This query is not MOAC ready
1093: l_pk_from := l_pk_from || ', ar_system_parameters arsys ';
1094: l_pk_where := l_pk_where || ' AND arsys.org_id = trx_sum.org_id ';
1095: l_pk_group := l_pk_group || ' order by pay_date desc '; -- To do this pay_date was added to the end of l_pk_query
1096: -- End - Bug#5358461 - Andre Araujo - 07/07/06 - This query is not MOAC ready
1097:
1841: ), 0) dso
1842: FROM ar_payment_schedules ps,
1843: hz_cust_accounts ca,
1844: iex_delinquencies del,
1845: ar_system_parameters sp
1846: WHERE ca.party_id = p_party_id
1847: AND ps.customer_id = ca.cust_account_id
1848: AND ps.status = 'OP'
1849: AND del.payment_schedule_id(+) = ps.payment_schedule_id
1900: ), 0) dso
1901: FROM ar_payment_schedules ps,
1902: hz_cust_accounts ca,
1903: iex_delinquencies del,
1904: ar_system_parameters sp
1905: WHERE ca.party_id = p_party_id
1906: AND ps.customer_id = ca.cust_account_id
1907: AND ps.status = 'OP'
1908: AND del.payment_schedule_id(+) = ps.payment_schedule_id