352:
353: CURSOR C_CREDIT_LIMIT IS
354: SELECT H.CREDIT_LIMIT,
355: H.LAST_UPDATE_DATE
356: FROM AR_CREDIT_HISTORIES H
357: WHERE H.CUSTOMER_ID = CUSTOMER_ID_1
358: AND (H.SITE_USE_ID = site_use_id_1
359: OR
360: to_char(site_use_id) is null
360: to_char(site_use_id) is null
361: OR
362: ( H.SITE_USE_ID IS NULL
363: AND NOT EXISTS (SELECT 1
364: FROM AR_CREDIT_HISTORIES H2
365: WHERE H2.SITE_USE_ID =
366: site_use_id_1
367: AND H2.CUSTOMER_ID =
368: customer_id_1
2582: l_dummy NUMBER (1);
2583:
2584: CURSOR C_HOLD IS
2585:
2586: SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
2587: AR_CREDIT_HISTORIES.HOLD_DATE,
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2583:
2584: CURSOR C_HOLD IS
2585:
2586: SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
2587: AR_CREDIT_HISTORIES.HOLD_DATE,
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2584: CURSOR C_HOLD IS
2585:
2586: SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
2587: AR_CREDIT_HISTORIES.HOLD_DATE,
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2586: SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
2587: AR_CREDIT_HISTORIES.HOLD_DATE,
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2593: WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2587: AR_CREDIT_HISTORIES.HOLD_DATE,
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2593: WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2595: or
2588: ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2593: WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2595: or
2596: site_use_id_1 is null
2589:
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2593: WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2595: or
2596: site_use_id_1 is null
2597: or
2590: FROM AR_CREDIT_HISTORIES
2591: --WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
2592: --and (ar_credit_histories.site_use_id = site_use_id
2593: WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2595: or
2596: site_use_id_1 is null
2597: or
2598: ( ar_credit_histories.site_use_id is null
2594: and (ar_credit_histories.site_use_id = site_use_id_1
2595: or
2596: site_use_id_1 is null
2597: or
2598: ( ar_credit_histories.site_use_id is null
2599: and not exists (select 1
2600: from ar_credit_histories h2
2601: -- where h2.site_use_id = site_use_id
2602: where h2.site_use_id = site_use_id_1
2596: site_use_id_1 is null
2597: or
2598: ( ar_credit_histories.site_use_id is null
2599: and not exists (select 1
2600: from ar_credit_histories h2
2601: -- where h2.site_use_id = site_use_id
2602: where h2.site_use_id = site_use_id_1
2603: -- and h2.customer_id =
2604: -- customer_id
2606: customer_id_1
2607: )
2608: )
2609: )
2610: AND AR_CREDIT_HISTORIES.ON_HOLD = 'Y'
2611: ORDER BY AR_CREDIT_HISTORIES.HOLD_DATE DESC,
2612: AR_CREDIT_HISTORIES.CREDIT_HISTORY_ID DESC
2613: ;
2614:
2607: )
2608: )
2609: )
2610: AND AR_CREDIT_HISTORIES.ON_HOLD = 'Y'
2611: ORDER BY AR_CREDIT_HISTORIES.HOLD_DATE DESC,
2612: AR_CREDIT_HISTORIES.CREDIT_HISTORY_ID DESC
2613: ;
2614:
2615: BEGIN
2608: )
2609: )
2610: AND AR_CREDIT_HISTORIES.ON_HOLD = 'Y'
2611: ORDER BY AR_CREDIT_HISTORIES.HOLD_DATE DESC,
2612: AR_CREDIT_HISTORIES.CREDIT_HISTORY_ID DESC
2613: ;
2614:
2615: BEGIN
2616: