[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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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 ,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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,b37,b38,b39,b40,b41,b42) --TaxER
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,b37,b38,b39,b40,b41,b42,b43,b44,b45,b46,b47,b48) --TaxER
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);
SELECT *
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 IN(1,2,3,4,5,6,7,8,9,10,11,13,14,15,16,17,20,21,22,23,24,25,26,27,28,29,30,31,32
,33,34,35,36);
DELETE FROM oe_credit_summaries
WHERE balance_type <> 18;
select 'N'
into l_cc_level_flag
from dual
where not exists ( select 1 from oe_credit_check_rules where credit_check_level_code = 'ORDER' and quick_cr_check_flag= 'Y');
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,
interface_line_attribute6) --TaxER
select /*+ leading(L) use_nl(H SU S CA CA_L SU_L S_L) */ --14689044
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,
--TaxER l.tax_value line_tax_value,
NVL(l.tax_line_value,l.tax_value) line_tax_value, --TaxER
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,
-999 interface_line_attribute6 --TaxER
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
/*14689044 start
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)
)
14689044 end*/
--14689044 start
AND EXISTS ( SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NOT NULL
AND T.PAYMENT_TYPE_CODE = L.PAYMENT_TYPE_CODE
UNION ALL
SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NULL AND H.PAYMENT_TYPE_CODE IS NOT NULL
AND T.PAYMENT_TYPE_CODE = H.PAYMENT_TYPE_CODE
UNION ALL
SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NULL AND H.PAYMENT_TYPE_CODE IS NULL
)
--14689044 end
--14689044 UNION ALL
UNION --14689044
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,
--TaxER l.tax_value line_tax_value,
Decode(NVL(rl.interface_line_attribute11,0),0,NVL(l.tax_line_value,l.tax_value),0) line_tax_value, --TaxER
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,
DECODE(OE_CREDIT_EXPOSURE_PVT.Is_Equal(rl.interface_line_attribute6,l.line_id),'Y',-999,to_number(rl.interface_line_attribute6)) interface_line_attribute6 --TaxER
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
--TaxER 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
/*14689044 start
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)
);
AND EXISTS ( SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NOT NULL
AND T.PAYMENT_TYPE_CODE = L.PAYMENT_TYPE_CODE
UNION ALL
SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NULL AND H.PAYMENT_TYPE_CODE IS NOT NULL
AND T.PAYMENT_TYPE_CODE = H.PAYMENT_TYPE_CODE
UNION ALL
SELECT /*+ no_unnest */ 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.PAYMENT_TYPE_CODE IS NULL AND H.PAYMENT_TYPE_CODE IS NULL
);
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
AND m.interface_line_attribute6 = -999 --TaxER
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'
AND m.interface_line_attribute6 = -999 --TaxER
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
AND m.interface_line_attribute6 = -999 --TaxER
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'
AND m.interface_line_attribute6 = -999 --TaxER
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
AND m.interface_line_attribute6 = -999 --TaxER
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'
AND m.interface_line_attribute6 = -999 --TaxER
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
AND m.interface_line_attribute6 = -999 --TaxER
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'
AND m.interface_line_attribute6 = -999 --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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( p1.adjusted_amount)
, G_LINE_UNINVOICED_FREIGHT_T
, 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,
oe_price_adjustments p1 --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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 ( p1.adjusted_amount)
, G_LINE_RETURN_UNINV_FREIGHT_T
, 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_price_adjustments p,
oe_price_adjustments p1 --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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
( p1.adjusted_amount)
, G_HEADER_UNINVOICED_FREIGHT_T
, 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_price_adjustments p1, --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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 ( p1.adjusted_amount)
, G_HEAD_RETURN_UNINV_FREIGHT_T
, 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_price_adjustments p1, --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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
, 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 ( p.adjusted_amount)
, G_HEADER_AND_LINE_FREIGHT_T
, 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'
--12597952 AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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_T
, 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'
--12597952 AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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
, 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 ( p.adjusted_amount)
, G_HEAD_LINE_RETURN_FREIGHT_T
, 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'
--12597952 AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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_T
, 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'
--12597952 AND p.applied_flag = 'Y'
AND p.list_line_type_code = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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 m.interface_line_attribute6 = -999 --TaxER
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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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 ( p1.adjusted_amount)
, G_LINE_FREIGHT_HOLDS_T
, 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_price_adjustments p1, --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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 ( p1.adjusted_amount)
, G_LINE_RETURN_FREIGHT_HOLDS_T
, 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_price_adjustments p1, --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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 ( p1.adjusted_amount)
, G_ORDER_FREIGHT_HOLDS_T
, 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_price_adjustments p1, --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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'
--TaxER AND NVL( p.invoiced_flag, 'N' ) = 'N'
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
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
, 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 ( p1.adjusted_amount)
, G_ORDER_RETURN_FREIGHT_HOLDS_T
, 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_price_adjustments p1, --TaxER
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 p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
AND p1.applied_flag = 'N' --12895421
AND p.price_adjustment_id = p1.parent_adjustment_id
AND p.line_id = p1.line_id
AND p.header_id = p1.header_id
AND m.interface_line_attribute6 = decode(m.rl_amount,0,-999,p.price_adjustment_id) --TaxER
--TaxER 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
, 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 ( p.adjusted_amount)
, G_HEADER_LINE_FREIGHT_HOLDS_T
, 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 = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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
, 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 ( p.adjusted_amount)
, G_H_L_RETURN_FREIGHT_HOLDS_T
, 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 = 'TAX'
AND p.parent_adjustment_id IS NOT NULL
AND p.applied_flag = 'N' --12895421
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
(balance,
balance_type,
site_use_id,
cust_account_id,
party_id,
org_id,
currency_code,
bucket,
bucket_duration)
SELECT
balance
, balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket
, bucket_duration
FROM
oe_credit_summaries
WHERE
balance_type NOT IN (18,23,24,25,26,27,28,29,30,31,32,33,34,35,36); ---change for Returns
INSERT
( 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( 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_INIT_SUMM_TMP
-- WHERE
-- bucket_duration = l_bucket_length / 2
GROUP BY
balance_type
, site_use_id
, cust_account_id
, party_id
, org_id
, currency_code
, bucket - MOD( bucket, l_bucket_length )
;
INSERT INTO /*+ parallel */ 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( 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 ');