[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT su.site_use_id
FROM hz_cust_site_uses su ,
hz_cust_acct_sites_all cas
WHERE cas.cust_account_id = p_cust_account_id
AND su.site_use_code = 'DRAWEE'
AND su.cust_acct_site_id = cas.cust_acct_site_id ; /* MOAC_SQL_CHANGE */
SELECT cust_acct_site_id
INTO l_cust_acct_site_id
FROM hz_cust_site_uses su
WHERE su.site_use_id = p_site_use_id ;
SELECT su.site_use_id
INTO l_site_use_id
FROM hz_cust_acct_sites cas
, hz_cust_site_uses_all su
WHERE cas.cust_acct_site_id = su.cust_acct_site_id
AND su.cust_acct_site_id = l_cust_acct_site_id
AND su.site_use_code = 'DRAWEE' ; /* MOAC_SQL_CHANGE */
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cu.credit_usage_rule_set_id
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_hierarchy_nodes hn
WHERE cp.party_id = hn.parent_id
AND cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
-- AND NVL(cp.credit_checking,'Y') = 'Y' --bug 4967828
AND hn.child_id = p_party_id
AND hn.parent_object_type = 'ORGANIZATION'
and hn.parent_table_name = 'HZ_PARTIES'
and hn.child_object_type = 'ORGANIZATION'
and hn.effective_start_date <= sysdate
and hn.effective_end_date >= SYSDATE
and hn.hierarchy_type = OE_CREDIT_CHECK_UTIL.G_hierarchy_type
ORDER BY hn.LEVEL_NUMBER DESC ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cu.credit_usage_rule_set_id
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_hierarchy_nodes hn
WHERE cp.party_id = hn.parent_id
AND cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND cur.include_all_flag = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
)
-- AND NVL(cp.credit_checking,'Y') = 'Y' --bug 4967828
AND hn.child_id = p_party_id
AND hn.parent_object_type = 'ORGANIZATION'
and hn.parent_table_name = 'HZ_PARTIES'
and hn.child_object_type = 'ORGANIZATION'
and hn.effective_start_date <= sysdate
and hn.effective_end_date >= SYSDATE
and hn.hierarchy_type = OE_CREDIT_CHECK_UTIL.G_hierarchy_type
ORDER BY hn.LEVEL_NUMBER DESC ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_hierarchy_nodes hn
WHERE cp.party_id = hn.parent_id
AND cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.currency_code = p_trx_curr_code
-- AND cp.credit_checking = 'Y' --bug 4967828
AND hn.child_id = p_party_id
AND hn.parent_object_type = 'ORGANIZATION'
and hn.parent_table_name = 'HZ_PARTIES'
and hn.child_object_type = 'ORGANIZATION'
and hn.effective_start_date
<= sysdate
and hn.effective_end_date
>= SYSDATE
and hn.hierarchy_type = OE_CREDIT_CHECK_UTIL.G_hierarchy_type
ORDER BY hn.LEVEL_NUMBER DESC ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
WHERE cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.party_id = p_party_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.currency_code = p_trx_curr_code ; --bug 4967828
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cu.credit_usage_rule_set_id
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.party_id = p_party_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N' ; --bug 4967828
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4967828
, cu.credit_usage_rule_set_id
, cp.party_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.cust_account_id = -1
AND cp.site_use_id IS NULL
AND cp.party_id = p_party_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
AND cur.include_all_flag = 'Y'
-- AND NVL(cp.credit_checking,'Y') = 'Y' --bug 4967828
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
);
SELECT NVL(curs.global_exposure_flag,'N') global_exposure_flag
, curs.credit_usage_rule_set_id credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rule_sets_B curs
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cpa.currency_code = p_limit_curr_code
AND curs.credit_usage_rule_set_id = cu.credit_usage_rule_set_id
AND curs.global_exposure_flag = 'Y' ;
SELECT NVL(curs.global_exposure_flag,'N') global_exposure_flag
, curs.credit_usage_rule_set_id credit_usage_rule_set_id
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rule_sets_B curs
WHERE cp.organization_id = p_entity_id
AND cp.credit_profile_id = cpa.credit_profile_id
AND cpa.credit_profile_amt_id = cu.credit_profile_amt_id
AND cu.credit_usage_rule_set_id = curs.credit_usage_rule_set_id
AND cp.enable_flag = 'Y'
AND curs.global_exposure_flag = 'Y'
AND cpa.currency_code = p_limit_curr_code
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ))
AND
TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
);
SELECT
NVL(global_exposure_flag,'N')
INTO
l_global_exposure_flag
FROM
HZ_CREDIT_USAGE_RULE_SETS_B
WHERE
credit_usage_rule_set_id = p_credit_usage_rule_set_id ;
SELECT
currency_code
INTO
l_gl_currency
FROM
GL_sets_of_books
WHERE set_of_books_id = l_sob_id ;
SELECT
category_set_id
INTO
l_category_set_id
FROM
MTL_DEFAULT_CATEGORY_SEts
WHERE functional_area_id = 7 ;
SELECT
credit_checking
INTO
l_credit_checking
FROM
HZ_CUSTOMER_PROFILES
WHERE cust_account_id = p_entity_id
AND site_use_id IS NULL ;
SELECT
credit_checking
INTO
l_credit_checking
FROM
HZ_CUSTOMER_PROFILES
WHERE site_use_id = p_entity_id ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 5071518
, 0 credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.currency_code = p_trx_curr_code ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4582292
, 0 credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
WHERE cp.site_use_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.currency_code = p_trx_curr_code
-- AND cp.credit_checking = 'Y' bug 4582292
AND NVL(TRUNC(cpa.expiration_date)
, TRUNC(SYSDATE) ) <= TRUNC(SYSDATE);
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 5071518
, curs.credit_usage_rule_set_id
, cu.credit_usage_id
, NVL(curs.global_exposure_flag,'N') global_exposure_flag
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_credit_usage_rule_sets_B curs
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cu.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N' ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 5071518
, cu.credit_usage_rule_set_id
, NVL(curs.global_exposure_flag,'N') global_exposure_flag
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_credit_usage_rule_sets_B curs
WHERE cp.cust_account_id = p_entity_id
AND cp.site_use_id IS NULL
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cu.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
AND cur.include_all_flag = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
) ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4582292
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.site_use_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
<= TRUNC(SYSDATE)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
-- AND cp.credit_checking = 'Y' bug 4582292
AND NVL(cur.exclude_flag,'N') = 'N';
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking credit_checking --bug 4582292
, cu.credit_usage_rule_set_id
FROM hz_customer_profiles cp
, hz_cust_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.site_use_id = p_entity_id
AND cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
) <= TRUNC(SYSDATE)
-- AND cp.credit_checking = 'Y' bug 4582292
AND cur.include_all_flag = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
);
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100+nvl(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
WHERE cp.organization_id = G_ORG_ID
AND cp.credit_profile_id = cpa.credit_profile_id
AND cpa.currency_code = p_trx_curr_code
AND cp.enable_flag = 'Y'
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE))
AND
TRUNC( NVL(cp.effective_date_to, SYSDATE ))
);
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
, NVL(curs.global_exposure_flag,'N') global_exposure_flag
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_credit_usage_rule_sets_B curs
WHERE cp.organization_id = G_ORG_ID
AND cp.credit_profile_id = cpa.credit_profile_id
AND cpa.credit_profile_amt_id = cu.credit_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cu.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND cp.enable_flag = 'Y'
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ))
AND
TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
AND cp.credit_checking = 'Y' ;
SELECT cpa.currency_code currency_code
, cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
overall_limit
, cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
trx_limit
, cp.credit_checking
, cu.credit_usage_rule_set_id
, NVL(curs.global_exposure_flag,'N') global_exposure_flag
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
, hz_credit_usage_rule_sets_B curs
WHERE cp.organization_id = G_ORG_ID
AND cp.credit_profile_id = cpa.credit_profile_id
AND cpa.credit_profile_amt_id = cu.credit_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cu.credit_usage_rule_set_id
AND curs.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND cp.enable_flag = 'Y'
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ))
AND
TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
)
AND cur.include_all_flag = 'Y'
AND cp.credit_checking = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
);
SELECT cpa.currency_code limit_curr_code
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
WHERE cp.item_category_id = p_category_id
AND cp.credit_profile_id = cpa.credit_profile_id
AND cpa.currency_code = p_trx_curr_code
AND cp.enable_flag = 'Y'
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ) )
AND TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
) ;
SELECT cpa.currency_code limit_curr_code
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cu.credit_usage_rule_set_id
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.credit_profile_id = cpa.credit_profile_id
AND cpa.credit_profile_amt_id = cu.credit_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND cp.enable_flag = 'Y'
AND cp.item_category_id = p_category_id
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ) )
AND TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
)
AND NVL (cur.include_all_flag, 'N') = 'N'
AND cur.usage_type = 'CURRENCY'
AND cur.user_code = p_trx_curr_code
AND NVL(cur.exclude_flag,'N') = 'N'
ORDER BY cpa.overall_credit_limit;
SELECT cpa.currency_code limit_curr_code
, cpa.trx_credit_limit * ((100+nvl(cp.tolerance,0))/100)
trx_limit
, cu.credit_usage_rule_set_id
FROM hz_credit_profiles cp
, hz_credit_profile_amts cpa
, hz_credit_usages cu
, hz_credit_usage_rules cur
WHERE cp.credit_profile_id = cpa.credit_profile_id
AND cpa.credit_profile_amt_id = cu.credit_profile_amt_id
AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
AND cp.enable_flag = 'Y'
AND cp.item_category_id = p_category_id
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ) )
AND TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
)
AND cur.include_all_flag = 'Y'
AND NOT EXISTS ( SELECT 'EXCLUDE'
FROM hz_credit_usage_rules cur2
WHERE cu.credit_usage_rule_set_id
= cur2.credit_usage_rule_set_id
AND cur2.exclude_flag = 'Y'
AND cur2.usage_type = 'CURRENCY'
AND cur2.user_code = p_trx_curr_code
)
ORDER BY cpa.overall_credit_limit;
CURSOR C_SELECT_CTG_CREDIT_PROFILE IS
SELECT
cp.credit_profile_id
FROM
hz_credit_profiles cp
WHERE cp.item_category_id is NOT NULL
AND cp.enable_flag = 'Y'
AND ( TRUNC(SYSDATE) BETWEEN
TRUNC( NVL(cp.effective_date_from, SYSDATE ) )
AND TRUNC( NVL(cp.effective_date_to, SYSDATE ) )
) ;
l_SELECT_CTG_CREDIT_PROFILE C_SELECT_CTG_CREDIT_PROFILE%ROWTYPE ;
CURSOR C_SELECT_LINES_CSR IS
SELECT
ln.line_id line_id
, ln.inventory_item_id item_id
, ctg.category_id category_id
, ln.ordered_quantity ordered_quantity
, ln.unit_selling_price selling_price
, ln.tax_value tax_value
FROM
OE_ORDER_LINES ln
, mtl_item_categories ctg
, ra_terms_b trm
WHERE ln.header_id = p_header_id
AND ln.invoice_to_org_id = NVL(l_site_use_id, ln.invoice_to_org_id )
AND ln.open_flag = 'Y'
AND (ln.invoiced_quantity IS NULL OR ln.invoiced_quantity = 0)
AND ctg.category_set_id = G_category_set_id
AND ctg.organization_id = G_ORG_ID
AND ctg.inventory_item_id = ln.inventory_item_id
AND trm.term_id = ln.payment_term_id
AND ln.line_category_code = 'ORDER'
AND trm.credit_check_flag = 'Y'
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_headers_all h
WHERE pt.credit_check_flag = 'Y'
AND h.header_id = p_header_id
AND ln.header_id = h.header_id
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(ln.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
ln.payment_type_code)
);
L_SELECT_LINES_VAL C_SELECT_LINES_CSR%ROWTYPE ;
l_tmp_lines_tbl.DELETE ;
l_tmp_category_tbl.DELETE ;
OE_DEBUG_PUB.ADD(' Cusror select to check CTG profile exist ');
OPEN C_SELECT_CTG_CREDIT_PROFILE ;
FETCH C_SELECT_CTG_CREDIT_PROFILE
INTO l_SELECT_CTG_CREDIT_PROFILE ;
IF C_SELECT_CTG_CREDIT_PROFILE%NOTFOUND
THEN
l_ctg_profile_exist := 'N' ;
l_SELECT_CTG_CREDIT_PROFILE.credit_profile_id ;
CLOSE C_SELECT_CTG_CREDIT_PROFILE ;
FOR L_SELECT_LINES_VAL IN C_SELECT_LINES_CSR
LOOP
l_line_id := L_SELECT_LINES_VAL.line_id ;
l_item_id := L_SELECT_LINES_VAL.item_id ;
l_category_id := L_SELECT_LINES_VAL.category_id ;
l_selling_price := L_SELECT_LINES_VAL.selling_price ;
l_tax_value := L_SELECT_LINES_VAL.tax_value ;
l_ordered_quantity := L_SELECT_LINES_VAL.ordered_quantity ;
SELECT credit_usage_rule_set_id
FROM hz_credit_usages usg,
hz_customer_profiles prf,
hz_cust_profile_amts amt
WHERE usg.cust_acct_profile_amt_id = amt.cust_acct_profile_amt_id
AND prf.cust_account_profile_id = amt.cust_account_profile_id
AND amt.currency_code = p_limit_curr_code
AND prf.cust_account_id = -1
AND prf.site_use_id IS NULL
AND prf.party_id = p_entity_id ;
SELECT credit_usage_rule_set_id
FROM hz_credit_usages usg,
hz_customer_profiles prf,
hz_cust_profile_amts amt
WHERE usg.cust_acct_profile_amt_id = amt.cust_acct_profile_amt_id
AND prf.cust_account_profile_id = amt.cust_account_profile_id
AND amt.currency_code = p_limit_curr_code
AND prf.cust_account_id = p_entity_id
AND prf.site_use_id IS NULL;
SELECT credit_usage_rule_set_id
FROM hz_credit_usages usg,
hz_customer_profiles prf,
hz_cust_profile_amts amt
WHERE usg.cust_acct_profile_amt_id = amt.cust_acct_profile_amt_id
AND prf.cust_account_profile_id = amt.cust_account_profile_id
AND amt.currency_code = p_limit_curr_code
AND prf.site_use_id = p_entity_id ;
SELECT credit_usage_rule_set_id
FROM hz_credit_usages usg,
hz_credit_profiles prf,
hz_credit_profile_amts amt
WHERE usg.credit_profile_amt_id = amt.credit_profile_amt_id
AND prf.credit_profile_id = amt.credit_profile_id
AND amt.currency_code = p_limit_curr_code
AND prf.organization_id = G_ORG_ID ;
SELECT 'X'
FROM hz_credit_usage_rules
WHERE credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND usage_type = 'CURRENCY'
AND include_all_flag = 'Y';
SELECT user_code
FROM hz_credit_usage_rules cur
WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND cur.usage_type = 'CURRENCY'
AND cur.user_code IS NOT NULL
AND NVL(cur.exclude_flag,'N') = 'N';
SELECT user_code
FROM hz_credit_usage_rules cur
WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
AND cur.usage_type = 'CURRENCY'
AND cur.user_code IS NOT NULL
AND cur.exclude_flag = 'Y';
SELECT soh.transactional_curr_code user_code
FROM oe_order_headers soh
, hz_cust_acct_sites_all cas
, hz_cust_site_uses_all su
WHERE soh.org_id = su.org_id
AND soh.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_entity_id
GROUP BY transactional_curr_code
UNION
SELECT pay.invoice_currency_code user_code
FROM ar_payment_schedules pay
, hz_cust_acct_sites_all cas
, hz_cust_site_uses_all su
WHERE pay.org_id = su.org_id
AND pay.customer_site_use_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_entity_id
GROUP BY invoice_currency_code
UNION
SELECT exs.currency_code
FROM oe_credit_summaries exs
WHERE exs.balance_type = 18
AND exs.cust_account_id = p_entity_id
AND NVL(exs.org_id,-99) = G_ORG_ID; /* MOAC_SQL_CHANGE */
SELECT soh.transactional_curr_code user_code
FROM oe_order_headers_ALL soh
, hz_cust_acct_sites_ALL cas
, hz_cust_site_uses_ALL su
WHERE soh.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_entity_id
GROUP BY transactional_curr_code
UNION
SELECT pay.invoice_currency_code user_code
FROM ar_payment_schedules_ALL pay
, hz_cust_acct_sites_ALL cas
, hz_cust_site_uses_ALL su
WHERE pay.customer_site_use_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_entity_id
GROUP BY invoice_currency_code
UNION
SELECT exs.currency_code
FROM oe_credit_summaries exs
WHERE exs.balance_type = 18
AND exs.cust_account_id = p_entity_id ;
SELECT soh.transactional_curr_code user_code
FROM oe_order_headers soh
WHERE soh.invoice_to_org_id = p_entity_id
GROUP BY transactional_curr_code
UNION
SELECT pay.invoice_currency_code user_code
FROM ar_payment_schedules pay
WHERE pay.customer_site_use_id = p_entity_id
GROUP BY invoice_currency_code
UNION
SELECT exs.currency_code
FROM oe_credit_summaries exs
WHERE balance_type = 18
AND site_use_id = p_entity_id ;
SELECT
'Y'
FROM
hz_hierarchy_nodes hn
WHERE hn.parent_id = p_entity_id
AND hn.parent_object_type = 'ORGANIZATION'
and hn.parent_table_name = 'HZ_PARTIES'
and hn.child_object_type = 'ORGANIZATION'
and hn.effective_start_date <= sysdatE
and hn.effective_end_date >= SYSDATE
and hn.hierarchy_type
= OE_CREDIT_CHECK_UTIL.G_hierarchy_type ;
SELECT distinct ( currency_code) user_code
FROM oe_credit_summaries
WHERE party_id = p_entity_id
AND bucket_duration = OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH ;
SELECT distinct ( oes.currency_code) user_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE hn.parent_id = p_entity_id
AND hn.parent_object_type = 'ORGANIZATION'
and hn.parent_table_name = 'HZ_PARTIES'
and hn.child_object_type = 'ORGANIZATION'
and hn.effective_start_date <= sysdate
and hn.effective_end_date >= SYSDATE
and hn.hierarchy_type
= OE_CREDIT_CHECK_UTIL.G_hierarchy_type
AND oes.party_id = hn.child_id
AND oes.bucket_duration = OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH ;
SELECT
org_id
, master_organization_id
, customer_relationships_flag
INTO
x_system_parameter_rec.org_id
, x_system_parameter_rec.master_organization_id
, x_system_parameter_rec.customer_relationships_flag
FROM
OE_system_parameters_all
WHERE NVL(org_id,-99) = G_ORG_ID;
IF p_calling_action in ('BOOKING','BOOKING_INLINE','AUTO','UPDATE')
THEN
IF G_debug_flag = 'Y'
THEN
OE_DEBUG_PUB.ADD('Selecting the order entry credit check rule');
SELECT ENTRY_CREDIT_CHECK_RULE_ID
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_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 credit check rule');
SELECT SHIPPING_CREDIT_CHECK_RULE_ID
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_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('Selecting the picking credit check rule');
SELECT PICKING_CREDIT_CHECK_RULE_ID
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT NVL(PICKING_CREDIT_CHECK_RULE_ID, -1)
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_order_type_id
AND PICKING_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 packing credit check rule');
SELECT PACKING_CREDIT_CHECK_RULE_ID
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_order_type_id;
SELECT NVL(PACKING_CREDIT_CHECK_RULE_ID, -1)
INTO x_credit_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_order_type_id
AND PACKING_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(' Select for ID ' || p_credit_check_rule_id );
SELECT
credit_check_rule_id
, name
, failure_result_code
, open_ar_balance_flag
, uninvoiced_orders_flag
, orders_on_hold_flag
, shipping_interval
, open_ar_days
, start_date_active
, end_date_active
, include_payments_at_risk_flag
, NVL(include_tax_flag,'N')
, maximum_days_past_due
, NVL(QUICK_CR_CHECK_FLAG,'N')
, NVL(incl_freight_charges_flag,'N')
, DECODE( shipping_interval, NULL,
TO_DATE('31/12/4712','DD/MM/YYYY'), shipping_interval + SYSDATE
)
, NVL(credit_check_level_code,'ORDER')
, NVL(credit_hold_level_code,'ORDER')
, conversion_type
, NVL(check_item_categories_flag,'N')
, NVL(send_hold_notifications_flag,'N')
, days_honor_manual_release
, NVL(include_external_exposure_flag, 'N')
, NVL(include_returns_flag, 'N')
INTO
x_credit_check_rules_rec.credit_check_rule_id
, x_credit_check_rules_rec.name
, x_credit_check_rules_rec.failure_result_code
, x_credit_check_rules_rec.open_ar_balance_flag
, x_credit_check_rules_rec.uninvoiced_orders_flag
, x_credit_check_rules_rec.orders_on_hold_flag
, x_credit_check_rules_rec.shipping_interval
, x_credit_check_rules_rec.open_ar_days
, x_credit_check_rules_rec.start_date_active
, x_credit_check_rules_rec.end_date_active
, x_credit_check_rules_rec.include_payments_at_risk_flag
, x_credit_check_rules_rec.include_tax_flag
, x_credit_check_rules_rec.maximum_days_past_due
, x_credit_check_rules_rec.QUICK_CR_CHECK_FLAG
, x_credit_check_rules_rec.incl_freight_charges_flag
, x_credit_check_rules_rec.shipping_horizon
, x_credit_check_rules_rec.credit_check_level_code
, x_credit_check_rules_rec.credit_hold_level_code
, x_credit_check_rules_rec.conversion_type
, x_credit_check_rules_rec.check_item_categories_flag
, x_credit_check_rules_rec.send_hold_notifications_flag
, x_credit_check_rules_rec.days_honor_manual_release
, x_credit_check_rules_rec.include_external_exposure_flag
, x_credit_check_rules_rec.include_returns_flag
FROM
OE_Credit_Check_rules
WHERE credit_check_rule_id = p_credit_check_rule_id ;
OE_DEBUG_PUB.ADD(' Select user_conversion_type ');
SELECT
user_conversion_type
INTO
x_credit_check_rules_rec.user_conversion_type
FROM
GL_DAILY_CONVERSION_TYPES
WHERE conversion_type = x_credit_check_rules_rec.conversion_type ;
OE_DEBUG_PUB.ADD('Line - Customer level select,no returns ');
SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(l.tax_value,0), 0 )
+ ( l.unit_selling_price * l.ordered_quantity )
)
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
HZ_CUST_SITE_USES_ALL su,
HZ_CUST_ACCT_SITES_ALL cas
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = su.site_use_id
AND su.CUST_ACCT_SITE_ID = cas.CUST_ACCT_SITE_ID
AND cas.cust_account_id = p_customer_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND l.line_category_code = 'ORDER'
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('Line - Customer level select,with returns ');
SELECT
SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0), 0 )
+ ( l.unit_selling_price *
(DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,
l.ordered_quantity) )
))
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
HZ_CUST_SITE_USES_ALL su,
HZ_CUST_ACCT_SITES_ALL cas
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = su.site_use_id
AND su.CUST_ACCT_SITE_ID = cas.CUST_ACCT_SITE_ID
AND cas.cust_account_id = p_customer_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('Customer level commitment select ');
SELECT NVL(SUM(P.commitment_applied_amount), 0)
INTO l_commitment
FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L ,
HZ_CUST_SITE_USES_ALL su,
HZ_CUST_ACCT_SITES_ALL cas,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = su.site_use_id
AND su.CUST_ACCT_SITE_ID = cas.CUST_ACCT_SITE_ID
AND cas.cust_account_id = p_customer_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND p.header_id = p_header_id
AND p.line_id = l.line_id
AND l.line_category_code = 'ORDER'
AND p.header_id = h.header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
HZ_CUST_SITE_USES_ALL su,
HZ_CUST_ACCT_SITES_ALL cas,
oe_price_adjustments p
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND l.invoice_to_org_id = su.site_use_id
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N' )
AND su.CUST_ACCT_SITE_ID = cas.CUST_ACCT_SITE_ID
AND cas.cust_account_id = p_customer_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND l.line_category_code = 'ORDER'
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
HZ_CUST_SITE_USES_ALL su,
HZ_CUST_ACCT_SITES_ALL cas,
oe_price_adjustments p
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND l.invoice_to_org_id = su.site_use_id
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N' )
AND su.CUST_ACCT_SITE_ID = cas.CUST_ACCT_SITE_ID
AND cas.cust_account_id = p_customer_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('Line - Site level select ');
SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(l.tax_value,0), 0 )
+ ( l.unit_selling_price * l.ordered_quantity )
)
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = p_site_use_id
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('Line - Site level select,including returns ');
SELECT
SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0), 0 )
+ (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
(-1)*l.ordered_quantity,l.ordered_quantity))
)
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = p_site_use_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT NVL(SUM(P.commitment_applied_amount), 0)
INTO l_commitment
FROM OE_PAYMENTS P,
OE_ORDER_HEADERS_ALL H,
OE_ORDER_LINES L,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.invoice_to_org_id = p_site_use_id
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND p.header_id = p_header_id
AND l.line_id = p.line_id
AND p.header_id = h.header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
oe_price_adjustments p
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND l.invoice_to_org_id = p_site_use_id
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
oe_price_adjustments p
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND l.invoice_to_org_id = p_site_use_id
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('In Order header level select ');
SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(l.tax_value,0), 0 )
+ ( l.unit_selling_price * l.ordered_quantity )
)
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
OE_DEBUG_PUB.ADD('In Order header level select, with returns ');
SELECT
SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0), 0 )
+ (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
(-1)*l.ordered_quantity,l.ordered_quantity))
)
INTO l_order_value
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT NVL(SUM(P.commitment_applied_amount), 0)
INTO l_commitment
FROM oe_order_lines l,
oe_order_headers_all h,
ra_terms_b t,
oe_payments p
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.header_id = l.header_id
AND p.header_id = p_header_id
AND p.header_id = h.header_id
AND p.line_id = l.line_id
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code,'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM(P.prepaid_amount)
INTO l_prepayment
FROM oe_payments p
WHERE p.header_id = p_header_id
AND p.line_id IS NULL;
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights_hdr_1
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code,'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM(DECODE(P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
INTO l_freights_hdr_2
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.header_id = p_header_id
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N');
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
INTO l_freights_hdr_1
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, ra_terms_b t
WHERE h.header_id = p_header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND p.line_id = l.line_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N' )
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND t.term_id = l.payment_term_id
AND t.credit_check_flag = 'Y'
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM(DECODE(P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
INTO l_freights_hdr_2
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.header_id = p_header_id
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N');
SELECT 'Y'
INTO x_exist_flag
FROM ar_payment_schedules
WHERE customer_id = p_customer_id
AND ( invoice_currency_code
= p_usage_curr(i).usage_curr_code
OR p_include_all_flag = 'Y' )
AND NVL(receipt_confirmed_flag, 'Y') = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND amount_due_remaining > 0
AND due_date <
sysdate - p_credit_check_rule_rec.maximum_days_past_due;
SELECT 'Y'
INTO x_exist_flag
FROM ar_payment_schedules
WHERE customer_site_use_id = p_site_use_id
AND ( invoice_currency_code = p_usage_curr(i).usage_curr_code
OR p_include_all_flag = 'Y' )
AND NVL(receipt_confirmed_flag, 'Y') = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND amount_due_remaining > 0
AND due_date <
sysdate - p_credit_check_rule_rec.maximum_days_past_due;
SELECT 'Y'
INTO x_exist_flag
FROM ar_payment_schedules_ALL
WHERE customer_id = p_customer_id
AND ( invoice_currency_code
= p_usage_curr(i).usage_curr_code
OR p_include_all_flag = 'Y' )
AND NVL(receipt_confirmed_flag, 'Y') = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND amount_due_remaining > 0
AND due_date <
sysdate - p_credit_check_rule_rec.maximum_days_past_due;
SELECT SUM(balance)
FROM oe_credit_summaries
WHERE balance_type = 18
AND cust_account_id = p_customer_id
AND NVL(org_id,-99) = G_ORG_ID
AND currency_code = p_curr_code;
SELECT SUM(balance)
FROM oe_credit_summaries
WHERE balance_type = 18
AND cust_account_id = p_customer_id
AND currency_code = p_curr_code;
SELECT SUM(NVL(ps.amount_due_remaining,0))
FROM ar_payment_schedules ps
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE ps.CUSTOMER_SITE_USE_ID = su.site_use_id
AND ps.org_id = su.org_id /* MOAC_SQL_CHANGE */
AND ps.status = 'OP'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,site_use_code,'BILL_TO' )
AND ps.invoice_currency_code =
p_curr_code
AND NVL(ps.receipt_confirmed_flag, 'Y') = 'Y'
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND sysdate - ps.trx_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS, sysdate - ps.trx_date - 1);
SELECT SUM(NVL(ps.amount_due_remaining,0))
FROM ar_payment_schedules ps
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE ps.CUSTOMER_SITE_USE_ID = su.site_use_id
AND ps.org_id = su.org_id /* MOAC_SQL_CHANGE */
AND ps.status = 'OP'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND ps.invoice_currency_code =
p_curr_code
AND NVL(ps.receipt_confirmed_flag, 'Y') = 'Y'
AND su.site_use_code = 'DRAWEE'
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY') ;
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history crh
, ar_cash_receipts_all cr
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.org_id = cr.org_id /* MOAC_SQL_CHANGE */
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 NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code =
p_curr_code
AND cr.pay_from_customer = cas.cust_account_id
AND cr.org_id = cas.org_id
AND cr.CUSTOMER_SITE_USE_ID = su.site_use_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,
site_use_code,'BILL_TO' )
AND sysdate - cr.receipt_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS,
sysdate - cr.receipt_date - 1)
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
) ;
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history crh
, ar_cash_receipts_all cr
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.org_id = cr.org_id /* MOAC_SQL_CHANGE */
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 NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code =
p_curr_code
AND cr.pay_from_customer = cas.cust_account_id
AND cr.org_id = cas.org_id
AND cr.CUSTOMER_SITE_USE_ID = su.site_use_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code = 'DRAWEE'
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y' );
SELECT SUM (
( l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines_all l
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0
))
FROM oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE H.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> l_header_id
AND L.HEADER_ID = H.HEADER_ID
AND L.ORG_ID = H.ORG_ID /* MOAC_SQL_CHANGE */
AND L.LINE_ID = P.LINE_ID
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
WHERE H.INVOICE_TO_ORG_ID = p_site_use_id
AND H.TRANSACTIONAL_CURR_CODE = p_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.ORG_ID = L.ORG_ID /* MOAC_SQL_CHANGE */
AND H.HEADER_ID <> l_header_id
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM (
(l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880589 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880618 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM (
(l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id /* MOAC_SQL_CHANGE */
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id /* MOAC_SQL_CHANGE */
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880775 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880801 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880869 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines_all l --Performance issue (SQL ID-14880869 FTS on OE_ORDER_HOLDS_ALL)
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880892 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines_all l --Performance issue (SQL ID-14880892 FTS on OE_ORDER_HOLDS_ALL)
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14880915 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines_all l --Performance issue (SQL ID-14880915 FTS on OE_ORDER_HOLDS_ALL)
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all cas
WHERE h.invoice_to_org_id = su.site_use_id
AND h.org_id = su.org_id
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT SUM(balance)
FROM oe_credit_summaries
WHERE balance_type = 18
AND site_use_id = p_site_use_id
AND currency_code = p_curr_code;
SELECT SUM(NVL(amount_due_remaining,0) )
FROM ar_payment_schedules
WHERE customer_site_use_id = p_site_use_id
AND status = 'OP'
AND invoice_currency_code =
p_curr_code
AND (receipt_confirmed_flag IS NULL OR receipt_confirmed_flag = 'Y')
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND sysdate - trx_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS, sysdate - trx_date - 1);
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history crh
, ar_cash_receipts_all cr
, hz_cust_site_uses_all s
, hz_cust_acct_sites_all a
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.org_id = cr.org_id
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED' )
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code =
p_curr_code
AND cr.customer_site_use_id = s.site_use_id
AND cr.org_id = s.org_id /* MOAC_SQL_CHANGE */
AND cr.pay_from_customer = a.cust_account_id
AND s.cust_acct_site_id = a.cust_acct_site_id
AND s.site_use_id = p_site_use_id
AND sysdate - cr.receipt_date
> NVL(p_credit_check_rule_rec.OPEN_AR_DAYS,
sysdate - cr.receipt_date - 1)
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
) ;
SELECT SUM(NVL(amount_due_remaining,0) )
FROM ar_payment_schedules
WHERE customer_site_use_id = l_drawee_site_use_id
AND status = 'OP'
AND invoice_currency_code = p_curr_code
AND (receipt_confirmed_flag IS NULL OR receipt_confirmed_flag = 'Y')
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY');
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history crh
, ar_cash_receipts_all cr
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.org_id = cr.org_id /* MOAC_SQL_CHANGE */
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED' )
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code = p_curr_code
AND cr.pay_from_customer = p_customer_id
AND cr.customer_site_use_id = l_drawee_site_use_id
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
) ;
SELECT SUM (
( l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM( (l.ordered_quantity * l.unit_selling_price )
+ DECODE( l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0
)
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM( (l.ordered_quantity * l.unit_selling_price )
+ DECODE( l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0
)
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity) * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND h.header_id = l.header_id
AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Performance issue (SQL ID-14881132 FTS on OE_ORDER_HOLDS_ALL)
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Modified the query to access oe_order_holds_all
WHERE h.header_id = oh.header_id --instead of oe_order_holds to avoid FTS (SQL ID#14881276)
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh --Modified the query to select from
WHERE h.header_id = oh.header_id --oe_order_holds_all to avoid FTS (SQL#14881293)
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = G_ORG_ID)
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines l
, oe_order_headers_all h
WHERE h.invoice_to_org_id = p_site_use_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND l.org_id = h.org_id /* MOAC_SQL_CHANGE */
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers h
WHERE h.invoice_to_org_id = p_site_use_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_all oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
(SELECT NULL
FROM oe_payment_types_all pt,
oe_order_lines l
WHERE pt.credit_check_flag = 'Y'
AND l.header_id = h.header_id
AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
AND NVL(pt.org_id, -99) = G_ORG_ID
AND pt.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, pt.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT SUM(NVL(ps.amount_due_remaining,0))
FROM ar_payment_schedules_ALL ps
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE ps.customer_site_use_id = su.site_use_id
AND ps.status = 'OP'
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,site_use_code,'BILL_TO' )
AND ps.invoice_currency_code = p_curr_code
AND (ps.receipt_confirmed_flag IS NULL OR ps.receipt_confirmed_flag='Y')
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND sysdate - ps.trx_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS, sysdate - ps.trx_date - 1); */
SELECT SUM(NVL(amount_due_remaining,0)) FROM (
SELECT NVL(ps.amount_due_remaining,0) amount_due_remaining,ps.PAYMENT_SCHEDULE_ID
FROM ar_payment_schedules_ALL ps
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE ps.customer_site_use_id = su.site_use_id
AND ps.status = 'OP'
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,site_use_code,'BILL_TO' )
AND ps.invoice_currency_code = p_curr_code
AND (ps.receipt_confirmed_flag IS NULL OR ps.receipt_confirmed_flag='Y')
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND sysdate - ps.trx_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS, sysdate - ps.trx_date - 1)
UNION
SELECT NVL(ps.amount_due_remaining,0) amount_due_remaining,ps.PAYMENT_SCHEDULE_ID
FROM ar_payment_schedules_ALL ps
WHERE ps.status = 'OP'
AND ps.CUSTOMER_ID = p_customer_id
AND ps.CUSTOMER_SITE_USE_ID IS NULL
AND ps.invoice_currency_code = p_curr_code
AND (ps.receipt_confirmed_flag IS NULL OR ps.receipt_confirmed_flag='Y')
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND sysdate - ps.trx_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS, sysdate - ps.trx_date - 1));
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history_ALL crh
, ar_cash_receipts_ALL cr
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED')
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code = p_curr_code
AND cr.pay_from_customer = cas.cust_account_id
AND cr.customer_site_use_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,site_use_code,'BILL_TO' )
AND sysdate - cr.receipt_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS,
sysdate - cr.receipt_date - 1)
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications_ALL rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
) ;
SELECT SUM(NVL(amount,0)) FROM (
SELECT NVL(crh.amount,0) amount,cr.pay_from_customer,cr.cash_receipt_id
FROM ar_cash_receipt_history_ALL crh
, ar_cash_receipts_ALL cr
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED')
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code = p_curr_code
AND cr.pay_from_customer = cas.cust_account_id
AND cr.customer_site_use_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code =
DECODE(l_open_ar_days,0,site_use_code,'BILL_TO' )
AND sysdate - cr.receipt_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS,
sysdate - cr.receipt_date - 1)
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications_ALL rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
)
UNION
SELECT NVL(crh.amount,0) amount,cr.pay_from_customer,cr.cash_receipt_id
FROM ar_cash_receipt_history_ALL crh
, ar_cash_receipts_ALL cr
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED')
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code = p_curr_code
AND cr.pay_from_customer = p_customer_id
AND cr.customer_site_use_id IS NULL
AND sysdate - cr.receipt_date >
NVL(p_credit_check_rule_rec.OPEN_AR_DAYS,
sysdate - cr.receipt_date - 1)
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications_ALL rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y'
)
);
SELECT SUM(NVL(ps.amount_due_remaining,0))
FROM ar_payment_schedules_ALL ps
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE ps.customer_site_use_id = su.site_use_id
AND ps.status = 'OP'
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code = 'DRAWEE'
AND ps.invoice_currency_code = p_curr_code
AND (ps.receipt_confirmed_flag IS NULL OR ps.receipt_confirmed_flag='Y')
AND ps.gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY') ;
SELECT SUM(NVL(crh.amount,0))
FROM ar_cash_receipt_history_ALL crh
, ar_cash_receipts_ALL cr
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
AND crh.current_record_flag = 'Y'
AND crh.status <> DECODE ( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'CLEARED')
AND NVL( cr.reversal_category, cr.status||'X' ) <> cr.status
AND crh.status <> 'REVERSED'
AND cr.currency_code = p_curr_code
AND cr.pay_from_customer = cas.cust_account_id
AND cr.customer_site_use_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND su.site_use_code = 'DRAWEE'
AND NOT EXISTS
(
SELECT
'X'
FROM
ar_receivable_applications_ALL rap
WHERE
rap.cash_receipt_id = cr.cash_receipt_id
AND rap.applied_payment_schedule_id = -2
AND rap.display = 'Y');
SELECT SUM (
( l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT NVL(SUM(P.commitment_applied_amount), 0)
FROM OE_PAYMENTS P
, OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE H.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND H.TRANSACTIONAL_CURR_CODE = p_curr_code
AND H.OPEN_FLAG = 'Y'
AND H.BOOKED_FLAG = 'Y'
AND H.HEADER_ID = P.HEADER_ID
AND H.HEADER_ID <> l_header_id
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_ID = P.LINE_ID
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM (
( l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT SUM (
( l.ordered_quantity * l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(l.tax_value,0), 0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM (
( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity)
* l.unit_selling_price )
+ DECODE(l_include_tax_flag, 'Y',
NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
)
FROM oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND h.header_id = l.header_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND l.open_flag = 'Y'
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.hold_release_id IS NULL )
AND h.transactional_curr_code = p_curr_code
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND ( p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM
( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
p.operand, (l.ordered_quantity * p.adjusted_amount))
)
FROM oe_price_adjustments p
, oe_order_lines_ALL l
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND p.line_id = l.line_id
AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
<= l_shipping_horizon
AND (l.invoiced_quantity IS NULL OR l.invoiced_quantity = 0)
AND l.header_id = h.header_id
AND p.header_id = l.header_id
AND p.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.transactional_curr_code = p_curr_code
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND NOT EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
SELECT
SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
FROM
oe_price_adjustments p
, oe_order_headers_ALL h
, hz_cust_site_uses_ALL su
, hz_cust_acct_sites_ALL cas
WHERE h.invoice_to_org_id = su.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id = p_customer_id
AND NVL(h.request_date, h.creation_date) <= l_shipping_horizon
AND h.transactional_curr_code = p_curr_code
AND p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED','RETURN')
AND h.open_flag = 'Y'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND (p.invoiced_flag IS NULL OR p.invoiced_flag = 'N')
AND h.header_id <> l_header_id
AND EXISTS ( SELECT 1
FROM oe_order_holds_ALL oh
WHERE h.header_id = oh.header_id
AND oh.line_id IS NULL
AND oh.hold_release_id IS NULL )
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t,
oe_order_lines_all l
WHERE t.credit_check_flag = 'Y'
AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
AND l.header_id = h.header_id
AND t.payment_type_code =
DECODE(l.payment_type_code, NULL,
DECODE(h.payment_type_code, NULL, t.payment_type_code,
h.payment_type_code),
l.payment_type_code)
);
OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders_global ');
OE_DEBUG_PUB.Add(' select cust_uninv_orders_global_ret ');
OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders ');
OE_DEBUG_PUB.Add(' select cust_uninv_orders_ret ');
SELECT user_name
FROM fnd_user
WHERE user_id = p_header_rec.created_by ;
SELECT name
FROM oe_transaction_types_vl
WHERE transaction_type_id = p_header_rec.order_type_id;
SELECT meaning
INTO l_meaning
FROM oe_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;