[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_INTO_MISSING_RATES
IS
BEGIN
INSERT INTO fii_ar_trx_msng_rt(
Primary_Key1,
Primary_Key2,
Primary_Key3 -- SLA Uptake
)
SELECT
TO_NUMBER(decode(substr(INVOICE_PK,1,2), 'D-',INVOICE_DIST_ID,
'OD',INVOICE_DIST_ID,
'OC',INVOICE_DIST_ID,
'R-',INVOICE_DIST_ID, NULL)),
TO_NUMBER(decode(substr(INVOICE_PK,1,2), 'A-',INVOICE_ID, NULL)),
fat.account_id -- SLA Uptake
FROM FII_AR_TRX_DIST_FSTG fat
WHERE
fat.COLLECTION_STATUS in ('RATE NOT AVAILABLE', 'INVALID CURRENCY');
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows into fii_ar_trx_msng_rt temp table');
'as select rev.item_fk INVENTORY_ITEM_ID, ' ||
' NULL ORDER_LINE_ID, ' ||
' DECODE(mi.inventory_item_id, NULL, ''NA_EDW'', ' ||
' mi.inventory_item_id || ''-'' || to_char(max(mi.organization_id)) || ' ||
' ''-'' || rev.instance || ''-IORG'') ITEM_FK, ' ||
' NULL UOM_FK ' ||
'FROM FII_AR_TRX_DIST_Fstg rev, mtl_system_items_b mi ' ||
'WHERE to_number(rev.item_fk) = mi.inventory_item_id (+) ' ||
'AND rev.item_fk not like ''%-%'' ' ||
'AND rev.item_fk <> ''NA_EDW'' ' ||
'GROUP BY rev.item_fk, mi.inventory_item_id, rev.instance ' ||
'UNION ' ||
'SELECT NULL INVENTORY_ITEM_ID, ' ||
' to_char(ood.line_id) ORDER_LINE_ID, ' ||
' decode(msi.inventory_item_id, NULL, ''NA_EDW'', ' ||
' msi.inventory_item_id||''-''||iwm.mtl_organization_id||''-''|| ' ||
' rev.instance||''-IORG'') ITEM_FK, ' ||
' edw_util.get_edw_base_uom(msi.primary_uom_code, ' ||
' msi.inventory_item_id) UOM_FK ' ||
'FROM FII_AR_TRX_DIST_Fstg rev, op_ordr_dtl ood, ic_whse_mst iwm, ' ||
' ic_item_mst iim, mtl_system_items msi ' ||
'WHERE rev.interface_line_context = ''GEMMS OP'' ' ||
'AND rev.item_fk like ''OPM-%'' ' ||
'AND substr(rev.order_line_id,1,instr(rev.order_line_id,''-'',1)-1)= ' ||
'to_char(ood.line_id) ' ||
'AND ood.item_id = iim.item_id ' ||
'AND ood.from_whse = iwm.whse_code ' ||
'AND iim.item_no = msi.segment1 ' ||
'AND iwm.mtl_organization_id = msi.organization_id';
FUNCTION update_item_fk RETURN NUMBER is
l_stmt VARCHAR2(6000);
edw_log.debug_line('Ready to Update Item FK' );
l_stmt:= 'UPDATE FII_AR_TRX_DIST_FSTG rev ' ||
'SET (rev.uom_fk, rev.item_fk, rev.parent_item_fk) = ' ||
' (SELECT DECODE(rev.interface_line_context, ''GEMMS OP'', ' ||
' NVL(temp.uom_fk, ''NA_EDW''), rev.uom_fk) UOM_FK, ' ||
'NVL(temp.item_fk, ''NA_EDW'') ITEM_FK, ' ||
'NVL(temp.item_fk, ''NA_EDW'') PARENT_ITEM_FK ' ||
'FROM ' || l_table_name || ' temp ' ||
'WHERE (temp.inventory_item_id = rev.item_fk ' ||
'OR (substr(rev.order_line_id,1,instr(rev.order_line_id,''-'',1)-1)= ' ||
' temp.order_line_id ' ||
' AND rev.interface_line_context = ''GEMMS OP''))) ' ||
'WHERE rev.item_fk <> ''NA_EDW'' ' ||
'AND rev.collection_status = ''LOCAL READY'' ' ||
'AND INSTANCE = (SELECT INSTANCE_CODE FROM EDW_LOCAL_INSTANCE) ' ||
'AND rev.item_fk NOT LIKE ''%-IORG'' ';
PROCEDURE DELETE_STG
IS
BEGIN
DELETE FII_AR_TRX_DIST_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);
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_AR_TRX_DIST_FSTG
SET COLLECTION_STATUS = 'READY'
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
Insert Into FII_AR_TRX_DIST_FSTG(
ACCOUNT_ID,
ACCOUNT_CLASS,
ACCOUNT_TYPE,
AGREEMENT_NAME,
AGREEMENT_TYPE,
AMT_B,
AMT_G,
AMT_T,
BILL_TO_CUSTOMER_FK,
BILL_TO_SITE_FK,
CAMPAIGN_ACTL_FK,
CAMPAIGN_INIT_FK,
CAMPAIGN_STATUS_ACTL_FK,
CAMPAIGN_STATUS_INIT_FK,
CELL_ACTL_FK,
CELL_INIT_FK,
COMPANY_CC_ORG_FK,
END_USER_CUSTOMER_FK,
EVENT_OFFER_ACTL_FK,
EVENT_OFFER_INIT_FK,
EVENT_OFFER_REG_FK,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
FUNCTIONAL_CURRENCY_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,
GL_ACCT10_FK,
GL_DATE,
GL_DATE_FK,
INSTANCE,
INSTANCE_FK,
INTERFACE_LINE_CONTEXT,
INTERNAL_FLAG,
INVOICE_DATE,
INVOICE_DATE_FK,
INVOICE_DIST_ID,
INVOICE_ID,
INVOICE_LINE_ID,
INVOICE_LINE_NUMBER,
INVOICE_LINE_MEMO,
INVOICE_NUMBER,
INVOICE_REASON,
INVOICE_SOURCE_NAME,
ITEM_FK,
QTY_CREDITED,
INVOICE_LINE_QTY,
ORDER_LINE_QTY,
LINE_TYPE,
MARKET_SEGMENT_FK,
MEDIA_ACTL_FK,
MEDIA_INIT_FK,
OFFER_ACTL_FK,
OFFER_INIT_FK,
ORDER_LINE_ID,
ORGANIZATION_FK,
ORIGINAL_INVOICE_ID,
ORIGINAL_INVOICE_LINE_ID,
ORIGINAL_INVOICE_NUM,
ORIGINAL_INVOICE_LINE_NUM,
PARENT_ITEM_FK,
PAYMENT_TERM_FK,
prim_salesrep_fk,
PRIM_SALESRESOURCE_FK,
PROCESS_TYPE,
PROJECT_FK,
RESELLER_CUSTOMER_FK,
INVOICE_PK,
SALES_ORDER_DATE_FK,
SALES_ORDER_LINE_NUMBER,
SALES_ORDER_NUMBER,
SALES_ORDER_SOURCE,
SALESCHANNEL_FK,
salesrep_fk,
SALESRESOURCE_FK,
SET_OF_BOOKS_FK,
SHIP_TO_CUSTOMER_FK,
SHIP_TO_SITE_FK,
SIC_CODE_FK,
SO_LINE_SELLING_PRICE,
SOLD_TO_CUSTOMER_FK,
SOLD_TO_SITE_FK,
SOURCE_LIST_FK,
TRANSACTION_CURRENCY_FK,
UNIT_SELLING_PRICE,
UOM_FK,
GL_POSTED_DATE,
TRANSACTION_STATUS,
TRANSACTION_CLASS,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
CREATION_DATE,
LAST_UPDATE_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
ACCOUNT_ID,
ACCOUNT_CLASS,
ACCOUNT_TYPE,
AGREEMENT_NAME,
AGREEMENT_TYPE,
AMT_B,
round(( AMT_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau,
AMT_T,
BILL_TO_CUSTOMER_FK,
BILL_TO_SITE_FK,
CAMPAIGN_ACTL_FK,
CAMPAIGN_INIT_FK,
CAMPAIGN_STATUS_ACTL_FK,
CAMPAIGN_STATUS_INIT_FK,
CELL_ACTL_FK,
CELL_INIT_FK,
'NA_EDW', -- COMPANY_CC_ORG_FK,
END_USER_CUSTOMER_FK,
EVENT_OFFER_ACTL_FK,
EVENT_OFFER_INIT_FK,
EVENT_OFFER_REG_FK,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
FUNCTIONAL_CURRENCY_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,
GL_ACCT10_FK,
GL_DATE,
GL_DATE_FK,
INSTANCE,
INSTANCE_FK,
INTERFACE_LINE_CONTEXT,
INTERNAL_FLAG,
INVOICE_DATE,
INVOICE_DATE_FK,
INVOICE_DIST_ID,
INVOICE_ID,
INVOICE_LINE_ID,
INVOICE_LINE_NUMBER,
INVOICE_LINE_MEMO,
INVOICE_NUMBER,
INVOICE_REASON,
INVOICE_SOURCE_NAME,
ITEM_FK,
QTY_CREDITED_NC * UOM_CONV_RATE,
INVOICE_LINE_QTY_NC * UOM_CONV_RATE,
ORDER_LINE_QTY_NC * UOM_CONV_RATE,
LINE_TYPE,
MARKET_SEGMENT_FK,
MEDIA_ACTL_FK,
MEDIA_INIT_FK,
OFFER_ACTL_FK,
OFFER_INIT_FK,
ORDER_LINE_ID,
ORGANIZATION_FK,
ORIGINAL_INVOICE_ID,
ORIGINAL_INVOICE_LINE_ID,
ORIGINAL_INVOICE_NUM,
ORIGINAL_INVOICE_LINE_NUM,
PARENT_ITEM_FK,
PAYMENT_TERM_FK,
prim_salesresource_fk, -- 'NA_EDW',
'NA_EDW', -- PRIM_SALESRESOURCE_FK,
PROCESS_TYPE,
PROJECT_FK,
RESELLER_CUSTOMER_FK,
INVOICE_PK,
SALES_ORDER_DATE_FK,
SALES_ORDER_LINE_NUMBER,
SALES_ORDER_NUMBER,
SALES_ORDER_SOURCE,
SALESCHANNEL_FK,
salesresource_fk, -- 'NA_EDW',
'NA_EDW', -- SALESRESOURCE_FK,
SET_OF_BOOKS_FK,
SHIP_TO_CUSTOMER_FK,
SHIP_TO_SITE_FK,
SIC_CODE_FK,
SO_LINE_SELLING_PRICE,
SOLD_TO_CUSTOMER_FK,
SOLD_TO_SITE_FK,
SOURCE_LIST_FK,
TRANSACTION_CURRENCY_FK,
UNIT_SELLING_PRICE,
UOM_FK,
GL_POSTED_DATE,
TRANSACTION_STATUS,
TRANSACTION_CLASS,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
sysdate,
sysdate,
NULL, -- OPERATION_CODE
decode(invoice_id,'NO_INV_ID','INVOICE_ID UNAVAILABLE',
decode(invoice_line_id,'NO_INV_LIN_ID','INVOICE_LINE_ID UNAVAILABLE',
decode(original_invoice_id,'NO_OR_INV_ID','ORIGINAL_INVOICE_ID UNAVAILABLE',
decode(original_invoice_line_id,'NO_OR_INV_LIN_ID','ORIGINAL_INVOICE_LINE_ID UNAVAILABLE',
decode(GLOBAL_CURRENCY_RATE,-1,'RATE NOT AVAILABLE',
-2,'INVALID CURRENCY','LOCAL READY')))))
FROM FII_AR_TRX_DIST_FCV
WHERE view_type = p_view_type
AND seq_id = g_seq_id;
INSERT INTO FII_AR_TRX_DIST_FSTG@EDW_APPS_TO_WH(
ACCOUNT_ID,
ACCOUNT_CLASS,
ACCOUNT_TYPE,
AGREEMENT_NAME,
AGREEMENT_TYPE,
AMT_B,
AMT_G,
AMT_T,
BILL_TO_CUSTOMER_FK,
BILL_TO_SITE_FK,
CAMPAIGN_ACTL_FK,
CAMPAIGN_INIT_FK,
CAMPAIGN_STATUS_ACTL_FK,
CAMPAIGN_STATUS_INIT_FK,
CELL_ACTL_FK,
CELL_INIT_FK,
COMPANY_CC_ORG_FK,
END_USER_CUSTOMER_FK,
EVENT_OFFER_ACTL_FK,
EVENT_OFFER_INIT_FK,
EVENT_OFFER_REG_FK,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
FUNCTIONAL_CURRENCY_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,
GL_ACCT10_FK,
GL_DATE,
GL_DATE_FK,
INSTANCE,
INSTANCE_FK,
INTERFACE_LINE_CONTEXT,
INTERNAL_FLAG,
INVOICE_DATE,
INVOICE_DATE_FK,
INVOICE_DIST_ID,
INVOICE_ID,
INVOICE_LINE_ID,
INVOICE_LINE_NUMBER,
INVOICE_LINE_MEMO,
INVOICE_NUMBER,
INVOICE_REASON,
INVOICE_SOURCE_NAME,
ITEM_FK,
QTY_CREDITED,
INVOICE_LINE_QTY,
ORDER_LINE_QTY,
LINE_TYPE,
MARKET_SEGMENT_FK,
MEDIA_ACTL_FK,
MEDIA_INIT_FK,
OFFER_ACTL_FK,
OFFER_INIT_FK,
ORDER_LINE_ID,
ORGANIZATION_FK,
ORIGINAL_INVOICE_ID,
ORIGINAL_INVOICE_LINE_ID,
ORIGINAL_INVOICE_NUM,
ORIGINAL_INVOICE_LINE_NUM,
PARENT_ITEM_FK,
PAYMENT_TERM_FK,
prim_salesrep_fk,
PRIM_SALESRESOURCE_FK,
PROCESS_TYPE,
PROJECT_FK,
RESELLER_CUSTOMER_FK,
INVOICE_PK,
SALES_ORDER_DATE_FK,
SALES_ORDER_LINE_NUMBER,
SALES_ORDER_NUMBER,
SALES_ORDER_SOURCE,
SALESCHANNEL_FK,
salesrep_fk,
SALESRESOURCE_FK,
SET_OF_BOOKS_FK,
SHIP_TO_CUSTOMER_FK,
SHIP_TO_SITE_FK,
SIC_CODE_FK,
SO_LINE_SELLING_PRICE,
SOLD_TO_CUSTOMER_FK,
SOLD_TO_SITE_FK,
SOURCE_LIST_FK,
TRANSACTION_CURRENCY_FK,
UNIT_SELLING_PRICE,
UOM_FK,
GL_POSTED_DATE,
TRANSACTION_STATUS,
TRANSACTION_CLASS,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
CREATION_DATE,
LAST_UPDATE_DATE,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
ACCOUNT_ID,
substrb(ACCOUNT_CLASS,1,3),
substrb(ACCOUNT_TYPE,1,4),
substrb(AGREEMENT_NAME,1,30),
substrb(AGREEMENT_TYPE,1,30),
AMT_B,
AMT_G,
AMT_T,
BILL_TO_CUSTOMER_FK,
BILL_TO_SITE_FK,
CAMPAIGN_ACTL_FK,
CAMPAIGN_INIT_FK,
CAMPAIGN_STATUS_ACTL_FK,
CAMPAIGN_STATUS_INIT_FK,
CELL_ACTL_FK,
CELL_INIT_FK,
COMPANY_CC_ORG_FK,
END_USER_CUSTOMER_FK,
EVENT_OFFER_ACTL_FK,
EVENT_OFFER_INIT_FK,
EVENT_OFFER_REG_FK,
EXCHANGE_DATE,
EXCHANGE_RATE,
substrb(EXCHANGE_RATE_TYPE,1,30),
FUNCTIONAL_CURRENCY_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,
GL_ACCT10_FK,
GL_DATE,
GL_DATE_FK,
substrb(INSTANCE,1,40),
INSTANCE_FK,
substrb(INTERFACE_LINE_CONTEXT,1,30),
substrb(INTERNAL_FLAG,1,3),
INVOICE_DATE,
INVOICE_DATE_FK,
substrb(INVOICE_DIST_ID,1,25),
substrb(INVOICE_ID,1,25),
substrb(INVOICE_LINE_ID,1,25),
substrb(INVOICE_LINE_NUMBER,1,10),
substrb(INVOICE_LINE_MEMO,1,15),
substrb(INVOICE_NUMBER,1,30),
substrb(INVOICE_REASON,1,30),
substrb(INVOICE_SOURCE_NAME,1,50),
ITEM_FK,
QTY_CREDITED,
INVOICE_LINE_QTY,
ORDER_LINE_QTY,
substrb(LINE_TYPE,1,3),
MARKET_SEGMENT_FK,
MEDIA_ACTL_FK,
MEDIA_INIT_FK,
OFFER_ACTL_FK,
OFFER_INIT_FK,
substrb(ORDER_LINE_ID,1,50),
ORGANIZATION_FK,
substrb(ORIGINAL_INVOICE_ID,1,15),
substrb(ORIGINAL_INVOICE_LINE_ID,1,25),
substrb(ORIGINAL_INVOICE_NUM,1,30),
substrb(ORIGINAL_INVOICE_LINE_NUM,1,10),
PARENT_ITEM_FK,
PAYMENT_TERM_FK,
prim_salesrep_fk,
PRIM_SALESRESOURCE_FK,
substrb(PROCESS_TYPE,1,1),
PROJECT_FK,
RESELLER_CUSTOMER_FK,
substrb(INVOICE_PK,1,120),
SALES_ORDER_DATE_FK,
substrb(SALES_ORDER_LINE_NUMBER,1,30),
substrb(SALES_ORDER_NUMBER,1,30),
substrb(SALES_ORDER_SOURCE,1,50),
SALESCHANNEL_FK,
salesrep_fk,
SALESRESOURCE_FK,
SET_OF_BOOKS_FK,
SHIP_TO_CUSTOMER_FK,
SHIP_TO_SITE_FK,
SIC_CODE_FK,
SO_LINE_SELLING_PRICE,
SOLD_TO_CUSTOMER_FK,
SOLD_TO_SITE_FK,
SOURCE_LIST_FK,
TRANSACTION_CURRENCY_FK,
UNIT_SELLING_PRICE,
UOM_FK,
GL_POSTED_DATE,
substrb(TRANSACTION_STATUS,1,1),
substrb(TRANSACTION_CLASS,1,3),
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
sysdate,
sysdate,
substrb(OPERATION_CODE,1,30),
'READY'
FROM FII_AR_TRX_DIST_FSTG
WHERE collection_status = 'LOCAL READY';
select fii_tmp_pk_s.nextval into l_seq_id from dual;
Insert into fii_tmp_pk(
SEQ_ID,
primary_key1,
primary_key_char5)
select /*+ PARALLEL(CT,4) */
l_seq_id,
ctlx.customer_trx_line_id,
p_mode
from ra_customer_trx_all ct,
ra_customer_trx_lines_all ctlx
where ct.last_update_date between g_push_from_date and g_push_to_date
and ct.complete_flag = 'Y'
and ct.customer_trx_id = ctlx.customer_trx_id
and exists (select 1 from ra_cust_trx_line_gl_dist_all ctlgd
where ctlgd.customer_trx_line_id=ctlx.customer_trx_line_id
and ctlgd.posting_control_id <> -3)
--added exists clause for SLA Uptake
UNION
select /*+ PARALLEL(CTLX,4) */
l_seq_id,
ctlx.customer_trx_line_id,
p_mode
from ra_customer_trx_lines_all ctlx
where ctlx.last_update_date between g_push_from_date and g_push_to_date
and exists (select 1 from ra_cust_trx_line_gl_dist_all ctlgd
where ctlgd.customer_trx_line_id=ctlx.customer_trx_line_id
and ctlgd.posting_control_id <> -3);
Insert into fii_tmp_pk(
SEQ_ID,
Primary_Key1,
primary_key_char5,
primary_key5,
primary_key4) --ccid
select l_seq_id,
ctlgd.cust_trx_line_gl_dist_id,
p_mode,
g_acct_or_inv_date,
xal.code_combination_id
from ra_cust_trx_line_gl_dist_all ctlgd,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl
where ctlgd.last_update_date between g_push_from_date and g_push_to_date
and ctlgd.account_set_flag = 'N'
and xah.application_id=222
and xal.application_id=222
and xdl.application_id=222
and xah.ae_header_id=xal.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ctlgd.cust_trx_line_gl_dist_id
and xdl.source_distribution_type='RA_CUST_TRX_LINE_GL_DIST_ALL'
and xah.ledger_id=ctlgd.set_of_books_id
and xah.balance_type_code='A'
UNION
select /*+ INDEX (CTLGD RA_CUST_TRX_LINE_GL_DIST_N1) */
l_seq_id,
ctlgd.cust_trx_line_gl_dist_id,
p_mode,
g_acct_or_inv_date,
xal.code_combination_id
from fii_tmp_pk ftr,
ra_cust_trx_line_gl_dist_all ctlgd,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl
where ftr.seq_id = p_parent_seq_id
and ftr.primary_key1 = ctlgd.customer_trx_line_id
and ctlgd.account_set_flag = 'N'
and xah.application_id=222
and xal.application_id=222
and xdl.application_id=222
and xah.ae_header_id=xal.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ctlgd.cust_trx_line_gl_dist_id
and xdl.source_distribution_type='RA_CUST_TRX_LINE_GL_DIST_ALL'
and xah.ledger_id=ctlgd.set_of_books_id
and xah.balance_type_code='A'
UNION
select l_seq_id,
primary_key1,
p_mode,
g_acct_or_inv_date,
primary_key3 --ccid
from fii_ar_trx_msng_rt;
Insert into fii_tmp_pk(
SEQ_ID,
primary_key1,
primary_key_char5 ,
primary_key4) --ccid
select
distinct l_seq_id,
adj.adjustment_id,
p_mode,
xal.code_combination_id
from ar_adjustments_all adj,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ar_distributions_all ad
where adj.last_update_date between g_push_from_date and g_push_to_date
and nvl(adj.status, 'A') = 'A'
and nvl(adj.postable,'Y') = 'Y'
and adj.amount <> 0
and xah.application_id=222
and xal.application_id=222
and xdl.application_id=222
and xah.ae_header_id=xal.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ad.line_id
and source_distribution_type='AR_DISTRIBUTIONS_ALL'
and ad.source_id=adj.adjustment_id
and ad.source_table='ADJ'
and xah.ledger_id=adj.set_of_books_id
and xah.balance_type_code='A'
UNION
select /*+ ORDERED
PARALLEL(CT,4)
INDEX(ADJ AR_ADJUSTMENTS_N2) */
distinct l_seq_id,
adj.adjustment_id,
p_mode,
xal.code_combination_id
from ra_customer_trx_all ct,
ar_adjustments_all adj,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
ar_distributions_all ad
where ct.last_update_date between g_push_from_date and g_push_to_date
and ct.complete_flag = 'Y'
and ct.customer_trx_id = adj.customer_trx_id
and nvl(adj.status, 'A') = 'A'
and nvl(adj.postable,'Y') = 'Y'
and adj.amount <> 0
and xah.application_id=222
and xal.application_id=222
and xdl.application_id=222
and xah.ae_header_id=xal.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
and xdl.SOURCE_DISTRIBUTION_ID_NUM_1=ad.line_id
and source_distribution_type='AR_DISTRIBUTIONS_ALL'
and ad.source_id=adj.adjustment_id
and ad.source_table='ADJ'
and xah.ledger_id=adj.set_of_books_id
and xah.balance_type_code='A'
UNION
select l_seq_id,
primary_key2,
p_mode ,
primary_key3 --ccid
from fii_ar_trx_msng_rt;
cursor miss_curr_past is select DISTINCT FUNCTIONAL_CURRENCY_FK FROM_CURRENCY,
DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
DECODE(g_acct_or_inv_date,1,GL_DATE,INVOICE_DATE)) CONVERSION_DATE,
COLLECTION_STATUS
From FII_AR_TRX_DIST_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND trunc(DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
DECODE(g_acct_or_inv_date,1, GL_DATE,INVOICE_DATE))) <= trunc(sysdate);
cursor miss_curr_future is select DISTINCT FUNCTIONAL_CURRENCY_FK FROM_CURRENCY,
DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
DECODE(g_acct_or_inv_date,1,GL_DATE,INVOICE_DATE)) CONVERSION_DATE,
COLLECTION_STATUS
From FII_AR_TRX_DIST_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND trunc(DECODE(substr(invoice_pk,1,2),'A-',GL_DATE,
DECODE(g_acct_or_inv_date,1, GL_DATE,INVOICE_DATE))) > trunc(sysdate);
DELETE_STG;
'deleted invoices: '||l_request_id4);
INSERT_INTO_MISSING_RATES;
select /*+ FULL(SP) CACHE(SP) */
warehouse_currency_code into l_to_currency
from edw_local_system_parameters SP;
g_row_count := update_item_fk;
edw_log.put_line('Updated '||g_row_count||' records');
DELETE_STG;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
delete fii_tmp_pk
where seq_id IN ( l_seq_id_line,
l_seq_id_dist_line);
delete fii_tmp_pk
where seq_id = g_seq_id;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the local staging table');
edw_log.put_line('Finding extra invoices in EDW which should be deleted');
edw_log.put_line('Inserting into staging area');
l_stmt := 'Begin FII_AR_TRX_DIST_F_D.Insert_Staging@EDW_APPS_TO_WH(:g_row_count); End; ';
edw_log.put_line('Inserting '||g_row_count||' records marked for deletion');
delete fii_tmp_pk -- clean out fii_tmp_pk table
where seq_id IN ( l_seq_id_line,
l_seq_id_dist_line,
l_seq_id_adjust_line);
DELETE_STG; -- Cleanup local staging table
rollback; -- Rollback insert into local staging
delete fii_tmp_pk -- clean out fii_tmp_pk table
where seq_id = g_seq_id;
edw_log.put_line('Inserting into local staging have failed');
rollback; -- rollback any insert into remote site
DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
delete fii_tmp_pk
where seq_id IN ( l_seq_id_line,
l_seq_id_dist_line,
l_seq_id_adjust_line,
g_seq_id);
DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
delete fii_tmp_pk
where seq_id IN ( l_seq_id_line,
l_seq_id_dist_line,
l_seq_id_adjust_line,
g_seq_id);