[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sysdate INTO g_fii_sysdate FROM dual;
SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
FROM BIS_SYSTEM_DATE;
SELECT NVL(fii_time_api.ent_sd_pper_end(l_this_date),l_min_start_date),
NVL(fii_time_api.ent_sd_pqtr_end(l_this_date),l_min_start_date),
NVL(fii_time_api.ent_sd_lyr_end(l_this_date),l_min_start_date),
NVL( fii_time_api.ent_cper_end(l_this_date),l_min_start_date)
INTO l_pp_this_date,
l_pq_this_date,
l_ly_this_date,
l_this_date_gov
FROM DUAL;*/
insert /*+ append */ INTO FII_AR_TPDUE_BASE_F
( parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
past_due_open_amount_func,
past_due_open_amount_prim,
past_due_open_amount_sec,
wtd_terms_out_open_num_func,
wtd_terms_out_open_num_prim,
wtd_terms_out_open_num_sec,
wtd_DDSO_due_num_func,
wtd_DDSO_due_num_prim,
wtd_DDSO_due_num_sec,
current_open_amount_func,
current_open_amount_prim,
current_open_amount_sec,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT parent_party_id,
party_id,
collector_id,
org_id,
is_leaf_flag,
is_self_flag,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
SUM(past_due_open_amount_func) past_due_open_amount_func,
SUM(past_due_open_amount_prim) past_due_open_amount_prim,
SUM(past_due_open_amount_sec) past_due_open_amount_sec,
SUM(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func,
SUM(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim,
SUM(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec,
SUM(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func,
SUM(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim,
SUM(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec,
SUM(current_open_amount_func) current_open_amount_func,
SUM(current_open_amount_prim) current_open_amount_prim,
SUM(current_open_amount_sec) current_open_amount_sec,
g_fii_sysdate,
g_fii_user_id,
g_fii_sysdate,
g_fii_user_id,
g_fii_login_id FROM (
SELECT
b.parent_party_id parent_party_id,
b.party_id party_id,
b.collector_id collector_id,
b.org_id org_id,
cust.next_level_is_leaf_flag is_leaf_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then 'Y'
else 'N' end is_self_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then hz.party_name ||g_self_msg
else hz.party_name end view_by,
hz.party_id viewby_code,
cust.next_level_party_id cust_next_level_party_id,
cust.parent_party_id cust_parent_party_id,
cust.child_party_id cust_child_party_id,
past_due_open_amount_func,
past_due_open_amount_prim,
past_due_open_amount_sec,
wtd_terms_out_open_num_func,
wtd_terms_out_open_num_prim,
wtd_terms_out_open_num_sec,
wtd_DDSO_due_num_func,
wtd_DDSO_due_num_prim,
wtd_DDSO_due_num_sec,
current_open_amount_func,
current_open_amount_prim,
current_open_amount_sec
FROM fii_time_structures cal,
fii_ar_net_rec_base_mv b,
FII_CUSTOMER_HIERARCHIES cust,
HZ_PARTIES hz
WHERE cal.report_date = l_this_date
AND cal.time_id = b.time_id
AND cal.period_type_id = b.period_type_id
AND bitand(cal.record_type_id, 512) = 512
AND cust.parent_party_id = b.parent_party_id
AND cust.child_party_id = b.party_id
AND cust.next_level_party_id = hz.party_id
AND b.gid = 1025
)
GROUP BY parent_party_id,
party_id,
collector_id,
org_id,
is_leaf_flag,
is_self_flag,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID;
FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
insert /*+ append */ INTO FII_AR_TPDUE_BASE_F
( parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
past_due_dispute_amount_func,
past_due_dispute_amount_prim,
past_due_dispute_amount_sec,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT parent_party_id,
party_id,
collector_id,
org_id,
is_leaf_flag,
is_self_flag,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
SUM(past_due_dispute_amount_func) past_due_dispute_amount_func,
SUM(past_due_dispute_amount_prim) past_due_dispute_amount_prim,
SUM(past_due_dispute_amount_sec) past_due_dispute_amount_sec,
g_fii_sysdate,
g_fii_user_id,
g_fii_sysdate,
g_fii_user_id,
g_fii_login_id FROM (
SELECT
b.parent_party_id parent_party_id,
b.party_id party_id,
b.collector_id collector_id,
b.org_id org_id,
cust.next_level_is_leaf_flag is_leaf_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then 'Y'
else 'N' end is_self_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then hz.party_name ||g_self_msg else hz.party_name end view_by,
hz.party_id viewby_code,
cust.next_level_party_id cust_next_level_party_id,
cust.parent_party_id cust_parent_party_id,
cust.child_party_id cust_child_party_id,
past_due_dispute_amount_func,
past_due_dispute_amount_prim,
past_due_dispute_amount_sec
FROM fii_time_structures cal,
fii_ar_disputes_base_mv b,
FII_CUSTOMER_HIERARCHIES cust,
HZ_PARTIES hz
WHERE cal.report_date = l_this_date
AND cal.time_id = b.time_id
AND cal.period_type_id = b.period_type_id
AND bitand(cal.record_type_id, 512) = 512
AND cust.parent_party_id = b.parent_party_id
AND cust.child_party_id = b.party_id
AND cust.next_level_party_id = hz.party_id
)
GROUP BY parent_party_id,
party_id,
collector_id,
org_id,
is_leaf_flag,
is_self_flag,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID;
FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
FROM BIS_SYSTEM_DATE;
SELECT NVL(fii_time_api.ent_sd_pper_end(l_this_date),l_min_start_date),
NVL(fii_time_api.ent_sd_pqtr_end(l_this_date),l_min_start_date),
NVL(fii_time_api.ent_sd_lyr_end(l_this_date),l_min_start_date),
NVL( fii_time_api.ent_cper_end(l_this_date),l_min_start_date)
INTO l_pp_this_date,
l_pq_this_date,
l_ly_this_date,
l_this_date_gov
FROM DUAL;*/
insert /*+ append */ INTO FII_AR_TPDUE_AGRT_F
( parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
past_due_open_amount_func,
past_due_open_amount_prim,
past_due_open_amount_sec,
wtd_terms_out_open_num_func,
wtd_terms_out_open_num_prim,
wtd_terms_out_open_num_sec,
wtd_DDSO_due_num_func,
wtd_DDSO_due_num_prim,
wtd_DDSO_due_num_sec,
current_open_amount_func,
current_open_amount_prim,
current_open_amount_sec,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
SUM(past_due_open_amount_func) past_due_open_amount_func,
SUM(past_due_open_amount_prim) past_due_open_amount_prim,
SUM(past_due_open_amount_sec) past_due_open_amount_sec,
SUM(wtd_terms_out_open_num_func) wtd_terms_out_open_num_func,
SUM(wtd_terms_out_open_num_prim) wtd_terms_out_open_num_prim,
SUM(wtd_terms_out_open_num_sec) wtd_terms_out_open_num_sec,
SUM(wtd_DDSO_due_num_func) wtd_DDSO_due_num_func,
SUM(wtd_DDSO_due_num_prim) wtd_DDSO_due_num_prim,
SUM(wtd_DDSO_due_num_sec) wtd_DDSO_due_num_sec,
SUM(current_open_amount_func) current_open_amount_func,
SUM(current_open_amount_prim) current_open_amount_prim,
SUM(current_open_amount_sec) current_open_amount_sec,
g_fii_sysdate,
g_fii_user_id,
g_fii_sysdate,
g_fii_user_id,
g_fii_login_id FROM (
SELECT
b.parent_party_id,
b.party_id,
b.collector_id,
b.org_id,
cust.next_level_is_leaf_flag is_leaf_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then 'Y'
else 'N' end is_self_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then hz.party_name ||g_self_msg else hz.party_name end view_by,
hz.party_id viewby_code,
cust.next_level_party_id cust_next_level_party_id,
cust.parent_party_id cust_parent_party_id,
cust.child_party_id cust_child_party_id,
past_due_open_amount_func,
past_due_open_amount_prim,
past_due_open_amount_sec,
wtd_terms_out_open_num_func,
wtd_terms_out_open_num_prim,
wtd_terms_out_open_num_sec,
wtd_DDSO_due_num_func,
wtd_DDSO_due_num_prim,
wtd_DDSO_due_num_sec,
current_open_amount_func,
current_open_amount_prim,
current_open_amount_sec
FROM fii_time_structures cal,
fii_ar_net_rec_agrt_mv b,
FII_CUSTOMER_HIERARCHIES cust,
HZ_PARTIES hz
WHERE cal.report_date = l_this_date
AND cal.time_id = b.time_id
AND cal.period_type_id = b.period_type_id
AND bitand(cal.record_type_id, 512) = 512
AND cust.child_party_id = b.party_id
AND b.parent_party_id in (SELECT decode (cust1.next_level_is_leaf_flag,
'Y', cust1.parent_party_id, cust.child_party_id)
FROM fii_customer_hierarchies cust1
WHERE cust1.next_level_party_id = cust.child_party_id
AND cust1.child_party_id = cust.child_party_id
AND cust1.child_party_id <> cust1.parent_party_id)
AND cust.next_level_party_id = hz.party_id
AND b.gid = 1025
)
GROUP BY parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID;
FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
insert /*+ append */ INTO FII_AR_TPDUE_AGRT_F
( parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
past_due_dispute_amount_func,
past_due_dispute_amount_prim,
past_due_dispute_amount_sec,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID,
SUM(past_due_dispute_amount_func) past_due_dispute_amount_func,
SUM(past_due_dispute_amount_prim) past_due_dispute_amount_prim,
SUM(past_due_dispute_amount_sec) past_due_dispute_amount_sec,
g_fii_sysdate,
g_fii_user_id,
g_fii_sysdate,
g_fii_user_id,
g_fii_login_id FROM(
SELECT
b.parent_party_id,
b.party_id,
b.collector_id,
b.org_id,
cust.next_level_is_leaf_flag is_leaf_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then 'Y'
else 'N' end is_self_flag,
case when cust.parent_party_id = hz.party_id
and cust.next_level_is_leaf_flag <> 'Y'
then hz.party_name ||g_self_msg else hz.party_name end view_by,
hz.party_id viewby_code,
cust.next_level_party_id cust_next_level_party_id,
cust.parent_party_id cust_parent_party_id,
cust.child_party_id cust_child_party_id,
past_due_dispute_amount_func past_due_dispute_amount_func,
past_due_dispute_amount_prim past_due_dispute_amount_prim,
past_due_dispute_amount_sec past_due_dispute_amount_sec
FROM fii_time_structures cal,
fii_ar_disputes_agrt_mv b,
FII_CUSTOMER_HIERARCHIES cust,
HZ_PARTIES hz
WHERE cal.report_date = l_this_date
AND cal.time_id = b.time_id
AND cal.period_type_id = b.period_type_id
AND bitand(cal.record_type_id, 512) = 512
AND cust.child_party_id = b.party_id
AND b.parent_party_id in (SELECT decode (cust1.next_level_is_leaf_flag,
'Y', cust1.parent_party_id, cust.child_party_id)
FROM fii_customer_hierarchies cust1
WHERE cust1.next_level_party_id = cust.child_party_id
AND cust1.child_party_id = cust.child_party_id
AND cust1.child_party_id <> cust1.parent_party_id)
AND cust.next_level_party_id = hz.party_id)
GROUP BY parent_party_id,
party_id,
collector_id,
org_id,
IS_LEAF_FLAG,
IS_SELF_FLAG,
VIEW_BY,
VIEWBY_CODE,
CUST_NEXT_LEVEL_PARTY_ID,
CUST_PARENT_PARTY_ID,
CUST_CHILD_PARTY_ID;
FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');