[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_STG
IS
BEGIN
DELETE FII_AP_SCH_PAYMTS_FSTG
WHERE COLLECTION_STATUS = 'LOCAL READY' OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
PROCEDURE INSERT_MISSING_RATES_IN_TMP
IS
BEGIN
-- --------------------------------------------------------------------------------------------------
-- The variable g_acct_or_inv_date is added in the below mentioned select statement.
-- The profile option stored in the global variable g_acct_or_inv_date
-- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement,25-APR-03
-----------------------------------------------------------------------------------------------------
INSERT INTO fii_ap_tmp_schp_pk(
Primary_Key1,
Primary_Key2,
Primary_Key5 )
SELECT
TO_NUMBER(SUBSTR (SCH_PAYMENT_PK, 1, INSTR(SCH_PAYMENT_PK, '-' )-1)),
TO_NUMBER(SUBSTR (SCH_PAYMENT_PK, INSTR(SCH_PAYMENT_PK, '-')+1,INSTR(SCH_PAYMENT_PK, '-',1,2) - (INSTR(SCH_PAYMENT_PK,'-')+1))) ,
g_acct_or_inv_date
FROM fii_ap_sch_paymts_fstg fsp
WHERE
fsp.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
OR
fsp.COLLECTION_STATUS = 'INVALID CURRENCY';
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
SELECT instance_code
INTO l_instance2
FROM edw_local_instance@edw_apps_to_wh;
UPDATE FII_AP_SCH_PAYMTS_FSTG
SET COLLECTION_STATUS = 'READY'
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
Insert Into FII_AP_SCH_PAYMTS_FSTG(
CCID,
COMPLETELY_PAID_PAYMENT_COUNT,
CREATION_DATE,
DISC_AMT_LOST_B,
DISC_AMT_LOST_G,
DISC_AMT_LOST_T,
DISC_AMT_TAKEN_B,
DISC_AMT_TAKEN_G,
DISC_AMT_TAKEN_T,
DISC_DATE_FK,
DUE_DATE,
DUE_DATE_FK,
DUNS_FK,
EMPLOYEE_FK,
FIRST_DISC_AVAILABLE_B,
FIRST_DISC_AVAILABLE_G,
FIRST_DISC_AVAILABLE_T,
FIRST_DISC_DATE,
GEOGRAPHY_FK,
GL_ACCT10_FK,
GL_ACCT1_FK,
GL_ACCT2_FK,
GL_ACCT3_FK,
GL_ACCT4_FK,
GL_ACCT5_FK,
GL_ACCT6_FK,
GL_ACCT7_FK,
GL_ACCT8_FK,
GL_ACCT9_FK,
HOLD_FLAG,
INSTANCE,
INSTANCE_FK,
INV_AMT_HAVING_DISC_B,
INV_AMT_HAVING_DISC_G,
INV_AMT_HAVING_DISC_T,
INV_AMT_NOT_HAVING_DISC_B,
INV_AMT_NOT_HAVING_DISC_G,
INV_AMT_NOT_HAVING_DISC_T,
INV_CURRENCY_FK,
INV_DATE_FK,
INV_EXCHANGE_DATE,
INV_EXCHANGE_RATE,
INV_EXCHANGE_RATE_TYPE,
INV_FK,
INV_SOURCE_FK,
INVOICE_NUM,
LAST_UPDATE_DATE,
ORG_FK,
PARTIALLY_PAID_PAYMENT_COUNT,
PAYMENT_AMT_B,
PAYMENT_AMT_G,
PAYMENT_AMT_T,
PAYMENT_CURRENCY_FK,
PAYMENT_EXCHANGE_DATE,
PAYMENT_EXCHANGE_RATE,
PAYMENT_EXCHANGE_RATE_TYPE,
PAYMENT_METHOD,
PAYMENT_PRIORITY,
PAYMENT_STATUS_FLAG,
PAYMENT_TERM_FK,
POTENTIAL_DISC_AMT_B,
POTENTIAL_DISC_AMT_G,
POTENTIAL_DISC_AMT_T,
REMAINING_DISC_AMT_AT_RISK_B,
REMAINING_DISC_AMT_AT_RISK_G,
REMAINING_DISC_AMT_AT_RISK_T,
REMAINING_INV_AMT_B,
REMAINING_INV_AMT_G,
REMAINING_INV_AMT_T,
REMAINING_INV_AMT_AT_RISK_B,
REMAINING_INV_AMT_AT_RISK_G,
REMAINING_INV_AMT_AT_RISK_T,
SCH_PAYMENT_PK,
SECOND_DISC_AVAILABLE_B,
SECOND_DISC_AVAILABLE_G,
SECOND_DISC_AVAILABLE_T,
SECOND_DISC_DATE,
SIC_CODE_FK,
SOB_FK,
SUPPLIER_FK,
THIRD_DISC_AVAILABLE_B,
THIRD_DISC_AVAILABLE_G,
THIRD_DISC_AVAILABLE_T,
THIRD_DISC_DATE,
UNPAID_PAYMENT_COUNT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS,
FROM_CURRENCY)
select
CCID,
COMPLETELY_PAID_PAYMENT_COUNT,
DECODE(g_acct_or_inv_date,1,GL_DATE,INVOICE_DATE),
DISC_AMT_LOST_B,
round((DISC_AMT_LOST_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
DISC_AMT_LOST_G,
DISC_AMT_LOST_T,
DISC_AMT_TAKEN_B,
round((DISC_AMT_TAKEN_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
DISC_AMT_TAKEN_G,
DISC_AMT_TAKEN_T,
NVL(DISC_DATE_FK,'NA_EDW'),
DUE_DATE,
NVL(DUE_DATE_FK,'NA_EDW'),
NVL(DUNS_FK,'NA_EDW'),
NVL(EMPLOYEE_FK,'NA_EDW'),
FIRST_DISC_AVAILABLE_B,
round((FIRST_DISC_AVAILABLE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
FIRST_DISC_AVAILABLE_G,
FIRST_DISC_AVAILABLE_T,
FIRST_DISC_DATE,
NVL(GEOGRAPHY_FK,'NA_EDW'),
NVL(GL_ACCT10_FK,'NA_EDW'),
NVL(GL_ACCT1_FK,'NA_EDW'),
NVL(GL_ACCT2_FK,'NA_EDW'),
NVL(GL_ACCT3_FK,'NA_EDW'),
NVL(GL_ACCT4_FK,'NA_EDW'),
NVL(GL_ACCT5_FK,'NA_EDW'),
NVL(GL_ACCT6_FK,'NA_EDW'),
NVL(GL_ACCT7_FK,'NA_EDW'),
NVL(GL_ACCT8_FK,'NA_EDW'),
NVL(GL_ACCT9_FK,'NA_EDW'),
HOLD_FLAG,
INSTANCE,
NVL(INSTANCE_FK,'NA_EDW'),
INV_AMT_HAVING_DISC_B,
round((INV_AMT_HAVING_DISC_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
INV_AMT_HAVING_DISC_G,
INV_AMT_HAVING_DISC_T,
INV_AMT_NOT_HAVING_DISC_B,
round((INV_AMT_NOT_HAVING_DISC_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
INV_AMT_NOT_HAVING_DISC_G,
INV_AMT_NOT_HAVING_DISC_T,
NVL(INV_CURRENCY_FK,'NA_EDW'),
NVL(INV_DATE_FK,'NA_EDW'),
INV_EXCHANGE_DATE,
INV_EXCHANGE_RATE,
INV_EXCHANGE_RATE_TYPE,
NVL(INV_FK,'NA_EDW'),
NVL(INV_SOURCE_FK,'NA_EDW'),
INVOICE_NUM,
LAST_UPDATE_DATE,
NVL(ORG_FK,'NA_EDW'),
PARTIALLY_PAID_PAYMENT_COUNT,
PAYMENT_AMT_B,
round((PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
PAYMENT_AMT_G,
PAYMENT_AMT_T,
NVL(PAYMENT_CURRENCY_FK,'NA_EDW'),
PAYMENT_EXCHANGE_DATE,
PAYMENT_EXCHANGE_RATE,
PAYMENT_EXCHANGE_RATE_TYPE,
PAYMENT_METHOD,
PAYMENT_PRIORITY,
PAYMENT_STATUS_FLAG,
NVL(PAYMENT_TERM_FK,'NA_EDW'),
POTENTIAL_DISC_AMT_B,
round((POTENTIAL_DISC_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
POTENTIAL_DISC_AMT_G,
POTENTIAL_DISC_AMT_T,
REMAINING_DISC_AMT_AT_RISK_B,
round((REMAINING_DISC_AMT_AT_RISK_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
REMAINING_DISC_AMT_AT_RISK_G,
REMAINING_DISC_AMT_AT_RISK_T,
REMAINING_INV_AMT_B,
round((REMAINING_INV_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
REMAINING_INV_AMT_G,
REMAINING_INV_AMT_T,
REMAINING_INV_AMT_AT_RISK_B,
round((REMAINING_INV_AMT_AT_RISK_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
REMAINING_INV_AMT_AT_RISK_G,
REMAINING_INV_AMT_AT_RISK_T,
SCH_PAYMENT_PK,
SECOND_DISC_AVAILABLE_B,
round((SECOND_DISC_AVAILABLE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
SECOND_DISC_AVAILABLE_G,
SECOND_DISC_AVAILABLE_T,
SECOND_DISC_DATE,
NVL(SIC_CODE_FK,'NA_EDW'),
NVL(SOB_FK,'NA_EDW'),
NVL(SUPPLIER_FK,'NA_EDW'),
THIRD_DISC_AVAILABLE_B,
round((THIRD_DISC_AVAILABLE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau
THIRD_DISC_AVAILABLE_G,
THIRD_DISC_AVAILABLE_T,
THIRD_DISC_DATE,
UNPAID_PAYMENT_COUNT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
NVL(USER_FK1,'NA_EDW'),
NVL(USER_FK2,'NA_EDW'),
NVL(USER_FK3,'NA_EDW'),
NVL(USER_FK4,'NA_EDW'),
NVL(USER_FK5,'NA_EDW'),
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
decode(GLOBAL_CURRENCY_RATE,
NULL, 'RATE NOT AVAILABLE',
-1, 'RATE NOT AVAILABLE',
-2, 'INVALID CURRENCY',
'LOCAL READY'),
FROM_CURRENCY
from FII_AP_SCH_PAYMTS_FCV;
Insert Into FII_AP_SCH_PAYMTS_FSTG@EDW_APPS_TO_WH(
CCID,
COMPLETELY_PAID_PAYMENT_COUNT,
CREATION_DATE,
DISC_AMT_LOST_B,
DISC_AMT_LOST_G,
DISC_AMT_LOST_T,
DISC_AMT_TAKEN_B,
DISC_AMT_TAKEN_G,
DISC_AMT_TAKEN_T,
DISC_DATE_FK,
DUE_DATE,
DUE_DATE_FK,
DUNS_FK,
EMPLOYEE_FK,
FIRST_DISC_AVAILABLE_B,
FIRST_DISC_AVAILABLE_G,
FIRST_DISC_AVAILABLE_T,
FIRST_DISC_DATE,
GEOGRAPHY_FK,
GL_ACCT10_FK,
GL_ACCT1_FK,
GL_ACCT2_FK,
GL_ACCT3_FK,
GL_ACCT4_FK,
GL_ACCT5_FK,
GL_ACCT6_FK,
GL_ACCT7_FK,
GL_ACCT8_FK,
GL_ACCT9_FK,
HOLD_FLAG,
INSTANCE,
INSTANCE_FK,
INV_AMT_HAVING_DISC_B,
INV_AMT_HAVING_DISC_G,
INV_AMT_HAVING_DISC_T,
INV_AMT_NOT_HAVING_DISC_B,
INV_AMT_NOT_HAVING_DISC_G,
INV_AMT_NOT_HAVING_DISC_T,
INV_CURRENCY_FK,
INV_DATE_FK,
INV_EXCHANGE_DATE,
INV_EXCHANGE_RATE,
INV_EXCHANGE_RATE_TYPE,
INV_FK,
INV_SOURCE_FK,
INVOICE_NUM,
LAST_UPDATE_DATE,
ORG_FK,
PARTIALLY_PAID_PAYMENT_COUNT,
PAYMENT_AMT_B,
PAYMENT_AMT_G,
PAYMENT_AMT_T,
PAYMENT_CURRENCY_FK,
PAYMENT_EXCHANGE_DATE,
PAYMENT_EXCHANGE_RATE,
PAYMENT_EXCHANGE_RATE_TYPE,
PAYMENT_METHOD,
PAYMENT_PRIORITY,
PAYMENT_STATUS_FLAG,
PAYMENT_TERM_FK,
POTENTIAL_DISC_AMT_B,
POTENTIAL_DISC_AMT_G,
POTENTIAL_DISC_AMT_T,
REMAINING_DISC_AMT_AT_RISK_B,
REMAINING_DISC_AMT_AT_RISK_G,
REMAINING_DISC_AMT_AT_RISK_T,
REMAINING_INV_AMT_B,
REMAINING_INV_AMT_G,
REMAINING_INV_AMT_T,
REMAINING_INV_AMT_AT_RISK_B,
REMAINING_INV_AMT_AT_RISK_G,
REMAINING_INV_AMT_AT_RISK_T,
SCH_PAYMENT_PK,
SECOND_DISC_AVAILABLE_B,
SECOND_DISC_AVAILABLE_G,
SECOND_DISC_AVAILABLE_T,
SECOND_DISC_DATE,
SIC_CODE_FK,
SOB_FK,
SUPPLIER_FK,
THIRD_DISC_AVAILABLE_B,
THIRD_DISC_AVAILABLE_G,
THIRD_DISC_AVAILABLE_T,
THIRD_DISC_DATE,
UNPAID_PAYMENT_COUNT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
select
CCID,
COMPLETELY_PAID_PAYMENT_COUNT,
CREATION_DATE,
DISC_AMT_LOST_B,
DISC_AMT_LOST_G,
DISC_AMT_LOST_T,
DISC_AMT_TAKEN_B,
DISC_AMT_TAKEN_G,
DISC_AMT_TAKEN_T,
NVL(DISC_DATE_FK,'NA_EDW'),
DUE_DATE,
NVL(DUE_DATE_FK,'NA_EDW'),
NVL(DUNS_FK,'NA_EDW'),
NVL(EMPLOYEE_FK,'NA_EDW'),
FIRST_DISC_AVAILABLE_B,
FIRST_DISC_AVAILABLE_G,
FIRST_DISC_AVAILABLE_T,
FIRST_DISC_DATE,
NVL(GEOGRAPHY_FK,'NA_EDW'),
NVL(GL_ACCT10_FK,'NA_EDW'),
NVL(GL_ACCT1_FK,'NA_EDW'),
NVL(GL_ACCT2_FK,'NA_EDW'),
NVL(GL_ACCT3_FK,'NA_EDW'),
NVL(GL_ACCT4_FK,'NA_EDW'),
NVL(GL_ACCT5_FK,'NA_EDW'),
NVL(GL_ACCT6_FK,'NA_EDW'),
NVL(GL_ACCT7_FK,'NA_EDW'),
NVL(GL_ACCT8_FK,'NA_EDW'),
NVL(GL_ACCT9_FK,'NA_EDW'),
substrb(HOLD_FLAG,1,1),
substrb(INSTANCE,1,40),
NVL(INSTANCE_FK,'NA_EDW'),
INV_AMT_HAVING_DISC_B,
INV_AMT_HAVING_DISC_G,
INV_AMT_HAVING_DISC_T,
INV_AMT_NOT_HAVING_DISC_B,
INV_AMT_NOT_HAVING_DISC_G,
INV_AMT_NOT_HAVING_DISC_T,
NVL(INV_CURRENCY_FK,'NA_EDW'),
NVL(INV_DATE_FK,'NA_EDW'),
INV_EXCHANGE_DATE,
INV_EXCHANGE_RATE,
substrb(INV_EXCHANGE_RATE_TYPE,1,30),
NVL(INV_FK,'NA_EDW'),
NVL(INV_SOURCE_FK,'NA_EDW'),
substrb(INVOICE_NUM,1,50),
LAST_UPDATE_DATE,
NVL(ORG_FK,'NA_EDW'),
PARTIALLY_PAID_PAYMENT_COUNT,
PAYMENT_AMT_B,
PAYMENT_AMT_G,
PAYMENT_AMT_T,
NVL(PAYMENT_CURRENCY_FK,'NA_EDW'),
PAYMENT_EXCHANGE_DATE,
PAYMENT_EXCHANGE_RATE,
substrb(PAYMENT_EXCHANGE_RATE_TYPE,1,30),
substrb(PAYMENT_METHOD,1,25),
PAYMENT_PRIORITY,
substrb(PAYMENT_STATUS_FLAG,1,1),
NVL(PAYMENT_TERM_FK,'NA_EDW'),
POTENTIAL_DISC_AMT_B,
POTENTIAL_DISC_AMT_G,
POTENTIAL_DISC_AMT_T,
REMAINING_DISC_AMT_AT_RISK_B,
REMAINING_DISC_AMT_AT_RISK_G,
REMAINING_DISC_AMT_AT_RISK_T,
REMAINING_INV_AMT_B,
REMAINING_INV_AMT_G,
REMAINING_INV_AMT_T,
REMAINING_INV_AMT_AT_RISK_B,
REMAINING_INV_AMT_AT_RISK_G,
REMAINING_INV_AMT_AT_RISK_T,
substrb(SCH_PAYMENT_PK,1,120),
SECOND_DISC_AVAILABLE_B,
SECOND_DISC_AVAILABLE_G,
SECOND_DISC_AVAILABLE_T,
SECOND_DISC_DATE,
NVL(SIC_CODE_FK,'NA_EDW'),
NVL(SOB_FK,'NA_EDW'),
NVL(SUPPLIER_FK,'NA_EDW'),
THIRD_DISC_AVAILABLE_B,
THIRD_DISC_AVAILABLE_G,
THIRD_DISC_AVAILABLE_T,
THIRD_DISC_DATE,
UNPAID_PAYMENT_COUNT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
NVL(USER_FK1,'NA_EDW'),
NVL(USER_FK2,'NA_EDW'),
NVL(USER_FK3,'NA_EDW'),
NVL(USER_FK4,'NA_EDW'),
NVL(USER_FK5,'NA_EDW'),
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
'READY'
from FII_AP_SCH_PAYMTS_FSTG
WHERE collection_status = 'LOCAL READY';
l_stmt := ' INSERT INTO fii_ap_tmp_schp_pk(
Primary_Key1,
Primary_Key2,
Primary_Key5)
SELECT
aps.invoice_id,
aps.payment_num,
:g_acct_or_inv_date
FROM ap_payment_schedules_all aps,
ap_invoices_all ai
WHERE aps.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
AND aps.invoice_id = ai.invoice_id'||l_er_stmt||'
UNION
SELECT
aps.invoice_id,
aps.payment_num,
:g_acct_or_inv_date
FROM ap_payment_schedules_all aps,
ap_invoices_all ai
WHERE ai.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
AND ai.invoice_id = aps.invoice_id'||l_er_stmt||'
UNION
SELECT
aps.invoice_id,
aps.payment_num,
:g_acct_or_inv_date
FROM ap_payment_schedules_all aps,
ap_invoice_payments_all aip,
ap_invoices_all ai
WHERE aip.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
AND aip.invoice_id = aps.invoice_id
AND aip.payment_num = aps.payment_num
AND aip.invoice_id = ai.invoice_id'||l_er_stmt;
cursor c1 is select DISTINCT FROM_CURRENCY,
CREATION_DATE CONVERSION_DATE,
COLLECTION_STATUS
From FII_AP_SCH_PAYMTS_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND trunc(CREATION_DATE) <= trunc(sysdate);
cursor c2 is select DISTINCT FROM_CURRENCY,
CREATION_DATE CONVERSION_DATE,
COLLECTION_STATUS
From FII_AP_SCH_PAYMTS_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY' )
AND trunc(CREATION_DATE) > trunc(sysdate);
execute immediate 'select count(*) from FII_AP_SCHP_MSNG_RATES' into l_count;
insert into fii_ap_schp_msng_rates(Primary_Key1,
Primary_key2,
Primary_key3)
select Primary_key1,
Primary_key2,
Primary_Key5
from fii_ap_tmp_schp_pk;
Insert into fii_ap_tmp_schp_pk(Primary_Key1,
Primary_Key2,
Primary_Key5)
select Primary_Key1,
Primary_Key2,
Primary_Key3
from fii_ap_schp_msng_rates;
DELETE_STG;
select count(*) into rows1 from FII_AP_SCH_PAYMTS_FSTG;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the local staging table');
select count(*) into rows from fii_ap_tmp_schp_pk ;
INSERT_MISSING_RATES_IN_TMP;
select /*+ FULL(SP) CACHE(SP) */
warehouse_currency_code into l_to_currency
from edw_local_system_parameters SP;
DELETE_STG;
delete from fii_ap_schp_msng_rates;
insert into fii_ap_schp_msng_rates(Primary_Key1,
Primary_Key2,
Primary_Key3)
select Primary_Key1,
Primary_Key2,
Primary_Key5
from fii_ap_tmp_schp_pk;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
edw_log.put_line('Inserting into local staging have failed');