[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM OE_credit_summaries
WHERE cust_account_id = p_customer_id
AND currency_code = p_curr_code
AND bucket <= l_jdate
AND bucket_duration = 1
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE cust_account_id = p_customer_id
AND currency_code = p_curr_code
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE site_use_id = p_site_use_id
AND currency_code = p_curr_code
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = p_curr_code
AND oes.bucket <= l_jdate
AND oes.balance_type = 20
AND oes.bucket_duration = 1 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE party_id = p_party_id
AND currency_code = p_curr_code
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE cust_account_id = p_customer_id
AND bucket <= l_jdate
AND bucket_duration = 1
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE cust_account_id = p_customer_id
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries
WHERE site_use_id = p_site_use_id
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
SELECT 'Y'
FROM OE_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket <= l_jdate
AND oes.balance_type = 20
AND oes.bucket_duration = 1;
SELECT 'Y'
FROM OE_credit_summaries
WHERE party_id = p_party_id
AND bucket <= l_jdate
AND bucket_duration = 1
AND balance_type = 20 ;
oe_debug_pub.add( 'selected buckets: ' );
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18)
AND site_use_id = l_site_use_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT NVL(SUM( balance ),0)
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b23, b24,
b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)
AND site_use_id = l_site_use_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18)
AND site_use_id = l_site_use_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b23, b24,
b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)
AND site_use_id = l_site_use_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND site_use_id = l_br_site_use_id
AND currency_code = l_currency_code
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21,b22)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21,b22, b23, b24,
b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22, b23, b24,
b25, b26, b27, b28, b29, b30, b31, b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND currency_code = l_currency_code
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND cust_account_id = p_customer_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND cust_account_id = p_customer_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND cust_account_id = p_customer_id
AND currency_code = l_currency_code
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND party_id = p_party_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND party_id = p_party_id
AND currency_code = l_currency_code
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND party_id = p_party_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND party_id = p_party_id
AND currency_code = l_currency_code
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
;
SELECT SUM( balance )
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND party_id = p_party_id
AND currency_code = l_currency_code
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
;
SELECT SUM( oes.balance )
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = l_currency_code
AND oes.bucket = l_bucket
AND oes.bucket_duration = l_bucket_length
;
SELECT SUM( oes.balance )
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = l_currency_code
AND oes.bucket = l_bucket
AND oes.bucket_duration = l_bucket_length
;
SELECT SUM( oes.balance )
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = l_currency_code
AND oes.bucket < l_bucket
AND oes.bucket_duration = l_bucket_length
;
SELECT SUM( oes.balance )
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = l_currency_code
AND oes.bucket < l_bucket
AND oes.bucket_duration = l_bucket_length
;
SELECT SUM( oes.balance )
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(21,22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.currency_code = l_currency_code
AND oes.bucket < l_br_bucket
AND oes.bucket_duration = l_br_bucket_length
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18)
AND site_use_id = l_site_use_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND site_use_id = l_site_use_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18)
AND site_use_id = l_site_use_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND site_use_id = l_site_use_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND site_use_id = l_br_site_use_id
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22 )
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18, b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND cust_account_id = p_customer_id
AND ((org_id = p_org_id)
OR
(org_id IS NULL AND p_org_id IS NULL))
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND cust_account_id = p_customer_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND cust_account_id = p_customer_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND cust_account_id = p_customer_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND cust_account_id = p_customer_id
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND party_id = p_party_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND party_id = p_party_id
AND bucket = l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND party_id = p_party_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND party_id = p_party_id
AND bucket < l_bucket
AND bucket_duration = l_bucket_length
GROUP BY currency_code
;
SELECT SUM( balance )
, currency_code
FROM oe_credit_summaries
WHERE balance_type IN
(21,22)
AND party_id = p_party_id
AND bucket < l_br_bucket
AND bucket_duration = l_br_bucket_length
GROUP BY currency_code
;
SELECT SUM( oes.balance )
, oes.currency_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket = l_bucket
AND oes.bucket_duration = l_bucket_length
GROUP BY oes.currency_code
;
SELECT SUM( oes.balance )
, oes.currency_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket = l_bucket
AND oes.bucket_duration = l_bucket_length
GROUP BY oes.currency_code
;
SELECT SUM( oes.balance )
, oes.currency_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket < l_bucket
AND oes.bucket_duration = l_bucket_length
GROUP BY oes.currency_code
;
SELECT SUM( oes.balance )
, oes.currency_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(b1, b2, b3, b4, b5, b6, b7, b8, b9,
b10, b11, b12, b13, b14, b15, b16, b17, b18 , b21, b22,
b23, b24, b25, b26, b27, b28, b29, b30, b31,
b32, b33, b34, b35, b36)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket < l_bucket
AND oes.bucket_duration = l_bucket_length
GROUP BY oes.currency_code
;
SELECT SUM( oes.balance )
, oes.currency_code
FROM oe_credit_summaries oes
, hz_hierarchy_nodes hn
WHERE oes.balance_type IN
(21,22)
AND hn.parent_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
AND oes.party_id = hn.child_id
AND oes.bucket < l_br_bucket
AND oes.bucket_duration = l_br_bucket_length
GROUP BY oes.currency_code
;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT
orders_on_hold_flag
, maximum_days_past_due
, nvl(incl_freight_charges_flag, 'N') incl_freight_charges_flag
FROM oe_credit_check_rules
WHERE nvl(quick_cr_check_flag, 'N') = 'Y'
AND sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate);
l_last_updated_by := fnd_global.USER_ID;
l_last_update_login := fnd_global.LOGIN_ID;
DELETE FROM oe_credit_summaries
WHERE balance_type <> 18;
DELETE FROM oe_credit_summaries
WHERE balance_type <> 18;
INSERT INTO OE_INIT_CREDIT_SUMM_GTT
(line_ordered_quantity ,
line_unit_selling_price,
order_invoice_to_org_id,
rl_amount,
rl_quantity_ordered,
order_org_id ,
order_transactional_curr_code,
line_schedule_ship_date ,
line_request_date ,
order_request_date,
order_creation_date,
line_tax_value ,
line_invoice_to_org_id,
line_line_category_code ,
line_invoiced_quantity ,
line_payment_type_code ,
order_payment_type_code,
order_order_number ,
line_line_id ,
order_header_id,
line_header_id ,
order_cust_account_id,
order_party_id,
line_cust_account_id,
line_party_id)
select
l.ordered_quantity line_ordered_quantity,
l.unit_selling_price line_unit_selling_price,
h.invoice_to_org_id order_invoice_to_org_id,
0 rl_amount,
0 rl_quantity_ordered,
h.org_id order_org_id,
h.transactional_curr_code order_transactional_curr_code,
l.schedule_ship_date line_schedule_ship_date,
l.request_date line_request_date,
h.request_date order_request_date,
h.creation_date order_creation_date,
l.tax_value line_tax_value,
l.invoice_to_org_id line_invoice_to_org_id,
l.line_category_code line_line_category_code,
l.invoiced_quantity line_invoiced_quantity,
l.payment_type_code line_payment_type_code,
h.payment_type_code order_payment_type_code,
h.order_number order_order_number,
l.line_id line_line_id,
h.header_id order_header_id,
l.header_id line_header_id,
s.cust_account_id order_cust_account_id,
ca.party_id order_party_id,
s_l.cust_account_id line_cust_account_id,
ca_l.party_id line_party_id
from oe_order_lines_all l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
, hz_cust_site_uses_all su_l
, hz_cust_acct_sites_all s_l
, hz_cust_accounts ca_l
where h.header_id = l.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND NVL( l.invoiced_quantity, 0 ) = 0
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND su_l.site_use_id = l.invoice_to_org_id
AND su_l.cust_acct_site_id = s_l.cust_acct_site_id
AND ca_l.cust_account_id = s_l.cust_account_id
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t
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)
)
UNION ALL
select
/*+ cardinality ( rl 10 ) leading(rl h l) */ 0 line_ordered_quantity,
0 line_unit_selling_price,
h.invoice_to_org_id order_invoice_to_org_id,
rl.amount rl_amount,
rl.quantity_ordered rl_quantity_ordered,
h.org_id order_org_id,
h.transactional_curr_code order_transactional_curr_code,
l.schedule_ship_date line_schedule_ship_date,
l.request_date line_request_date,
h.request_date order_request_date,
h.creation_date order_creation_date,
l.tax_value line_tax_value,
l.invoice_to_org_id line_invoice_to_org_id,
l.line_category_code line_line_category_code,
l.invoiced_quantity line_invoiced_quantity,
l.payment_type_code line_payment_type_code,
h.payment_type_code order_payment_type_code,
h.order_number order_order_number,
l.line_id line_line_id,
h.header_id order_header_id,
l.header_id line_header_id,
s.cust_account_id order_cust_account_id,
ca.party_id order_party_id,
s_l.cust_account_id line_cust_account_id,
ca_l.party_id line_party_id
from oe_order_lines_all l
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
, hz_cust_site_uses_all su_l
, hz_cust_acct_sites_all s_l
, hz_cust_accounts ca_l
, ra_interface_lines_all rl
where h.header_id = l.header_id
AND h.booked_flag = 'Y'
AND rl.orig_system_bill_customer_id = ca.cust_account_id
AND nvl(rl.interface_status, '~') <> 'P'
AND rl.interface_line_context = 'ORDER ENTRY'
AND rl.interface_line_attribute1 = h.order_number
AND rl.interface_line_attribute6 = l.line_id
AND NVL( l.invoiced_quantity, 0 ) <> 0
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND su_l.site_use_id = l.invoice_to_org_id
AND su_l.cust_acct_site_id = s_l.cust_acct_site_id
AND ca_l.cust_account_id = s_l.cust_account_id
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t
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( 'Start Inserting into summary tables ',1);
INSERT INTO oe_credit_summaries
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 ) )
- SUM( NVL( p.commitment_applied_amount, 0 ) )
+ SUM( NVL( m.rl_amount, 0 ))
, G_HEADER_UNINVOICED_ORDERS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'ORDER'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( NVL( m.line_tax_value, 0 ) )
, G_HEADER_UNINVOICED_ORDERS_TAX
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'ORDER'
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO oe_credit_summaries
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 ) )
+ SUM( NVL( p.commitment_applied_amount, 0 ) )
+ SUM( DECODE( SIGN (NVL( m.rl_quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( m.rl_amount, 0 ) )
, G_HEAD_RETURN_UNINV_ORDERS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'RETURN'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM( NVL( m.line_tax_value, 0 ) )
, G_HEAD_RETURN_UNINV_ORD_TAX
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'RETURN'
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 )
)
- SUM( NVL( p.commitment_applied_amount, 0 ) )
+ SUM( NVL( m.rl_amount, 0 ))
, G_LINE_UNINVOICED_ORDERS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'ORDER'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( NVL( m.line_tax_value, 0 ) )
, G_LINE_UNINVOICED_ORDERS_TAX
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'ORDER'
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM( NVL(m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 )
)
+ SUM( NVL( p.commitment_applied_amount, 0 ) )
+ SUM( DECODE( SIGN (NVL( m.rl_quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( m.rl_amount, 0 ) )
, G_LINE_RETURN_UNINV_ORDERS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'RETURN'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM( NVL(m.line_tax_value, 0 ) )
, G_LINE_RETURN_UNINV_ORD_TAX
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'RETURN'
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
+ SUM( NVL( m.rl_amount, 0 ))
, G_LINE_UNINVOICED_FREIGHT
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m,
oe_price_adjustments p
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
+ SUM( NVL( m.rl_amount, 0 ))
, G_LINE_RETURN_UNINV_FREIGHT
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'RETURN'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
+ SUM( NVL( m.rl_amount, 0 ))
, G_HEADER_UNINVOICED_FREIGHT
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) );
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
+ SUM( NVL( m.rl_amount, 0 ))
, G_HEAD_RETURN_UNINV_FREIGHT
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'RETURN'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) ) * p.operand )
, G_HEADER_AND_LINE_FREIGHT
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
WHERE
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 NVL( p.invoiced_flag, 'N' ) = 'N'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
UNION ALL --bug# 2714553
SELECT
SUM( NVL( rl.amount, 0 ))
, G_HEADER_AND_LINE_FREIGHT
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
, ra_interface_lines_all rl
WHERE
p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code IN ('ORDER','MIXED')
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'Y'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND rl.orig_system_bill_customer_id = ca.cust_account_id
AND nvl(rl.interface_status, '~') <> 'P'
AND rl.interface_line_context = 'ORDER ENTRY'
AND rl.interface_line_attribute1 = h.order_number
AND rl.interface_line_attribute6 = p.price_adjustment_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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) ) * p.operand )
, G_HEAD_LINE_RETURN_FREIGHT
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
WHERE
p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code ='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 NVL( p.invoiced_flag, 'N' ) = 'N'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
UNION ALL --bug# 2714553
SELECT
SUM( NVL( rl.amount, 0 ))
, G_HEAD_LINE_RETURN_FREIGHT
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
, ra_interface_lines_all rl
WHERE
p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code ='RETURN'
AND h.booked_flag = 'Y'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'Y'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND rl.orig_system_bill_customer_id = ca.cust_account_id
AND nvl(rl.interface_status, '~') <> 'P'
AND rl.interface_line_context = 'ORDER ENTRY'
AND rl.interface_line_attribute1 = h.order_number
AND rl.interface_line_attribute6 = p.price_adjustment_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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 ) )
+ SUM( NVL( p.commitment_applied_amount, 0 ) )
, G_ORDER_HOLDS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m,
oe_payments p
WHERE
m.line_line_category_code = 'ORDER'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
AND EXISTS
( SELECT 1
FROM
oe_order_holds_all oh
WHERE
oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM( NVL( m.line_tax_value, 0 ) )
, G_ORDER_TAX_HOLDS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'ORDER'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM( NVL( l.ordered_quantity, 0 )
* NVL( l.unit_selling_price, 0 ) )
- SUM( NVL( p.commitment_applied_amount, 0 ) )
, G_ORDER_RETURN_HOLDS
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_order_lines_all l
, oe_order_headers_all h
, oe_payments p
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
WHERE
h.header_id = l.header_id
AND h.booked_flag = 'Y'
AND h.open_flag = 'Y'
AND l.open_flag = 'Y'
AND l.line_category_code = 'RETURN'
AND NVL( l.invoiced_quantity, 0 ) = 0
AND p.header_id (+) = l.header_id
AND p.line_id (+) = l.line_id
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = h.header_id
AND ( oh.line_id = l.line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
AND EXISTS
( SELECT NULL
FROM oe_payment_types_all t
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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM( NVL( m.line_tax_value, 0 ) )
, G_ORDER_RETURN_TAX_HOLDS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'RETURN'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 ) )
+ SUM( NVL( p.commitment_applied_amount, 0 ) )
, G_LINE_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'ORDER'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM( NVL( m.line_tax_value, 0 ) )
, G_LINE_TAX_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'ORDER'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM( NVL( m.line_ordered_quantity, 0 )
* NVL( m.line_unit_selling_price, 0 ) )
- SUM( NVL( p.commitment_applied_amount, 0 ) )
, G_LINE_RETURN_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
, oe_payments p
WHERE
m.line_line_category_code = 'RETURN'
AND p.header_id (+) = m.line_header_id
AND p.line_id (+) = m.line_line_id
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
SUM( NVL( m.line_tax_value, 0 ) )
, G_LINE_RETURN_TAX_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
OE_INIT_CREDIT_SUMM_GTT m
WHERE
m.line_line_category_code = 'RETURN'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
, G_LINE_FREIGHT_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
, G_LINE_RETURN_FREIGHT_HOLDS
, m.line_invoice_to_org_id
, line_cust_account_id
, line_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'RETURN'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.line_invoice_to_org_id
, m.order_transactional_curr_code
, line_cust_account_id
, line_party_id
, m.order_org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
, G_ORDER_FREIGHT_HOLDS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'ORDER'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
, TO_NUMBER( TO_CHAR( NVL( m.line_schedule_ship_date,
NVL( m.line_request_date,
NVL( m.order_request_date, m.order_creation_date) ) ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM
( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) )
* DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295298
p.operand, (m.line_ordered_quantity * p.adjusted_amount))
)
, G_ORDER_RETURN_FREIGHT_HOLDS
, m.order_invoice_to_org_id
, order_cust_account_id
, order_party_id
, m.order_org_id
, m.order_transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM oe_price_adjustments p,
OE_INIT_CREDIT_SUMM_GTT m
WHERE
p.line_id = m.line_line_id
AND p.header_id = m.line_header_id
AND p.header_id = m.order_header_id
AND m.line_line_category_code = 'RETURN'
AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = m.order_header_id
AND ( oh.line_id = m.line_line_id
OR oh.line_id IS NULL
)
AND oh.hold_release_id IS NULL
)
GROUP BY
m.order_invoice_to_org_id
, m.order_transactional_curr_code
, order_cust_account_id
, order_party_id
, m.order_org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
- SUM( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) ) * p.operand )
, G_HEADER_LINE_FREIGHT_HOLDS
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
WHERE
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 NVL( p.invoiced_flag, 'N' ) = 'N'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = h.header_id
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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
, TO_NUMBER( TO_CHAR( NVL( h.request_date, h.creation_date ), 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT
- SUM( DECODE( p.credit_or_charge_flag, 'C', (-1), (+1) ) * p.operand )
, G_H_L_RETURN_FREIGHT_HOLDS
, h.invoice_to_org_id
, s.cust_account_id
, ca.party_id
, h.org_id
, h.transactional_curr_code
, -2
, OE_CREDIT_EXPOSURE_PVT.G_MAX_BUCKET_LENGTH
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_price_adjustments p
, oe_order_headers_all h
, hz_cust_site_uses_all su
, hz_cust_acct_sites_all s
, hz_cust_accounts ca
WHERE
p.line_id IS NULL
AND p.header_id = h.header_id
AND h.order_category_code = '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 NVL( p.invoiced_flag, 'N' ) = 'N'
AND su.site_use_id = h.invoice_to_org_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND ca.cust_account_id = s.cust_account_id
AND EXISTS
( SELECT 1
FROM oe_order_holds_all oh
WHERE oh.header_id = h.header_id
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)
)
GROUP BY
h.invoice_to_org_id
, h.transactional_curr_code
, s.cust_account_id
, ca.party_id
, h.org_id
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( sch.amount_due_remaining )
, G_INVOICES
, sch.customer_site_use_id
, sch.customer_id
, ca.party_id
, sch.org_id
, sch.invoice_currency_code
, TO_NUMBER( TO_CHAR( sch.trx_date, 'J' ) )
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
ar_payment_schedules_all sch ,
hz_cust_accounts ca
, hz_cust_site_uses_all su
WHERE
NVL( receipt_confirmed_flag, 'Y' ) = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND ca.cust_account_id = sch.customer_id
AND su.site_use_id = sch.customer_site_use_id
AND su.site_use_code <> 'DRAWEE'
GROUP BY
sch.customer_site_use_id
, sch.invoice_currency_code
, sch.customer_id
, ca.party_id
, sch.org_id
, TO_NUMBER( TO_CHAR( sch.trx_date, 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( crh.amount ) pay_risk
, G_PAYMENTS_AT_RISK
, cr.customer_site_use_id
, cr.pay_from_customer
, ca.party_id
, cr.org_id
, cr.currency_code
, TO_NUMBER( TO_CHAR( cr.receipt_date, 'J' ) )
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
ar_cash_receipts_all cr
, ar_cash_receipt_history_all crh
, hz_cust_accounts ca
, hz_cust_site_uses_all su
WHERE
cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND NVL( cr.confirmed_flag, 'Y' ) = 'Y'
AND NVL( cr.reversal_category, cr.status || 'X' ) <> cr.status
AND crh.status NOT IN
(
DECODE( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'N', 'CLEARED'
)
, 'REVERSED'
)
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'
)
AND ca.cust_account_id = cr.pay_from_customer
AND su.site_use_id = cr.customer_site_use_id
AND su.site_use_code <> 'DRAWEE'
GROUP BY
cr.customer_site_use_id
, cr.currency_code
, cr.pay_from_customer
, ca.party_id
, cr.org_id
, TO_NUMBER( TO_CHAR( cr.receipt_date, 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( sch.amount_due_remaining )
, G_BR_INVOICES
, sch.customer_site_use_id
, sch.customer_id
, ca.party_id
, sch.org_id
, sch.invoice_currency_code
, TO_NUMBER( TO_CHAR( sch.trx_date, 'J' ) )
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
ar_payment_schedules_all sch ,
hz_cust_accounts ca
, hz_cust_site_uses_all su
WHERE
NVL( receipt_confirmed_flag, 'Y' ) = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND ca.cust_account_id = sch.customer_id
AND su.site_use_id = sch.customer_site_use_id
AND su.site_use_code = 'DRAWEE'
GROUP BY
sch.customer_site_use_id
, sch.invoice_currency_code
, sch.customer_id
, ca.party_id
, sch.org_id
, TO_NUMBER( TO_CHAR( sch.trx_date, 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
SUM( crh.amount ) pay_risk
, G_BR_PAYMENTS_AT_RISK
, cr.customer_site_use_id
, cr.pay_from_customer
, ca.party_id
, cr.org_id
, cr.currency_code
, TO_NUMBER( TO_CHAR( cr.receipt_date, 'J' ) )
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
ar_cash_receipts_all cr
, ar_cash_receipt_history_all crh
, hz_cust_accounts ca
, hz_cust_site_uses_all su
WHERE
cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND NVL( cr.confirmed_flag, 'Y' ) = 'Y'
AND NVL( cr.reversal_category, cr.status || 'X' ) <> cr.status
AND crh.status NOT IN
(
DECODE( crh.factor_flag
, 'Y', 'RISK_ELIMINATED'
, 'N', 'CLEARED'
)
, 'REVERSED'
)
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'
)
AND ca.cust_account_id = cr.pay_from_customer
AND su.site_use_id = cr.customer_site_use_id
AND su.site_use_code = 'DRAWEE'
GROUP BY
cr.customer_site_use_id
, cr.currency_code
, cr.pay_from_customer
, ca.party_id
, cr.org_id
, TO_NUMBER( TO_CHAR( cr.receipt_date, 'J' ) )
;
INSERT INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, bucket_duration
)
SELECT
COUNT(payment_schedule_id)
, G_past_due_invoices
, sch.customer_site_use_id
, sch.customer_id
, ca.party_id
, sch.org_id
, sch.invoice_currency_code
, TO_NUMBER( TO_CHAR( sch.due_date, 'J' ) )
, sysdate
, l_created_by
, sysdate
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
, 1
FROM
ar_payment_schedules_all sch ,
hz_cust_accounts ca
WHERE
NVL( receipt_confirmed_flag, 'Y' ) = 'Y'
AND gl_date_closed = to_date( '31-12-4712', 'DD-MM-YYYY')
AND ca.cust_account_id = sch.customer_id
AND amount_due_remaining > 0
GROUP BY
sch.customer_site_use_id
, sch.invoice_currency_code
, sch.customer_id
, ca.party_id
, sch.org_id
, TO_NUMBER( TO_CHAR( sch.due_date, 'J' ) )
;
/*UPDATE
oe_credit_summaries
SET
bucket_duration = l_bucket_length
WHERE balance_type NOT IN (18,23,24,25,26,27,28,29,30,31,32,33,34,35,36); ---change for Returns
UPDATE
oe_credit_summaries
SET
bucket_duration = l_bucket_length
WHERE balance_type =18;
oe_debug_pub.add( 'level 0 records updated at ' || DO_TIME, 2 );
INSERT /*+ parallel */ INTO OE_CREDIT_SUMMARIES
( balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
,bucket_duration
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
)
SELECT /*+ parallel(OE_CREDIT_SUMMARIES) */
SUM( balance )
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket - MOD( bucket, l_bucket_length )
, l_bucket_length
, SYSDATE
, l_created_by
, SYSDATE
, l_last_updated_by
, l_last_update_login
, l_program_application_id
, l_program_id
, SYSDATE
, l_request_id
FROM
oe_credit_summaries
WHERE
bucket_duration = l_bucket_length / 2
AND balance_type NOT IN (18,23,24,25,26,27,28,29,30,31,32,33,34,35,36) ---change for Returns
GROUP BY
balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket - MOD( bucket, l_bucket_length )
;
oe_debug_pub.add( ' Done Inserting into summary table, about to COMMIT');
SELECT
'Y'
FROM
hz_hierarchy_nodes hn
WHERE hn.parent_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 ;
oe_debug_pub.add(' Select OM and AR exposure separately ');