[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select NVL(maximum_days_past_due, 0)
into l_maximum_days_past_due
from OE_CREDIT_CHECK_RULES
where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
select 'Any Past due invoice'
into l_dummy
from AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = ( SELECT CUSTOMER_ID
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id)
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND AMOUNT_DUE_REMAINING > 0
AND DUE_DATE < sysdate - l_maximum_days_past_due;
select NVL(include_tax_flag, 'N')
into l_include_tax_flag
from OE_CREDIT_CHECK_RULES
where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
+ (NVL(unit_selling_price,0)
* (NVL(ordered_quantity,0) )))
INTO l_order_value
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_rec.header_id;
SELECT NVL(SUM(commitment_applied_amount), 0)
INTO l_order_commitment
FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_rec.header_id;
oe_debug_pub.ADD('Selecting the order entry cchk rule');
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.ADD('Selecting the shipping cchk rule');
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
SELECT CUSTOMER_ID
INTO l_invoice_to_cust_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
SELECT NVL(CP.CREDIT_CHECKING, 'N')
, (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
, NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
, NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
INTO l_check_order
, l_credit_limit_test
, p_overall_credit_limit
, p_trx_credit_limit
FROM HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_AMTS CPA
WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
AND CP.SITE_USE_ID = p_header_rec.invoice_to_org_id
AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CPA.SITE_USE_ID = CP.SITE_USE_ID
AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
AND CP.STATUS='A' --14699527
AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
SELECT NVL(CP.CREDIT_CHECKING,'N')
, (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
, NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
, NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
INTO l_check_order
, l_credit_limit_test
, p_overall_credit_limit
, p_trx_credit_limit
FROM HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_AMTS CPA
WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CP.STATUS='A' --14699527
AND CP.SITE_USE_ID IS NULL;
SELECT COUNT(*)
INTO l_credit_check_term
FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
WHERE L.HEADER_ID = p_header_rec.header_id
AND T.TERM_ID = L.PAYMENT_TERM_ID
AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
SELECT OPEN_AR_BALANCE_FLAG
, OPEN_AR_DAYS
, UNINVOICED_ORDERS_FLAG
, ORDERS_ON_HOLD_FLAG
, INCLUDE_TAX_FLAG
, DECODE(SHIPPING_INTERVAL, NULL,
TO_DATE('31/12/4712', 'DD/MM/YYYY'),
SHIPPING_INTERVAL+SYSDATE)
, INCLUDE_PAYMENTS_AT_RISK_FLAG
FROM OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = l_invoice_to_cust_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = l_invoice_to_cust_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT NVL(SUM(CRH.AMOUNT), 0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id;
SELECT NVL(SUM(CRH.AMOUNT), 0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H --ksurendr SQL# 16485169 Removed OE_PAYMENTS
WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id --table from outer query as it is not used.
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon;
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND EXISTS (SELECT 'Hold Exists'
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL );
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.HEADER_ID = p_header_rec.header_id
AND H.HEADER_ID = P.HEADER_ID
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER';
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT NVL(SUM(CRH.AMOUNT),0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id;
SELECT NVL(SUM(CRH.AMOUNT),0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon;
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND H.HEADER_ID <> p_header_rec.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND EXISTS (SELECT 'Hold Exists'
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL );
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.HEADER_ID = p_header_rec.header_id
AND H.HEADER_ID = L.HEADER_ID
-- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
-- <= to_date(l_shipping_horizon, 'DD-MON-YY')
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT CUSTOMER_ID
INTO l_invoice_to_cust_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
Update the exposure by payments that are not thought to be collectable
These payments are in the cash receipts history
*/
IF l_include_risk_flag = 'Y' THEN
IF l_open_ar_days IS NULL THEN
OPEN pay_risk;
Update the exposure by payments that are not thought to be collectable
These payments are in the cash receipts history
*/
IF l_include_risk_flag = 'Y' THEN
IF l_open_ar_days IS NULL THEN
OPEN site_pay_risk;
-- Now update the total exposure value.
l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
select NVL(maximum_days_past_due, 0)
into l_maximum_days_past_due
from OE_CREDIT_CHECK_RULES
where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
select 'Any Past due invoice'
into l_dummy
from AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = p_customer_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND AMOUNT_DUE_REMAINING > 0
AND DUE_DATE < sysdate - l_maximum_days_past_due;
select NVL(include_tax_flag, 'N')
into l_include_tax_flag
from OE_CREDIT_CHECK_RULES
where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
** Only one select to find out Trxn level order total needed
IF p_credit_level = 'SITE' THEN
*/
SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
+ (NVL(unit_selling_price,0)
* (NVL(ordered_quantity,0) )))
INTO l_order_value
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_rec.header_id
AND invoice_to_org_id = p_invoice_to_org_id;
SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
+ (NVL(unit_selling_price,0)
* (NVL(ordered_quantity,0) )))
INTO l_order_value
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_rec.header_id
AND sold_to_org_id = (select organization_id
from oe_sold_to_orgs_v
where customer_id = p_customer_id);
SELECT NVL(SUM(P.commitment_applied_amount), 0)
INTO l_order_commitment
FROM OE_PAYMENTS P, OE_ORDER_LINES L
WHERE P.HEADER_ID = p_header_rec.header_id
AND L.LINE_ID = P.LINE_ID
AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id;
SELECT customer_id
INTO l_customer_id
FROM oe_invoice_to_orgs_v
WHERE ORGANIZATION_ID = p_invoice_to_org_id;
oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.ADD('Line Level: Selecting the shipping cchk rule');
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
SELECT NVL(CP.CREDIT_CHECKING, 'N')
, (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
, NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
, NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
INTO l_check_order
, l_credit_limit_test
, p_overall_credit_limit
, p_trx_credit_limit
FROM HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_AMTS CPA
WHERE CP.CUST_ACCOUNT_ID = p_customer_id
AND CP.SITE_USE_ID = p_invoice_to_org_id
AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CPA.SITE_USE_ID = CP.SITE_USE_ID
AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
AND CP.STATUS='A' --14699527
AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
SELECT NVL(CP.CREDIT_CHECKING,'N')
, (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
, NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
, NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
INTO l_check_order
, l_credit_limit_test
, p_overall_credit_limit
, p_trx_credit_limit
FROM HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_AMTS CPA
WHERE CP.CUST_ACCOUNT_ID = p_customer_id
AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CP.STATUS='A' --14699527
AND CP.SITE_USE_ID IS NULL;
SELECT COUNT(*)
INTO l_credit_check_term
FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
WHERE L.header_id = p_header_rec.header_id
AND L.invoice_to_org_id = p_invoice_to_org_id
AND L.PAYMENT_TERM_ID = T.TERM_ID
AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
SELECT OPEN_AR_BALANCE_FLAG
, OPEN_AR_DAYS
, UNINVOICED_ORDERS_FLAG
, ORDERS_ON_HOLD_FLAG
, INCLUDE_TAX_FLAG
, DECODE(SHIPPING_INTERVAL, NULL,
TO_DATE('31/12/4712', 'DD/MM/YYYY'),
SHIPPING_INTERVAL+SYSDATE)
, INCLUDE_PAYMENTS_AT_RISK_FLAG
FROM OE_CREDIT_CHECK_RULES
WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = p_customer_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = p_customer_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT NVL(SUM(CRH.AMOUNT), 0)
FROM AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = p_customer_id;
SELECT NVL(SUM(CRH.AMOUNT), 0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = p_customer_id
AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.SOLD_TO_ORG_ID = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.SOLD_TO_ORG_ID = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.SOLD_TO_ORG_ID = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon;
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.SOLD_TO_ORG_ID = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
WHERE H.HEADER_ID = p_header_rec.header_id
AND H.HEADER_ID = P.HEADER_ID
AND L.HEADER_ID = H.HEADER_ID
AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
AND L.LINE_ID = P.LINE_ID
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.HEADER_ID = p_header_rec.header_id
AND H.HEADER_ID = L.HEADER_ID
AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
-- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
-- <= to_date(l_shipping_horizon, 'DD-MON-YY')
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT NVL(SUM(CRH.AMOUNT),0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = p_customer_id
AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id;
SELECT NVL(SUM(CRH.AMOUNT),0)
FROM AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <>
DECODE(CRH.FACTOR_FLAG,'Y',
'RISK_ELIMINATED','CLEARED')
AND CRH.STATUS <> 'REVERSED'
AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
AND CR.PAY_FROM_CUSTOMER = p_customer_id
AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
AND SYSDATE - TRX_DATE > l_open_ar_days;
SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
*NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = L.HEADER_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = P.HEADER_ID
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND H.BOOKED_FLAG = 'Y'
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
AND H.HEADER_ID = P.HEADER_ID
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
<= l_shipping_horizon
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID <> p_header_rec.header_id
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
*/
AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
OR (h.payment_type_code = 'CREDIT_CARD'
AND NOT EXISTS
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
)
AND EXISTS (SELECT 1
FROM OE_ORDER_HOLDS OH
WHERE H.HEADER_ID = OH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL);
SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
*NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
WHERE H.HEADER_ID = p_header_rec.header_id
AND H.HEADER_ID = L.HEADER_ID
AND L.invoice_to_org_id = p_invoice_to_org_id
-- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
-- <= to_date(l_shipping_horizon, 'DD-MON-YY')
AND NVL(L.INVOICED_QUANTITY,0) = 0
AND L.OPEN_FLAG ='Y'
AND L.LINE_CATEGORY_CODE ='ORDER'
/*
AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
(H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
(Select 'valid auth code'
From oe_payments op,
iby_trxn_ext_auths_v ite
Where op.header_id = h.header_id
And op.trxn_extension_id = ite.trxn_extension_id
And authorization_status = 0
And effective_auth_amount > 0)
)
);
* If the include payments at risk flag is set to yes, Update the *
* exposure by payments that are not thought to be collectable *
* These payments are in the cash receipts history *
*********************************************************************/
IF l_include_risk_flag = 'Y' THEN
IF l_open_ar_days IS NULL THEN
OPEN pay_risk;
-- Now update the total exposure value.
l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);