[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
s.ledger_id,
s.name
FROM gl_period_statuses ps,
gl_ledgers_public_v s,
(SELECT DISTINCT slga.ledger_id
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code =g_usage_code) fset
WHERE s.ledger_id = fset.ledger_id
AND ps.application_id = 200
AND ps.set_of_books_id = fset.ledger_id
AND ps.end_date >= p_start_date
AND ps.migration_status_code <> 'U';
SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
INTO l_result
FROM fii_change_log
WHERE log_item = 'AP_RESUMMARIZE';
SELECT 1
INTO l_count1
FROM fii_ap_inv_b
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_ap_inv_stg
WHERE ROWNUM = 1;
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'AP_RESUMMARIZE';
g_state := 'Inserting records into FII_AP_UNPOST_HEADERS_T table';
INSERT INTO FII_AP_UNPOST_HEADERS_T (
AE_HEADER_ID,
REF_AE_HEADER_ID,
TEMP_LINE_NUM,
SEQUENCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
SELECT AE_Header_ID AE_HEADER_ID,
Ref_AE_Header_ID REF_AE_HEADER_ID,
Temp_Line_Num TEMP_LINE_NUM,
Rownum SEQUENCE_ID,
sysdate LAST_UPDATE_DATE,
g_fii_user_id LAST_UPDATED_BY,
sysdate CREATION_DATE,
g_fii_user_id CREATED_BY,
g_fii_login_id LAST_UPDATE_LOGIN
FROM (SELECT AE_Header_ID, Ref_AE_Header_ID, Temp_Line_Num
FROM FII_AP_Inv_B
WHERE GL_SL_Link_ID IS NULL);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT||' records in FII_AP_UNPOST_HEADERS_T');
g_state := 'Populating FII_AP_Inv_STG with updated data using the FII_AP_Unpost_Headers_T table';
INSERT INTO FII_AP_INV_STG (
AE_HEADER_ID,
REF_AE_HEADER_ID,
TEMP_LINE_NUM,
GL_SL_LINK_ID,
GL_SL_LINK_TABLE)
SELECT XDL.AE_Header_ID AE_Header_ID,
XDL.Ref_AE_Header_ID Ref_AE_Header_ID,
XDL.Temp_Line_Num Temp_Line_Num,
XAL.GL_SL_Link_ID GL_SL_Link_ID,
XAL.GL_SL_Link_Table GL_SL_Link_Table
FROM FII_AP_Unpost_Headers_T ID,
XLA_AE_Lines XAL,
XLA_Distribution_Links XDL
WHERE ID.AE_Header_ID = XDL.AE_Header_ID
AND ID.Ref_AE_Header_ID = XDL.Ref_AE_Header_ID
AND ID.Temp_Line_Num = XDL.Temp_Line_Num
AND XDL.Application_ID = 200
AND XDL.AE_Header_ID = XAL.AE_Header_ID
AND XDL.AE_Line_Num = XAL.AE_Line_Num
AND XAL.Application_ID = 200
AND XAL.GL_SL_Link_ID IS NOT NULL
AND ID.sequence_id >= p_start_range
AND ID.sequence_id <= p_end_range;
FII_UTIL.put_line('Inserted '||sql%rowcount||' rows INTO FII_AP_INV_STG table');
INSERT INTO FII_AP_INV_STG
(LEDGER_ID,
ACCOUNT_DATE,
INV_CURRENCY_CODE,
AMOUNT_T,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
INVOICE_TYPE,
POSTED_FLAG,
FIN_CATEGORY_ID,
COMPANY_ID,
COST_CENTER_ID,
CHART_OF_ACCOUNTS_ID,
DIST_CCID,
PO_DISTRIBUTION_ID,
QUANTITY_INVOICED,
PROJECT_ID,
TASK_ID,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
APPROVED_FLAG,
ORG_ID,
EMPLOYEE_ID,
LINE_TYPE_LOOKUP_CODE,
INVOICE_NUM,
DISCRETIONARY_EXPENSE_FLAG,
TRANS_CURRENCY_CODE,
INVOICE_DATE,
DISTRIBUTION_LINE_NUMBER,
USER_DIM1_ID,
USER_DIM2_ID,
EXP_REPORT_HEADER_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_NUM,
AE_HEADER_ID,
REF_AE_HEADER_ID,
TEMP_LINE_NUM,
GL_SL_LINK_ID,
GL_SL_LINK_TABLE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
WITH ACCNT_CLASS AS (SELECT /*+ MATERIALIZE */ XAD.Ledger_ID,
XACA.Accounting_Class_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code = 'PAYABLES DBI EXPENSES'
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT /*+ ORDERED no_expand use_hash(XAL,XAH,XTE,XDL,RH,AID,AI,FND,PO,AIL)
parallel(ai) parallel(fnd) parallel(aid) parallel(po) parallel(xah) parallel(xte) parallel(xal) parallel(xdl) parallel(ail)
swap_join_inputs(AC) swap_join_inputs(FND) swap_join_inputs(PO)
pq_distribute(xah,hash,hash) pq_distribute(xte,hash,hash) pq_distribute(xal,hash,hash) pq_distribute(xdl,hash,hash)
pq_distribute(aid,hash,hash) pq_distribute(po,hash,hash) pq_distribute(fnd,hash,hash) pq_distribute(ai,hash,hash)
pq_distribute(ail,hash,hash)
*/ aid.set_of_books_id SET_OF_BOOKS_ID,
trunc(aid.accounting_date) ACCOUNT_DATE,
gsob.currency_code INV_CURRENCY_CODE,
NVL(XDL.Unrounded_Entered_DR, 0) - NVL(XDL.Unrounded_Entered_CR, 0) Amount_T,
aid.invoice_id INVOICE_ID,
aid.invoice_distribution_id INVOICE_DISTRIBUTION_ID,
NVL(XDL.Unrounded_Accounted_DR, 0) - NVL(XDL.Unrounded_Accounted_CR, 0) Amount_B,
decode(aid.po_distribution_id, Null, 'N', 'Y') PO_MATCHED_FLAG,
ai.source SOURCE,
nvl(fnd.EMPLOYEE_ID,-1) INV_DIST_CREATED_BY,
NVL(ai.vendor_site_id, -1) SUPPLIER_SITE_ID,
trunc(aid.creation_date) INV_DIST_CREATION_DATE,
ai.vendor_id SUPPLIER_ID,
ai.invoice_type_lookup_code INVOICE_TYPE,
NVL(aid.posted_flag, 'N') POSTED_FLAG,
glcc.NATURAL_ACCOUNT_ID FIN_CATEGORY_ID,
glcc.company_id,
glcc.cost_center_id,
gsob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
XAL.Code_Combination_ID DIST_CCID,
aid.po_distribution_id,
aid.quantity_invoiced,
aid.project_id,
aid.task_id,
decode(gsob.currency_code, g_prim_currency, 1,
fii_currency.get_global_rate_primary( gsob.currency_code,
trunc(least(aid.accounting_date,sysdate)))) PRIM_CONVERSION_RATE,
decode(gsob.currency_code, g_sec_currency, 1,
fii_currency.get_global_rate_secondary( gsob.currency_code,
trunc(least(aid.accounting_date,sysdate)))) SEC_CONVERSION_RATE,
NVL(aid.match_status_flag, 'N') APPROVED_FLAG,
NVL(ai.org_id, -1) ORG_ID,
nvl(po.employee_id, ai.paid_on_behalf_employee_id),
aid.LINE_TYPE_LOOKUP_CODE,
ai.invoice_num,
CASE
WHEN ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND ai.source in ('XpenseXpress', 'SelfService', 'CREDIT CARD', 'Oracle Project Accounting')
AND NVL(aid.match_status_flag, 'N') = 'A'
THEN 'Y'
WHEN ai.invoice_type_lookup_code = 'STANDARD'
AND ai.source = 'CREDIT CARD'
AND ai.PAID_ON_BEHALF_EMPLOYEE_ID is not null
AND NVL(aid.match_status_flag, 'N') = 'A'
THEN 'Y'
ELSE 'N'
END Discretionary_Expense_Flag,
ai.invoice_currency_code Trans_Currency_Code,
ai.invoice_date Invoice_Date,
aid.distribution_line_number Distribution_Line_Number,
glcc.user_dim1_id,
glcc.user_dim2_id,
rh.report_header_id Exp_Report_Header_ID,
PD.PO_Header_ID,
PD.PO_Release_ID,
PH.Segment1 PO_Num,
XAH.AE_Header_ID AE_Header_ID,
XDL.Ref_AE_Header_ID Ref_AE_Header_ID,
XDL.Temp_Line_Num Temp_Line_Num,
XAL.GL_SL_Link_ID GL_SL_Link_ID,
XAL.GL_SL_Link_Table GL_SL_Link_Table,
AIL.Inventory_Item_ID Inventory_Item_ID,
AIL.Purchasing_Category_ID Purchasing_Category_ID,
AIL.Item_Description Item_Description
FROM
XLA_AE_Lines XAL,
ACCNT_CLASS AC,
XLA_Distribution_Links XDL,
ap_invoice_distributions_all aid,
XLA_AE_Headers XAH,
XLA_Transaction_Entities XTE,
AP_Invoice_Lines_ALL AIL,
ap_invoices_all ai,
po_vendors po, --AP_SUPPLIERS PO,
fnd_user fnd,
(select vouchno,
case when count(1) > 1 then -1
else min(report_header_id) end report_header_id
from ap_expense_report_headers_all
where accounting_date >= g_start_date
group by vouchno) rh,
po_distributions_all pd,
po_headers_all ph,
gl_ledgers_public_v gsob,
fii_gl_ccid_dimensions glcc,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE XAH.Entity_ID = XTE.Entity_ID
AND XAH.AE_Header_ID = XAL.AE_Header_ID
AND XAL.AE_Header_ID = XDL.AE_Header_ID
AND XAL.AE_Line_Num = XDL.AE_Line_Num
AND XAH.Ledger_ID = AID.Set_Of_Books_ID
AND XAL.Accounting_Class_Code = AC.Accounting_Class_Code
AND (AC.ledger_id IS NULL OR AID.Set_Of_Books_ID = AC.Ledger_ID)
AND XDL.Source_Distribution_Type IN ('AP_INV_DIST', 'AP_PMT_DIST', 'AP_PREPAY')
AND XDL.Source_Distribution_ID_Num_1 = AID.Invoice_Distribution_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND ai.invoice_id = aid.invoice_id
AND aid.set_of_books_id = gsob.ledger_id
AND XAL.Code_Combination_ID = glcc.code_combination_id
AND glcc.chart_of_accounts_id = slga.chart_of_accounts_id
AND (glcc.company_id = slga.bal_seg_value_id
OR slga.bal_seg_value_id = -1 )
AND aid.set_of_books_id = slga.ledger_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
AND XAH.Application_ID = 200
AND XAH.Balance_Type_Code = 'A'
AND XTE.Application_ID = 200
AND XAL.Application_ID = 200
AND XDL.Application_ID = 200
AND XAH.Accounting_Entry_Status_Code = 'F'
AND XTE.Entity_Code = 'AP_INVOICES'
AND XAH.AE_Header_ID >= p_start_range
AND XAH.AE_Header_ID <= p_end_range
AND ai.vendor_id = po.vendor_id
AND aid.accounting_date >= g_start_date
AND ai.invoice_id = rh.vouchno (+)
AND aid.po_distribution_id = pd.po_distribution_id (+)
AND pd.po_header_id = ph.po_header_id (+)
AND xal.accounting_date >= g_start_date
AND xah.accounting_date >= g_start_date
AND ail.accounting_date >= g_start_date;
FII_UTIL.put_line('Inserted '||sql%rowcount||' rows INTO FII_AP_INV_STG table');
SELECT nvl(max(sequence_id), -1), nvl(min(sequence_id), -1)
INTO l_max_number1, l_start_number1
FROM FII_AP_Unpost_Headers_T;
SELECT MAX(NVL(AE_Header_ID, -1)) + 1 INTO l_start_number2 FROM FII_AP_INV_B;
SELECT MAX(AE_Header_ID) INTO l_max_number2 FROM XLA_AE_HEADERS;
INSERT INTO FII_AP_SUM_WORK_JOBS (start_range, end_range, worker_number, status, phase_id)
VALUES (l_start_number1, least(l_end_number, l_max_number1), 0, 'UNASSIGNED', 1);
FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_AP_SUM_WORK_JOBS table');
INSERT INTO FII_AP_SUM_WORK_JOBS (start_range, end_range, worker_number, status, phase_id)
VALUES (l_start_number2, least(l_end_number, l_max_number2), 0, 'UNASSIGNED', 2);
FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_AP_SUM_WORK_JOBS table');
SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(prim_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(ACCOUNT_DATE, sysdate)) actg_dt
FROM FII_AP_INV_STG stg
WHERE stg.prim_conversion_rate < 0 ;
SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(sec_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'),LEAST(ACCOUNT_DATE, sysdate)) actg_dt
FROM FII_AP_INV_STG stg
WHERE stg.sec_conversion_rate < 0 ;
SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(prim_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
FROM FII_AP_INV_RATES_TEMP rates
WHERE rates.prim_conversion_rate < 0 ;
SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(sec_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
FROM FII_AP_INV_RATES_TEMP rates
WHERE rates.sec_conversion_rate < 0 ;
SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_RATES_TEMP rates WHERE rates.prim_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_RATES_TEMP rates WHERE rates.sec_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_STG stg WHERE stg.prim_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_STG stg WHERE stg.sec_conversion_rate < 0;
SELECT FII_AP_INV_B_S.nextval INTO seq_id FROM dual;
USING (SELECT *
FROM FII_AP_INV_STG
WHERE (nvl(prim_conversion_rate,1) > 0 OR nvl(sec_conversion_rate,1) > 0)
) STG
ON (BSUM.Ref_AE_Header_ID = STG.Ref_AE_Header_ID
AND BSUM.Temp_Line_Num = STG.Temp_Line_Num
AND BSUM.AE_Header_ID = STG.AE_Header_ID)
WHEN MATCHED THEN UPDATE SET BSUM.GL_SL_Link_ID = STG.GL_SL_Link_ID,
BSUM.GL_SL_Link_Table = STG.GL_SL_Link_Table
WHEN NOT MATCHED THEN INSERT(bsum.LEDGER_ID,
bsum.ACCOUNT_DATE,
bsum.INV_CURRENCY_CODE,
bsum.AMOUNT_T,
bsum.INVOICE_ID,
bsum.INVOICE_DISTRIBUTION_ID,
bsum.AMOUNT_B,
bsum.PO_MATCHED_FLAG,
bsum.SOURCE,
bsum.INV_DIST_CREATED_BY,
bsum.SUPPLIER_SITE_ID,
bsum.INV_DIST_CREATION_DATE,
bsum.SUPPLIER_ID,
bsum.INVOICE_TYPE,
bsum.POSTED_FLAG,
bsum.FIN_CATEGORY_ID,
bsum.COMPANY_ID,
bsum.COST_CENTER_ID,
bsum.CHART_OF_ACCOUNTS_ID,
bsum.DIST_CCID,
bsum.PO_DISTRIBUTION_ID,
bsum.QUANTITY_INVOICED,
bsum.PROJECT_ID,
bsum.TASK_ID,
bsum.PRIM_AMOUNT_G,
bsum.SEC_AMOUNT_G,
bsum.UPDATE_SEQUENCE,
bsum.APPROVED_FLAG,
bsum.ORG_ID,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login,
bsum.account_date_id,
bsum.employee_id,
bsum.dist_count,
bsum.LINE_TYPE_LOOKUP_CODE,
bsum.invoice_num,
bsum.discretionary_expense_flag,
bsum.trans_currency_code,
bsum.invoice_date,
bsum.distribution_line_number,
bsum.user_dim1_id,
bsum.user_dim2_id,
bsum.exp_report_header_id,
bsum.po_header_id,
bsum.po_release_id,
bsum.po_num,
BSUM.AE_Header_ID,
BSUM.Ref_AE_Header_ID,
BSUM.Temp_Line_Num,
BSUM.GL_SL_Link_ID,
BSUM.GL_SL_Link_Table,
BSUM.Inventory_Item_ID,
BSUM.Purchasing_Category_ID,
BSUM.Item_Description)
VALUES
(stg.LEDGER_ID,
stg.ACCOUNT_DATE,
stg.INV_CURRENCY_CODE,
stg.AMOUNT_T,
stg.INVOICE_ID,
stg.INVOICE_DISTRIBUTION_ID,
stg.AMOUNT_B,
stg.PO_MATCHED_FLAG,
stg.SOURCE,
stg.INV_DIST_CREATED_BY,
stg.SUPPLIER_SITE_ID,
stg.INV_DIST_CREATION_DATE,
stg.SUPPLIER_ID,
stg.INVOICE_TYPE,
stg.POSTED_FLAG,
stg.FIN_CATEGORY_ID,
stg.COMPANY_ID,
stg.COST_CENTER_ID,
stg.CHART_OF_ACCOUNTS_ID,
stg.DIST_CCID,
stg.PO_DISTRIBUTION_ID,
stg.QUANTITY_INVOICED,
stg.PROJECT_ID,
stg.TASK_ID,
round((stg.amount_b*stg.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
round((stg.amount_b*stg.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
seq_id,
stg.APPROVED_FLAG,
stg.org_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
to_number (to_char(stg.ACCOUNT_DATE, 'J')),
stg.employee_id,
1,
stg.LINE_TYPE_LOOKUP_CODE,
stg.invoice_num,
stg.discretionary_expense_flag,
stg.trans_currency_code,
trunc(stg.invoice_date),
stg.distribution_line_number,
stg.user_dim1_id,
stg.user_dim2_id,
stg.exp_report_header_id,
stg.po_header_id,
stg.po_release_id,
stg.po_num,
STG.AE_Header_ID,
STG.Ref_AE_Header_ID,
STG.Temp_Line_Num,
STG.GL_SL_Link_ID,
STG.GL_SL_Link_Table,
STG.Inventory_Item_ID,
STG.Purchasing_Category_ID,
STG.Item_Description);
SELECT tablespace_name
INTO g_tablespace
FROM all_tables
WHERE table_name = g_table_name
AND owner = g_fii_schema;
select user_conversion_type into g_prim_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_prim_rate_type;
select user_conversion_type into g_sec_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_sec_rate_type;
FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
program');
g_phase := 'Calling CCID Dimension update program';
PROCEDURE INSERT_INTO_STG (p_parallel_query IN NUMBER,
p_sort_area_size IN NUMBER,
p_hash_area_size IN NUMBER) IS
l_stmt VARCHAR2(1000);
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_STG F
(LEDGER_ID,
ACCOUNT_DATE,
INV_CURRENCY_CODE,
AMOUNT_T,
INVOICE_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
-- DIST_COUNT,
INVOICE_TYPE,
POSTED_FLAG,
FIN_CATEGORY_ID,
COMPANY_ID,
COST_CENTER_ID,
CHART_OF_ACCOUNTS_ID,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
APPROVED_FLAG,
ORG_ID,
INVOICE_DISTRIBUTION_ID,
DIST_CCID,
PO_DISTRIBUTION_ID,
QUANTITY_INVOICED,
PROJECT_ID,
TASK_ID,
EMPLOYEE_ID,
LINE_TYPE_LOOKUP_CODE,
INVOICE_NUM,
DISCRETIONARY_EXPENSE_FLAG,
TRANS_CURRENCY_CODE,
INVOICE_DATE,
DISTRIBUTION_LINE_NUMBER,
USER_DIM1_ID,
USER_DIM2_ID,
EXP_REPORT_HEADER_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_NUM,
AE_HEADER_ID,
REF_AE_HEADER_ID,
TEMP_LINE_NUM,
GL_SL_LINK_ID,
GL_SL_LINK_TABLE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
WITH ACCNT_CLASS AS (SELECT /*+ MATERIALIZE */ XAD.Ledger_ID,
XACA.Accounting_Class_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code = 'PAYABLES DBI EXPENSES'
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
select /*+ ORDERED no_expand use_hash(XAL,XAH,XTE,XDL,RH,AID,AI,FND,PO,AIL)
parallel(ai) parallel(fnd) parallel(aid) parallel(po) parallel(xah) parallel(xte) parallel(xal) parallel(xdl) parallel(ail)
swap_join_inputs(AC) swap_join_inputs(FND) swap_join_inputs(PO)
pq_distribute(xah,hash,hash) pq_distribute(xte,hash,hash) pq_distribute(xal,hash,hash) pq_distribute(xdl,hash,hash)
pq_distribute(aid,hash,hash) pq_distribute(po,hash,hash) pq_distribute(fnd,hash,hash) pq_distribute(ai,hash,hash)
pq_distribute(ail,hash,hash)
*/
aid.set_of_books_id set_of_books_id,
nvl(trunc(aid.accounting_date),trunc(aid.accounting_date)) account_date,
v.currency_code inv_currency_code,
NVL(XDL.Unrounded_Entered_DR, 0) - NVL(XDL.Unrounded_Entered_CR, 0) Amount_T,
aid.invoice_id invoice_id,
NVL(XDL.Unrounded_Accounted_DR, 0) - NVL(XDL.Unrounded_Accounted_CR, 0) Amount_B,
nvl2(aid.po_distribution_id, 'Y', 'N') po_matched_flag,
ai.source source,
nvl(fnd.employee_id, -1) inv_dist_created_by,
nvl(ai.vendor_site_id, -1) supplier_site_id,
trunc(aid.creation_date) inv_dist_creation_date,
ai.vendor_id supplier_id,
ai.invoice_type_lookup_code invoice_type,
nvl(aid.posted_flag, 'N') posted_flag,
v.natural_account_id fin_category_id,
v.company_id company_id,
v.cost_center_id cost_center_id,
v.chart_of_accounts_id chart_of_accounts_id,
-1 prim_conversion_rate,
-1 sec_conversion_rate,
nvl(aid.match_status_flag, 'N') approved_flag,
nvl(ai.org_id, -1) org_id,
aid.invoice_distribution_id invoice_distribution_id,
XAL.Code_Combination_ID dist_ccid,
aid.po_distribution_id po_distribution_id,
aid.quantity_invoiced quantity_invoiced,
aid.project_id project_id,
aid.task_id task_id,
nvl(po.employee_id, ai.paid_on_behalf_employee_id) employee_id,
aid.line_type_lookup_code line_type_lookup_code,
ai.invoice_num invoice_num,
CASE
WHEN ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND ai.source in ('XpenseXpress', 'SelfService', 'CREDIT CARD', 'Oracle Project Accounting')
AND nvl(aid.match_status_flag, 'N') = 'A'
THEN 'Y'
WHEN ai.invoice_type_lookup_code = 'STANDARD'
AND ai.source = 'CREDIT CARD'
AND ai.PAID_ON_BEHALF_EMPLOYEE_ID is not null
AND nvl(aid.match_status_flag, 'N') = 'A'
THEN 'Y'
ELSE 'N'
END Discretionary_Expense_Flag,
ai.invoice_currency_code Trans_Currency_Code,
ai.invoice_date Invoice_Date,
aid.distribution_line_number Distribution_Line_Number,
v.user_dim1_id,
v.user_dim2_id,
rh.report_header_id exp_report_header_id,
pd.po_header_id,
pd.po_release_id,
ph.segment1 po_num,
XAH.AE_Header_ID AE_Header_ID,
XDL.Ref_AE_Header_ID Ref_AE_Header_ID,
XDL.Temp_Line_Num Temp_Line_Num,
XAL.GL_SL_Link_ID GL_SL_Link_ID,
XAL.GL_SL_Link_Table GL_SL_Link_Table,
AIL.Inventory_Item_ID Inventory_Item_ID,
AIL.Purchasing_Category_ID Purchasing_Category_ID,
AIL.Item_Description Item_Description
from (select /*+ no_merge no_expand parallel(glcc) */
gsob.ledger_id, glcc.code_combination_id,
gsob.currency_code, glcc.natural_account_id,
glcc.company_id, glcc.cost_center_id,
gsob.chart_of_accounts_id, glcc.user_dim1_id,
glcc.user_dim2_id
from fii_source_ledger_groups fslg, fii_slg_assignments slga,
gl_ledgers_public_v gsob, fii_gl_ccid_dimensions glcc
where slga.chart_of_accounts_id = glcc.chart_of_accounts_id
and slga.bal_seg_value_id in (glcc.company_id, -1)
and slga.source_ledger_group_id = fslg.source_ledger_group_id
and slga.ledger_id = gsob.ledger_id
and fslg.usage_code = g_usage_code) v,
XLA_AE_Lines XAL,
ACCNT_CLASS AC,
XLA_Distribution_Links XDL,
ap_invoice_distributions_all aid,
XLA_AE_Headers XAH,
XLA_Transaction_Entities XTE,
AP_Invoice_Lines_ALL AIL,
ap_invoices_all ai,
po_vendors po, --AP_SUPPLIERS PO,
fnd_user fnd,
(SELECT /*+ no_merge parallel(H) */
VOUCHNO,
CASE WHEN COUNT(*) > 1 THEN -1
ELSE MIN(REPORT_HEADER_ID) END REPORT_HEADER_ID
FROM AP_EXPENSE_REPORT_HEADERS_ALL H
WHERE ACCOUNTING_DATE >= g_start_date
GROUP BY VOUCHNO) RH,
po_distributions_all pd,
po_headers_all ph
where XAH.Entity_ID = XTE.Entity_ID
AND XAH.AE_Header_ID = XAL.AE_Header_ID
AND XAL.AE_Header_ID = XDL.AE_Header_ID
AND XAL.AE_Line_Num = XDL.AE_Line_Num
AND XAH.Ledger_ID = AID.Set_Of_Books_ID
AND XAL.Accounting_Class_Code = AC.Accounting_Class_Code
AND (AC.ledger_id IS NULL OR AID.Set_Of_Books_ID = AC.Ledger_ID)
AND XDL.Source_Distribution_Type IN ('AP_INV_DIST', 'AP_PMT_DIST', 'AP_PREPAY')
AND XDL.Source_Distribution_ID_Num_1 = AID.Invoice_Distribution_ID
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
and ai.invoice_id = aid.invoice_id
and ai.vendor_id = po.vendor_id
and fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
and aid.set_of_books_id = v.ledger_id
and XAL.Code_Combination_ID = v.code_combination_id
AND XAH.Application_ID = 200
AND XAH.Balance_Type_Code = 'A'
AND XTE.Application_ID = 200
AND XAL.Application_ID = 200
AND XDL.Application_ID = 200
AND XAH.Accounting_Entry_Status_Code = 'F'
AND XTE.Entity_Code = 'AP_INVOICES'
and aid.accounting_date >= g_start_date
AND ai.invoice_id = rh.vouchno (+)
AND aid.po_distribution_id = pd.po_distribution_id (+)
AND pd.po_header_id = ph.po_header_id (+)
AND xal.accounting_date >= g_start_date
AND ail.ACCOUNTING_DATE >= g_start_date
AND xah.accounting_date >= g_start_date;
END INSERT_INTO_STG;
PROCEDURE INSERT_RATES IS
BEGIN
g_state := 'Loading data into rates table';
insert into fii_ap_inv_rates_temp
(FUNCTIONAL_CURRENCY,
TRX_DATE,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE)
select cc functional_currency,
dt trx_date,
decode(cc, g_prim_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(dt,sysdate))) PRIM_CONVERSION_RATE,
decode(cc, g_sec_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(dt,sysdate))) SEC_CONVERSION_RATE
from (
select /*+ no_merge */ distinct
inv_currency_code cc,
account_date dt
from FII_AP_INV_STG
);
END INSERT_RATES;
PROCEDURE INSERT_INTO_SUMMARY (p_parallel_query IN NUMBER) IS
l_stmt VARCHAR2(50);
SELECT FII_AP_INV_B_S.nextval INTO seq_id FROM dual;
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_B F (
LEDGER_ID,
ACCOUNT_DATE,
ACCOUNT_DATE_ID,
INV_CURRENCY_CODE,
AMOUNT_T,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
INVOICE_TYPE,
POSTED_FLAG,
FIN_CATEGORY_ID,
COMPANY_ID,
COST_CENTER_ID,
CHART_OF_ACCOUNTS_ID,
DIST_CCID,
PO_DISTRIBUTION_ID,
QUANTITY_INVOICED,
PROJECT_ID,
TASK_ID,
PRIM_AMOUNT_G,
SEC_AMOUNT_G,
UPDATE_SEQUENCE,
APPROVED_FLAG,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EMPLOYEE_ID,
DIST_COUNT,
LINE_TYPE_LOOKUP_CODE,
INVOICE_NUM,
DISCRETIONARY_EXPENSE_FLAG,
TRANS_CURRENCY_CODE,
INVOICE_DATE,
DISTRIBUTION_LINE_NUMBER,
USER_DIM1_ID,
USER_DIM2_ID,
EXP_REPORT_HEADER_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_NUM,
AE_HEADER_ID,
REF_AE_HEADER_ID,
TEMP_LINE_NUM,
GL_SL_LINK_ID,
GL_SL_LINK_TABLE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
SELECT /*+ PARALLEL(stg) PARALLEL(rates) */
stg.ledger_id,
stg.account_date,
to_number (to_char(stg.ACCOUNT_DATE, 'J')),
stg.inv_currency_code,
stg.amount_t amount_t,
stg.invoice_id,
stg.invoice_distribution_id,
stg.amount_b amount_b,
stg.po_matched_flag,
stg.source,
stg.inv_dist_created_by,
stg.supplier_site_id,
stg.inv_dist_creation_date,
stg.supplier_id,
stg.invoice_type,
stg.posted_flag,
stg.FIN_CATEGORY_ID,
stg.company_id,
stg.cost_center_id,
stg.chart_of_accounts_id,
stg.dist_ccid,
stg.po_distribution_id,
stg.quantity_invoiced,
stg.project_id,
stg.task_id,
round((stg.amount_b*rates.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
round((stg.amount_b*rates.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
seq_id,
stg.approved_flag,
stg.org_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_login_id,
stg.employee_id,
1,
stg.LINE_TYPE_LOOKUP_CODE,
stg.invoice_num,
stg.discretionary_expense_flag,
stg.trans_currency_code,
trunc(stg.invoice_date) invoice_date,
stg.distribution_line_number,
stg.user_dim1_id,
stg.user_dim2_id,
stg.exp_report_header_id,
stg.po_header_id,
stg.po_release_id,
stg.po_num,
STG.AE_Header_ID,
STG.Ref_AE_Header_ID,
STG.Temp_Line_Num,
STG.GL_SL_Link_ID,
STG.GL_SL_Link_Table,
STG.Inventory_Item_ID,
STG.Purchasing_Category_ID,
STG.Item_Description
FROM FII_AP_INV_STG stg, fii_ap_inv_rates_temp rates
where stg.account_date = rates.trx_date
and stg.inv_currency_code = rates.functional_currency;
END INSERT_INTO_SUMMARY;
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'AP_RESUMMARIZE';
SELECT 1 INTO g_num FROM fii_ap_inv_stg where rownum = 1;
INSERT_INTO_STG (p_parallel_query, p_sort_area_size, p_hash_area_size);
INSERT_RATES;
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'COMPLETED',1,0)),0),
nvl(sum(decode(status,'IN PROCESS',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
count(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_wip_cnt,
l_failed_cnt,
l_tot_cnt
FROM FII_AP_SUM_WORK_JOBS;
UPDATE FII_AP_INV_STG stg
SET stg.PRIM_CONVERSION_RATE = DECODE(stg.inv_currency_code, g_prim_currency, 1,
fii_currency.get_global_rate_primary(stg.inv_currency_code,
least(stg.account_date,sysdate)))
WHERE stg.PRIM_CONVERSION_RATE < 0;
FII_UTIL.put_line('Updated ' || l_rowcount || ' records for primary conversion rate');
UPDATE FII_AP_INV_STG stg
SET stg.SEC_CONVERSION_RATE = decode(stg.inv_currency_code, g_sec_currency, 1,
fii_currency.get_global_rate_secondary(stg.inv_currency_code,
least( stg.account_date,sysdate)))
WHERE stg.SEC_CONVERSION_RATE < 0;
FII_UTIL.put_line('Updated ' || l_rowcount || ' records for secondary conversion rate');
INSERT_INTO_SUMMARY(p_parallel_query);
SELECT MAX(Last_Update_Date) INTO l_last_sup_merge
FROM AP_Duplicate_Vendors_All
WHERE Process_Flag = 'Y';
UPDATE FII_AP_Inv_B BSUM
SET (Supplier_ID, Supplier_Site_ID) =
(SELECT Vendor_ID, NVL(Vendor_Site_ID, Duplicate_Vendor_Site_ID)
FROM AP_Duplicate_Vendors_All DV1
WHERE DV1.Duplicate_Vendor_ID = BSUM.Supplier_ID
AND DV1.Duplicate_Vendor_Site_ID = BSUM.Supplier_Site_ID
AND DV1.Process_Flag = 'Y'
AND TRUNC(DV1.Last_Update_Date) >= TRUNC(l_timestamp))
WHERE (Supplier_ID, Supplier_Site_ID) IN
(SELECT Duplicate_Vendor_ID, Duplicate_Vendor_Site_ID
FROM AP_Duplicate_Vendors_All DV2
WHERE DV2.Process_Flag = 'Y'
AND TRUNC(DV2.Last_Update_Date) >= TRUNC(l_timestamp));
UPDATE ap_dbi_log log
SET exp_processed_flag = 'N'
WHERE exp_processed_flag = 'Y';
UPDATE ap_dbi_log
SET exp_processed_flag = 'N'
WHERE exp_processed_flag = 'S';
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
nvl(sum(decode(status,'UNASSIGNED',1, 0)),0),
nvl(sum(decode(status,'COMPLETED', 1, 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_curr_unasgn_cnt,
l_curr_comp_cnt,
l_curr_tot_cnt
FROM FII_AP_SUM_WORK_JOBS;
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'IN PROCESS',
worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND rownum < 2;
SELECT start_range, end_range, phase_id
INTO l_start_range, l_end_range, l_phase
FROM FII_AP_SUM_WORK_JOBS jobs
WHERE jobs.worker_number = p_worker_no
AND jobs.status = 'IN PROCESS';
UPDATE FII_AP_SUM_WORK_JOBS jobs
SET jobs.status = 'COMPLETED'
WHERE jobs.status = 'IN PROCESS'
AND jobs.worker_number = p_worker_no;
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';