[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,
fa_deprn_periods dp,
fa_book_controls bc,
(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 = 101
AND ps.set_of_books_id = fset.ledger_id
AND ps.end_date >= g_global_Start_Date
AND bc.set_of_books_id = fset.ledger_id
AND dp.book_type_code = bc.book_type_code
AND dp.period_name = ps.period_name
AND nvl(dp.xla_conversion_status, 'UA') <> 'UA';
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 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 = 222
AND ps.set_of_books_id = fset.ledger_id
AND ps.end_date >= g_global_Start_Date
AND ps.migration_status_code <> 'U';
SELECT tablespace_name
INTO g_tablespace
FROM dba_tables
WHERE table_name = g_table_name
AND owner = g_fii_schema;
SELECT * FROM FII_AR_REVENUE_STG b
WHERE b.revenue_pk in
(SELECT b2.revenue_pk FROM FII_AR_REVENUE_STG b2
GROUP BY b2.revenue_pk
HAVING count(*)>1) ';
This needs to be fixed before inserting records in FII_AR_REVENUE_B.
Created temp table fii_ar_uni_con_rec which stores
records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
this table for duplicate records.');
INSERT INTO FII_AR_REVENUE_STG (
REVENUE_PK,
GL_DATE_ID,
GL_DATE,
INVENTORY_ITEM_ID,
OPERATING_UNIT_ID,
COMPANY_ID,
COST_CENTER_ID,
INVOICE_NUMBER,
INVOICE_DATE,
ORDER_LINE_ID,
BILL_TO_PARTY_ID,
FUNCTIONAL_CURRENCY,
TRANSACTION_CURRENCY,
LEDGER_ID,
INVOICE_ID,
AMOUNT_T,
AMOUNT_B,
EXCHANGE_DATE,
TOP_MODEL_ITEM_ID,
ORGANIZATION_ID,
item_organization_id,
om_product_revenue_flag,
TRANSACTION_CLASS,
FIN_CATEGORY_ID,
INVOICE_LINE_ID,
SALES_CHANNEL,
ORDER_NUMBER,
POSTED_FLAG,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
PROD_CATEGORY_ID,
CHART_OF_ACCOUNTS_ID,
FIN_CAT_TYPE_CODE,
REV_BOOKED_DATE,
CHILD_ORDER_LINE_ID)
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code in (g_program_code_R,
g_program_code_DR)
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT /*+ NO_EXPAND */
DISTINCT
'AR-'||ctl.customer_trx_line_id||'-'||
to_char(trunc(aeh.accounting_date),'YYYY/MM/DD')||'-'||
ael.code_combination_id REVENUE_PK,
to_number(to_char(trunc(aeh.accounting_date),'J')) GL_DATE_ID,
--Bug 3455965: use TRUNC for date
TRUNC(aeh.accounting_date) GL_DATE,
-- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
CASE
when (ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_child.ship_from_org_id IS NOT NULL )
THEN ctl_parent.inventory_item_id
when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
THEN ctl_parent.inventory_item_id
ELSE
to_number(NULL)
END INVENTORY_ITEM_ID,
ct.org_id OPERATING_UNIT_ID,
ccdim.company_id COMPANY_ID,
ccdim.cost_center_id COST_CENTER_ID,
substrb(ct.trx_number,1,30) INVOICE_NUMBER,
trunc(ct.trx_date) INVOICE_DATE,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.line_id,
'INTERCOMPANY', sl_parent.line_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) ORDER_LINE_ID,
bill_acct.party_id BILL_TO_PARTY_ID,
sob.currency_code FUNCTIONAL_CURRENCY,
nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
ct.set_of_books_id SET_OF_BOOKS_ID,
ct.customer_trx_id INVOICE_ID,
sum( NVL(lnk.unrounded_entered_cr,0) -
NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_T,
sum( NVL(lnk.unrounded_accounted_cr,0) -
NVL(lnk.unrounded_accounted_dr,0) ) AMOUNT_B,
trunc(aeh.accounting_date) EXCHANGE_DATE,
/* DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.inventory_item_id,
'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.ship_from_org_id,
'INTERCOMPANY', sl_parent.ship_from_org_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_child.ship_from_org_id,
'INTERCOMPANY', sl_child.ship_from_org_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) item_organization_id, */
CASE
when ( ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_parent.ship_from_org_id IS NOT NULL)
THEN sl_parent.inventory_item_id
ELSE
to_number(NULL)
END TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_parent.ship_from_org_id, to_number(null)),
to_number(NULL) ) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
to_number(NULL)) item_organization_id,
decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
'N', 'Y'),
'N') om_product_revenue_flag,
decode(ctt.type,'GUAR','GUR',substrb(ctt.type,1,3)) TRANSACTION_CLASS,
ccdim.natural_account_id FIN_CATEGORY_ID,
ctl.customer_trx_line_id INVOICE_LINE_ID,
nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
substrb( DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY',ctl_parent.interface_line_attribute1,
'INTERCOMPANY',ctl_parent.interface_line_attribute1,
ctl_parent.sales_order),1,30) ORDER_NUMBER,
'Y' POSTED_FLAG,
fii_currency.get_global_rate_primary(sob.currency_code,
trunc(least(trunc(aeh.accounting_date), sysdate))) PRIM_CONVERSION_RATE,
fii_currency.get_global_rate_secondary(sob.currency_code,
trunc(least(trunc(aeh.accounting_date), sysdate))) SEC_CONVERSION_RATE,
ccdim.prod_category_id PROD_CATEGORY_ID,
sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
-- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
AC.fin_cat_type_code FIN_CAT_TYPE_CODE,
decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
null) CHILD_ORDER_LINE_ID
FROM
fii_ar_revenue_id fpk,
ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
ra_cust_trx_line_gl_dist_all ctlgd,
gl_code_combinations glcc,
fii_gl_ccid_dimensions ccdim,
fii_slg_assignments slga,
fii_source_ledger_groups fslg,
-- fii_fin_cat_type_assgns ffcta,
ra_customer_trx_lines_all ctl_parent,
gl_ledgers_public_v sob,
hz_cust_accounts bill_acct,
oe_order_lines_all sl_child,
oe_order_lines_all sl_parent,
oe_order_headers_all sh,
ACCNT_CLASS AC,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links lnk
WHERE fpk.view_type_id= 4
AND fpk.job_num = p_job_num
AND aeh.ae_header_id = fpk.primary_key1
AND aeh.application_id = 222
AND aeh.balance_type_code = 'A'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.application_id = 222
AND aeh.ae_header_id = ael.ae_header_id
AND lnk.application_id = 222
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
AND aeh.ledger_id = ctlgd.set_of_books_id
AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
AND ct.customer_trx_id = ctl.customer_trx_id
AND ct.complete_flag = 'Y'
AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
AND ctt.org_id (+) = ct.org_id
AND NVL(ctt.post_to_gl,'Y') = 'Y'
AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
AND ctlgd.account_set_flag = 'N'
-- AND ctlgd.gl_posted_date IS NOT NULL
AND NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) <> 0
-- AND ctlgd.gl_date IS NOT NULL
AND glcc.code_combination_id = ael.code_combination_id
AND ccdim.code_combination_id = glcc.code_combination_id
AND ( slga.bal_seg_value_id = ccdim.company_id
OR slga.bal_seg_value_id = -1 )
AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
AND slga.ledger_id = ctl.set_of_books_id
-- AND ffcta.fin_category_id = ccdim.natural_account_id
-- AND ffcta.fin_cat_type_code in ('R', 'DR')
AND ctl_parent.customer_trx_line_id =
nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
AND sob.ledger_id = ct.set_of_books_id
AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
AND sl_child.line_id (+) =
case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
then to_number(ctl_parent.interface_line_attribute6)
else to_number(NULL) end
AND sh.header_id (+) = sl_child.header_id
AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND ael.accounting_class_code = AC.Accounting_Class_Code
AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
GROUP BY
ctl.customer_trx_line_id,
trunc(aeh.accounting_date),
ael.code_combination_id,
ctl_parent.inventory_item_id,
ct.org_id, --bug 3361888
ccdim.company_id,
ccdim.cost_center_id,
ct.trx_number,
ctl_parent.line_type,
ctl_parent.interface_line_context,
sl_child.item_type_code,
sl_child.inventory_item_id,
sl_parent.line_id,
bill_acct.party_id,
sob.currency_code,
ct.invoice_currency_code,
ct.set_of_books_id, --bug 3361888
ct.customer_trx_id,
ct.trx_date,
sysdate,
sl_parent.inventory_item_id,
sl_parent.ship_from_org_id,
sl_child.ship_from_org_id,
ctt.type,
ccdim.natural_account_id,
sh.sales_channel_code,
ctl_parent.interface_line_attribute1,
ctl_parent.sales_order,
ccdim.prod_category_id,
sob.chart_of_accounts_id,
-- ffcta.fin_cat_type_code,
AC.Fin_Cat_Type_Code,
ctl_parent.WAREHOUSE_ID,
decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date, sh.booked_date)), to_date(null)),
ctl.interface_line_context,
ctl.interface_line_attribute6;
INSERT INTO FII_AR_REVENUE_STG (
REVENUE_PK,
GL_DATE_ID,
GL_DATE,
INVENTORY_ITEM_ID,
OPERATING_UNIT_ID,
--commented by ilavenil COMPANY_COST_CENTER_ORG_ID,
COMPANY_ID,
COST_CENTER_ID,
--above columns added by ilavenil
INVOICE_NUMBER,
INVOICE_DATE,
ORDER_LINE_ID,
BILL_TO_PARTY_ID,
FUNCTIONAL_CURRENCY,
TRANSACTION_CURRENCY,
LEDGER_ID,
INVOICE_ID,
AMOUNT_T,
AMOUNT_B,
EXCHANGE_DATE,
TOP_MODEL_ITEM_ID,
ORGANIZATION_ID,
item_organization_id,
om_product_revenue_flag,
TRANSACTION_CLASS,
FIN_CATEGORY_ID,
INVOICE_LINE_ID,
SALES_CHANNEL,
ORDER_NUMBER,
POSTED_FLAG,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
PROD_CATEGORY_ID,
CHART_OF_ACCOUNTS_ID,
FIN_CAT_TYPE_CODE,
REV_BOOKED_DATE,
CHILD_ORDER_LINE_ID)
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code = g_program_code_R
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT /*+ NO_EXPAND */
DISTINCT
'ADJ-'||ad.line_id REVENUE_PK,
to_number(to_char(trunc(aeh.accounting_date),'J')) GL_DATE_ID,
--Bug 3455965: use TRUNC for date
TRUNC(aeh.accounting_date) GL_DATE,
-- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
/* CASE
when (ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_child.ship_from_org_id IS NOT NULL )
THEN ctl_parent.inventory_item_id
when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
THEN ctl_parent.inventory_item_id
ELSE
to_number(NULL)
END INVENTORY_ITEM_ID, */
to_number (null) INVENTORY_ITEM_ID,
adj.org_id OPERATING_UNIT_ID,
--commented by ilavenil ccdim.company_cost_center_org_id COMPANY_COST_CENTER_ORG_ID,
ccdim.company_id COMPANY_ID,
ccdim.cost_center_id COST_CENTER_ID,
substrb(ct.trx_number,1,30) INVOICE_NUMBER,
trunc(ct.trx_date) INVOICE_DATE,
/* DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.line_id,
'INTERCOMPANY', sl_parent.line_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) ORDER_LINE_ID, */
to_number(null) ORDER_LINE_ID,
bill_acct.party_id BILL_TO_PARTY_ID,
sob.currency_code FUNCTIONAL_CURRENCY,
nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
aeh.ledger_id SET_OF_BOOKS_ID,
ct.customer_trx_id INVOICE_ID,
decode(gcc.account_type,'A',
sum( NVL(lnk.unrounded_entered_dr,0) -
NVL(lnk.unrounded_entered_cr,0) ),
sum( NVL(lnk.unrounded_entered_cr,0) -
NVL(lnk.unrounded_entered_dr,0) )
) AMOUNT_T,
decode(gcc.account_type,'A',
sum( NVL(lnk.unrounded_accounted_dr,0) -
NVL(lnk.unrounded_accounted_cr,0) ),
sum( NVL(lnk.unrounded_accounted_cr,0) -
NVL(lnk.unrounded_accounted_dr,0) )
) AMOUNT_B,
trunc(aeh.accounting_date) EXCHANGE_DATE,
/* DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.inventory_item_id,
'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.ship_from_org_id,
'INTERCOMPANY', sl_parent.ship_from_org_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_child.ship_from_org_id,
'INTERCOMPANY', sl_child.ship_from_org_id, to_number(NULL)),
to_number(NULL)),
to_number(NULL)) item_organization_id, */
/* CASE
when ( ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_parent.ship_from_org_id IS NOT NULL)
THEN sl_parent.inventory_item_id
ELSE
to_number(NULL)
END TOP_MODEL_ITEM_ID, */
to_number(null) TOP_MODEL_ITEM_ID,
/* DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_parent.ship_from_org_id, to_number(null)),
to_number(NULL) ) ORGANIZATION_ID, */
to_number(null) ORGANIZATION_ID,
/* DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
to_number(NULL)) item_organization_id, */
to_number(null) item_organization_id,
/* decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
'N', 'Y'),
'N') om_product_revenue_flag, */
'N' om_product_revenue_flag,
'ADJ' TRANSACTION_CLASS,
ccdim.natural_account_id FIN_CATEGORY_ID,
0 INVOICE_LINE_ID,
-- nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
'-1' SALES_CHANNEL,
/* substrb( DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY',ctl_parent.interface_line_attribute1,
'INTERCOMPANY',ctl_parent.interface_line_attribute1,
ctl_parent.sales_order),1,30) ORDER_NUMBER, */
null ORDER_NUMBER,
'Y' POSTED_FLAG,
FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY(sob.currency_code,
trunc(least(trunc(aeh.accounting_date), sysdate))) PRIM_CONVERSION_RATE,
FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(sob.currency_code,
trunc(least(trunc(aeh.accounting_date), sysdate))) SEC_CONVERSION_RATE,
ccdim.prod_category_id PROD_CATEGORY_ID,
sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
-- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
AC.fin_cat_type_code FIN_CAT_TYPE_CODE,
-- decode(sh.booked_flag, 'Y', trunc(sh.booked_date), to_date(null)) REV_BOOKED_DATE
to_date(null) REV_BOOKED_DATE,
null CHILD_ORDER_LINE_ID
FROM
fii_ar_revenue_id fpk,
ar_adjustments_all adj,
ar_distributions_all ad,
gl_code_combinations gcc,
fii_gl_ccid_dimensions ccdim,
fii_slg_assignments slga,
fii_source_ledger_groups fslg,
-- fii_fin_cat_type_assgns ffcta,
ra_customer_trx_all ct,
-- ra_customer_trx_lines_all ctl,
-- ra_customer_trx_lines_all ctl_parent,
-- oe_order_lines_all sl_child,
-- oe_order_headers_all sh,
-- oe_order_lines_all sl_parent,
gl_ledgers_public_v sob,
hz_cust_accounts bill_acct,
ACCNT_CLASS AC,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links lnk
WHERE fpk.view_type_id = 3
AND fpk.job_num = p_job_num
-- AND adj.adjustment_id = fpk.Primary_Key1
AND aeh.ae_header_id = fpk.primary_key1
AND aeh.application_id = 222
AND aeh.balance_type_code = 'A'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.application_id = 222
AND aeh.ae_header_id = ael.ae_header_id
AND lnk.application_id = 222
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = ad.line_id
AND aeh.ledger_id = adj.set_of_books_id
AND NVL(adj.status, 'A') = 'A'
AND NVL(adj.postable,'Y') = 'Y'
-- AND adj.gl_posted_date IS NOT NULL
AND ad.source_id = adj.adjustment_id
AND ad.source_table = 'ADJ'
AND gcc.code_combination_id = ael.code_combination_id
AND ccdim.code_combination_id = gcc.code_combination_id
AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
AND ( slga.bal_seg_value_id = ccdim.company_id
OR slga.bal_seg_value_id = -1 )
AND slga.ledger_id = aeh.ledger_id
-- AND ffcta.fin_category_id = ccdim.natural_account_id
-- AND ffcta.fin_cat_type_code = 'R'
AND ct.customer_trx_id = adj.customer_trx_id
AND nvl(ct.org_id, -999) = nvl(adj.org_id, -999)
AND ct.complete_flag = 'Y'
-- AND ctl.customer_trx_line_id (+) = nvl2(adj.customer_trx_line_id,0,0)
-- AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
-- AND ctl_parent.customer_trx_line_id (+) =
-- nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
-- AND sl_child.line_id (+) =
-- case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
-- and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
-- then to_number(ctl_parent.interface_line_attribute6)
-- else to_number(NULL) end
-- AND sh.header_id (+) = sl_child.header_id
-- AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND sob.ledger_id = aeh.ledger_id
AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
AND ael.accounting_class_code = AC.Accounting_Class_Code
AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
group by
ad.line_id,
to_number(to_char(trunc(aeh.accounting_date),'J')),
TRUNC(aeh.accounting_date),
adj.org_id,
ccdim.company_id,
ccdim.cost_center_id,
substrb(ct.trx_number,1,30),
trunc(ct.trx_date),
bill_acct.party_id,
sob.currency_code,
nvl(ct.invoice_currency_code,sob.currency_code),
aeh.ledger_id,
ct.customer_trx_id,
gcc.account_type,
ccdim.natural_account_id,
trunc(least(trunc(aeh.accounting_date), sysdate)),
ccdim.prod_category_id,
sob.chart_of_accounts_id,
-- ffcta.fin_cat_type_code,
AC.Fin_Cat_Type_Code;
fii_util.put_line('Inserting records into staging table');
fii_util.put_line('Inserted '||l_row_count||' rows');
fii_util.put_line('Started inserting rows into staging table for def rev transactions prior to global start date.');
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_STG F (
REVENUE_PK,
GL_DATE_ID,
GL_DATE,
INVENTORY_ITEM_ID,
OPERATING_UNIT_ID,
COMPANY_ID,
COST_CENTER_ID,
INVOICE_NUMBER,
INVOICE_DATE,
ORDER_LINE_ID,
BILL_TO_PARTY_ID,
FUNCTIONAL_CURRENCY,
TRANSACTION_CURRENCY,
LEDGER_ID,
INVOICE_ID,
AMOUNT_T,
AMOUNT_B,
EXCHANGE_DATE,
TOP_MODEL_ITEM_ID,
ORGANIZATION_ID,
item_organization_id,
om_product_revenue_flag,
TRANSACTION_CLASS,
FIN_CATEGORY_ID,
INVOICE_LINE_ID,
SALES_CHANNEL,
ORDER_NUMBER,
POSTED_FLAG,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
PROD_CATEGORY_ID,
CHART_OF_ACCOUNTS_ID,
FIN_CAT_TYPE_CODE,
REV_BOOKED_DATE,
CHILD_ORDER_LINE_ID)
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code = g_program_code_DR
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
select /*+ ordered use_hash(X,sob,glcc) use_nl(ctl_parent,bill_acct,sl_child,sl_parent,sh)
swap_join_inputs(sob) swap_join_inputs(glcc)
parallel(X) parallel(Y) parallel(glcc) parallel(ctl_parent) parallel(sob)
parallel(bill_acct) parallel(sl_child) parallel(sl_parent) parallel(sh) */
DISTINCT 'AR-'||X.x_customer_trx_line_id||'-'||
to_char(X.x_gl_date,'YYYY/MM/DD')||'-'|| X.x_code_combination_id REVENUE_PK,
to_number(to_char(X.x_gl_date,'J')) GL_DATE_ID,
TRUNC(X.x_gl_date) GL_DATE,
CASE
when (ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_child.ship_from_org_id IS NOT NULL )
THEN ctl_parent.inventory_item_id
when (ctl_parent.line_type like 'LINE'
and ctl_parent.WAREHOUSE_ID IS NOT NULL)
THEN ctl_parent.inventory_item_id
ELSE
to_number(NULL)
END INVENTORY_ITEM_ID,
X.x_org_id OPERATING_UNIT_ID,
Y.y_company_id COMPANY_ID,
Y.y_cost_center_id COST_CENTER_ID,
substrb(X.x_trx_number,1,30) INVOICE_NUMBER,
trunc(X.x_trx_date) INVOICE_DATE,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', sl_parent.line_id,
'INTERCOMPANY', sl_parent.line_id,
to_number(NULL)),
to_number(NULL)),
to_number(NULL)) ORDER_LINE_ID,
bill_acct.party_id BILL_TO_PARTY_ID,
sob.currency_code FUNCTIONAL_CURRENCY,
nvl(X.x_invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
X.x_ct_set_of_books_id SET_OF_BOOKS_ID,
X.x_customer_trx_id INVOICE_ID,
(X.x_amount) AMOUNT_T,
(X.x_acctd_amount) AMOUNT_B,
trunc(X.x_gl_date) EXCHANGE_DATE,
CASE
when (ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_parent.ship_from_org_id IS NOT NULL)
THEN sl_parent.inventory_item_id
ELSE
to_number(NULL)
END TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_parent.ship_from_org_id, to_number(null)),
to_number(NULL)) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID),
to_number(NULL)) item_organization_id,
decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
'N', 'Y'),
'N') om_product_revenue_flag,
decode(X.x_type,'GUAR','GUR',substrb(X.x_type,1,3)) TRANSACTION_CLASS,
Y.y_natural_account_id FIN_CATEGORY_ID,
X.x_customer_trx_line_id INVOICE_LINE_ID,
nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
substrb( DECODE(ctl_parent.interface_line_context,
'ORDER ENTRY', ctl_parent.interface_line_attribute1,
'INTERCOMPANY', ctl_parent.interface_line_attribute1,
ctl_parent.sales_order),1,30) ORDER_NUMBER,
'Y' POSTED_FLAG,
-1 PRIM_CONVERSION_RATE,
-1 SEC_CONVERSION_RATE,
Y.y_prod_category_id PROD_CATEGORY_ID,
sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
-- Y.y_fin_cat_type_code FIN_CAT_TYPE_CODE,
X.x_fin_cat_type_code FIN_CAT_TYPE_CODE,
decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
X.x_child_order_line_id CHILD_ORDER_LINE_ID
from (
select /*+ no_merge cardinality(1000000) parallel(ccdim) parallel(slga) parallel(ffcta) parallel(fslg) */
ccdim.code_combination_id y_code_combination_id,
ccdim.company_id y_company_id,
ccdim.cost_center_id y_cost_center_id,
ccdim.natural_account_id y_natural_account_id,
ccdim.prod_category_id y_prod_category_id,
slga.ledger_id y_ledger_id
-- ffcta.fin_cat_type_code y_fin_cat_type_code
from fii_source_ledger_groups fslg,
fii_slg_assignments slga,
fii_gl_ccid_dimensions ccdim
-- fii_fin_cat_type_assgns ffcta
where (slga.bal_seg_value_id = ccdim.company_id
OR slga.bal_seg_value_id = -1
)
AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
-- AND ffcta.fin_category_id = ccdim.natural_account_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
-- AND ffcta.fin_cat_type_code = 'DR'
) Y,
(
select /*+ no_merge cardinality(10000000) ordered full(fpk) use_hash(ctl,ct,ctt,ctlgd)
parallel(fpk) parallel(ctl) parallel(ct) parallel(ctt) parallel(ctlgd) */
ael.code_combination_id x_code_combination_id,
trunc(aeh.accounting_date) x_gl_date,
sum( NVL(lnk.unrounded_entered_cr,0) -
NVL(lnk.unrounded_entered_dr,0) ) x_amount,
sum( NVL(lnk.unrounded_accounted_cr,0) -
NVL(lnk.unrounded_accounted_dr,0) ) x_acctd_amount,
ctl.set_of_books_id x_set_of_books_id,
ctl.previous_customer_trx_line_id x_prev_customer_trx_line_id,
ctl.customer_trx_line_id x_customer_trx_line_id,
ct.org_id x_org_id,
ct.trx_number x_trx_number,
ct.invoice_currency_code x_invoice_currency_code,
ct.customer_trx_id x_customer_trx_id,
ct.trx_date x_trx_date,
ct.set_of_books_id x_ct_set_of_books_id,
ct.bill_to_customer_id x_bill_to_customer_id,
ctt.type x_type,
decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
null) x_child_order_line_id,
AC.Fin_Cat_Type_Code x_fin_cat_type_code
from fii_ar_revenue_id fpk,
ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
ra_cust_trx_line_gl_dist_all ctlgd,
ACCNT_CLASS AC,
xla_distribution_links lnk,
xla_ae_lines ael,
xla_ae_headers aeh
WHERE fpk.view_type_id= 4
AND fpk.job_num = p_job_num
AND ctl.customer_trx_line_id = fpk.primary_key1
AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
AND ct.customer_trx_id = ctl.customer_trx_id
AND ct.complete_flag = 'Y'
AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
AND ctt.org_id (+) = ct.org_id
AND NVL(ctt.post_to_gl,'Y') = 'Y'
AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
AND ctlgd.account_set_flag = 'N'
AND NVL(lnk.unrounded_entered_cr,0) -
NVL(lnk.unrounded_entered_dr,0) <> 0
AND aeh.accounting_date < g_gl_from_date
AND aeh.application_id = 222
AND aeh.balance_type_code = 'A'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.application_id = 222
AND aeh.ae_header_id = ael.ae_header_id
AND lnk.application_id = 222
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
AND aeh.ledger_id = ctlgd.set_of_books_id
AND ael.accounting_class_code = AC.Accounting_Class_Code
AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
GROUP BY
ael.code_combination_id,
trunc(aeh.accounting_date),
ctl.set_of_books_id,
ctl.previous_customer_trx_line_id,
ctl.customer_trx_line_id,
ct.org_id,
ct.trx_number,
ct.invoice_currency_code,
ct.customer_trx_id,
ct.trx_date,
ct.set_of_books_id,
ct.bill_to_customer_id,
ctt.type,
ctl.interface_line_context,
ctl.interface_line_attribute6,
AC.Fin_Cat_Type_Code
) X,
gl_ledgers_public_v sob,
gl_code_combinations glcc,
ra_customer_trx_lines_all ctl_parent,
hz_cust_accounts bill_acct,
oe_order_lines_all sl_child,
oe_order_lines_all sl_parent,
oe_order_headers_all sh
WHERE Y.y_code_combination_id = X.x_code_combination_id
AND Y.y_code_combination_id = glcc.code_combination_id
AND Y.y_ledger_id = X.x_set_of_books_id
AND ctl_parent.customer_trx_line_id = nvl(X.x_prev_customer_trx_line_id,X.x_customer_trx_line_id)
AND sob.ledger_id = X.x_ct_set_of_books_id
AND bill_acct.cust_account_id(+) = X.x_bill_to_customer_id
AND sl_child.line_id (+) =
case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
then to_number(ctl_parent.interface_line_attribute6)
else to_number(NULL)
end
AND sh.header_id (+) = sl_child.header_id
AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id);
fii_util.put_line('Inserted '||l_row_count||' rows into staging table.');
SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
USING (SELECT /*+ cardinality(stg,1) */ * FROM FII_AR_REVENUE_STG stg
WHERE prim_conversion_rate > 0
OR sec_conversion_rate > 0) stg
ON ( stg.revenue_pk = f.revenue_pk)
WHEN MATCHED THEN
UPDATE SET
f.AMOUNT_T = stg.AMOUNT_T,
f.AMOUNT_B = stg.AMOUNT_B,
f.PRIM_AMOUNT_G = ROUND(stg.AMOUNT_B * NVL(stg.prim_conversion_rate, 1) /
to_char(g_mau_prim)) * to_char(g_mau_prim),
f.SEC_AMOUNT_G = ROUND(stg.AMOUNT_B * NVL(stg.sec_conversion_rate, 1) /
to_char(g_mau_sec)) * to_char(g_mau_sec),
f.UPDATE_SEQUENCE = seq_id,
f.LAST_UPDATED_BY = g_fii_user_id,
f.LAST_UPDATE_LOGIN = g_fii_login_id,
f.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT (
f.REVENUE_PK,
f.GL_DATE_ID,
f.GL_DATE,
f.INVENTORY_ITEM_ID,
f.OPERATING_UNIT_ID,
f.COMPANY_ID,
f.COST_CENTER_ID,
f.INVOICE_NUMBER,
f.ORDER_LINE_ID,
f.BILL_TO_PARTY_ID,
f.FUNCTIONAL_CURRENCY,
f.TRANSACTION_CURRENCY,
f.LEDGER_ID,
f.INVOICE_ID,
f.AMOUNT_T,
f.AMOUNT_B,
f.PRIM_AMOUNT_G,
f.SEC_AMOUNT_G,
f.TOP_MODEL_ITEM_ID,
f.ORGANIZATION_ID,
f.item_organization_id,
f.om_product_revenue_flag,
f.TRANSACTION_CLASS,
f.FIN_CATEGORY_ID,
f.ORDER_NUMBER,
f.SALES_CHANNEL,
f.INVOICE_LINE_ID,
f.LAST_UPDATE_DATE,
f.CREATION_DATE,
f.POSTED_FLAG,
f.PROD_CATEGORY_ID,
f.CHART_OF_ACCOUNTS_ID,
f.UPDATE_SEQUENCE,
f.LAST_UPDATED_BY,
f.CREATED_BY,
f.LAST_UPDATE_LOGIN,
f.INVOICE_DATE,
f.FIN_CAT_TYPE_CODE,
f.REV_BOOKED_DATE,
f.CHILD_ORDER_LINE_ID)
VALUES (
stg.REVENUE_PK,
stg.GL_DATE_ID,
stg.GL_DATE,
stg.INVENTORY_ITEM_ID,
stg.OPERATING_UNIT_ID,
stg.COMPANY_ID,
stg.COST_CENTER_ID,
stg.INVOICE_NUMBER,
stg.ORDER_LINE_ID,
stg.BILL_TO_PARTY_ID,
stg.FUNCTIONAL_CURRENCY,
stg.TRANSACTION_CURRENCY,
stg.LEDGER_ID,
stg.INVOICE_ID,
stg.AMOUNT_T,
stg.AMOUNT_B,
ROUND(stg.AMOUNT_B * NVL(stg.prim_conversion_rate, 1) /
to_char(g_mau_prim)) * to_char(g_mau_prim),
ROUND(stg.AMOUNT_B * NVL(stg.sec_conversion_rate, 1) /
to_char(g_mau_sec)) * to_char(g_mau_sec),
stg.TOP_MODEL_ITEM_ID,
stg.ORGANIZATION_ID,
stg.item_organization_id,
stg.om_product_revenue_flag,
stg.TRANSACTION_CLASS,
stg.FIN_CATEGORY_ID,
stg.ORDER_NUMBER,
stg.SALES_CHANNEL,
stg.INVOICE_LINE_ID,
SYSDATE,
SYSDATE,
stg.POSTED_FLAG,
stg.PROD_CATEGORY_ID,
stg.CHART_OF_ACCOUNTS_ID,
seq_id,
g_fii_user_id,
g_fii_user_id,
g_fii_login_id,
stg.invoice_date,
stg.FIN_CAT_TYPE_CODE,
stg.REV_BOOKED_DATE,
stg.CHILD_ORDER_LINE_ID);
/* DELETE FROM FII_AR_REVENUE_B f
WHERE f.UPDATE_SEQUENCE <> seq_id
AND f.TRANSACTION_CLASS <> 'ADJ'
AND f.INVOICE_LINE_ID IN (SELECT fpk.primary_key1 FROM FII_AR_REVENUE_ID fpk
WHERE fpk.view_type_id = 4);
CURSOR c1 IS SELECT DISTINCT TRANSACTION_CURRENCY,
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
trunc(least(EXCHANGE_DATE,sysdate))) EXCHANGE_DATE
FROM FII_AR_REVENUE_STG
WHERE prim_conversion_rate < 0;
CURSOR c2 IS SELECT DISTINCT TRANSACTION_CURRENCY,
decode( sec_conversion_RATE,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
trunc(least(EXCHANGE_DATE,sysdate))) EXCHANGE_DATE
FROM FII_AR_REVENUE_STG
WHERE sec_conversion_RATE < 0;
CURSOR c3 IS SELECT DISTINCT FUNCTIONAL_CURRENCY,
decode( prim_conversion_rate,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
trunc(least(TRX_DATE,sysdate))) TRX_DATE
FROM FII_AR_REVENUE_RATES_TEMP
WHERE prim_conversion_rate < 0;
CURSOR c4 IS SELECT DISTINCT FUNCTIONAL_CURRENCY,
decode( sec_conversion_RATE,
-3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
trunc(least(TRX_DATE,sysdate))) TRX_DATE
FROM FII_AR_REVENUE_RATES_TEMP
WHERE sec_conversion_RATE < 0;
SELECT 1
INTO l_miss_rates
FROM FII_AR_REVENUE_RATES_TEMP
WHERE ((prim_conversion_rate < 0) OR (sec_conversion_rate < 0))
AND ROWNUM = 1;
SELECT 1
INTO l_miss_rates
FROM FII_AR_REVENUE_STG
WHERE ((prim_conversion_rate < 0) OR (sec_conversion_rate < 0))
AND ROWNUM = 1;
select user_conversion_type into l_prim_rate_type_name
from gl_daily_conversion_types
where conversion_type = l_prim_rate_type;
select user_conversion_type into l_sec_rate_type_name
from gl_daily_conversion_types
where conversion_type = l_sec_rate_type;
SELECT 1
INTO l_miss_rates_prim
FROM FII_AR_REVENUE_RATES_TEMP
WHERE prim_conversion_rate < 0
AND ROWNUM = 1;
SELECT 1
INTO l_miss_rates_sec
FROM FII_AR_REVENUE_RATES_TEMP
WHERE sec_conversion_rate < 0
AND ROWNUM = 1;
SELECT 1
INTO l_miss_rates_prim
FROM FII_AR_REVENUE_STG
WHERE prim_conversion_rate < 0
AND ROWNUM = 1;
SELECT 1
INTO l_miss_rates_sec
FROM FII_AR_REVENUE_STG
WHERE sec_conversion_rate < 0
AND ROWNUM = 1;
select to_number(item_value) into l_max_group_id
from FII_CHANGE_LOG
where log_item = 'AR_MAX_GROUP_ID';
INSERT INTO FII_AR_REVENUE_ID (
view_type_id,
primary_key1)
SELECT --+ ORDERED USE_NL(ADJ)
7,
adj.adjustment_id
from ra_customer_trx_all t,
ar_adjustments_all adj,
( select distinct 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 = :a
) lidset
WHERE t.complete_flag = ''Y''
AND t.last_update_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') and to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
and t.customer_trx_id = adj.customer_trx_id
and nvl(adj.status, ''A'') = ''A''
and nvl(adj.postable,''Y'') = ''Y''
and adj.amount <> 0
and t.set_of_books_id = lidset.ledger_id';
INSERT INTO FII_AR_REVENUE_ID (
view_type_id,
primary_key1)
SELECT
7,
t.adjustment_id
FROM ar_adjustments_all t,
( select distinct 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 = :a
) lidset
WHERE t.gl_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
AND t.last_update_date BETWEEN to_date(:d,''YYYY/MM/DD HH24:MI:SS'') and to_date(:e,''YYYY/MM/DD HH24:MI:SS'')
AND NVL(t.status, ''A'') = ''A''
AND NVL(t.postable,''Y'') = ''Y''
AND t.amount <> 0
AND t.set_of_books_id = lidset.ledger_id';
INSERT INTO FII_AR_REVENUE_ID (
view_type_id,
primary_key1)
SELECT
8,
ctl.customer_trx_line_id
FROM ra_customer_trx_all ct,
ra_customer_trx_lines_all ctl,
( select distinct 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 = :a
) lidset
WHERE ct.last_update_date between to_date(:b,''YYYY/MM/DD HH24:MI:SS'') and to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
AND ct.customer_trx_id = ctl.customer_trx_id
AND ct.complete_flag = ''Y''
AND ct.set_of_books_id = lidset.ledger_id
UNION
SELECT
8,
ct.customer_trx_line_id
FROM ra_customer_trx_lines_all ct,
( select distinct 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 = :d
) lidset
WHERE nvl(ct.interface_line_context, ''xxx'') NOT IN (''PA INVOICES'')
AND ct.last_update_date between to_date(:e,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:f,''YYYY/MM/DD HH24:MI:SS'')
AND ct.set_of_books_id = lidset.ledger_id';
l_stmt := 'INSERT INTO FII_AR_REVENUE_ID (
view_type_id,
primary_key1)
SELECT /*+ INDEX(aeh, xla_ae_headers_N5) */
dup.view_type_id,
aeh.ae_header_id
FROM xla_ae_headers aeh,
( select /*+ no_merge */ distinct 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 = :m
) lidset,
( select 8 view_type_id from dual
union all
select 7 view_type_id from dual
) dup
WHERE aeh.accounting_date BETWEEN to_date(:n,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:o,''YYYY/MM/DD HH24:MI:SS'')
AND aeh.application_id = 222
AND aeh.balance_type_code = ''A''
AND aeh.gl_transfer_status_code = ''Y''
AND aeh.ledger_id = lidset.ledger_id';
l_stmt := 'INSERT INTO FII_AR_REVENUE_ID (
view_type_id,
primary_key1)
SELECT /*+ INDEX(aeh, xla_ae_headers_N5) */
dup.view_type_id,
aeh.ae_header_id
FROM xla_ae_headers aeh,
( select distinct 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 = :a
) lidset,
( select 8 view_type_id from dual
union all
select 7 view_type_id from dual
) dup
WHERE aeh.accounting_date BETWEEN to_date(:b,''YYYY/MM/DD HH24:MI:SS'') AND to_date(:c,''YYYY/MM/DD HH24:MI:SS'')
AND aeh.group_id > :m
AND aeh.application_id = 222
AND aeh.balance_type_code = ''A''
AND aeh.gl_transfer_status_code = ''Y''
AND aeh.ledger_id = lidset.ledger_id';
select 1 into l_count from dba_tables
where table_name = 'AR_DEFERRED_LINES_ALL'
and owner = k_ar_schema
and rownum = 1;
select /*+ parallel(trail) */
CUSTOMER_TRX_LINE_ID lid
from AR_DEFERRED_LINES_ALL trail';
select 1 into l_count from dba_tables
where table_name = 'AR_RAMC_AUDIT_TRAIL'
and owner = k_ar_schema
and rownum = 1;
select /*+ parallel(trail) full(trail) */
CUSTOMER_TRX_LINE_ID lid
from AR_RAMC_AUDIT_TRAIL trail';
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_ID F
(
view_type_id,
job_num,
primary_key1
)
select /*+ no_merge parallel(z) */ distinct 4,
1,
lid
from (
select /*+ no_merge PARALLEL(A) */
a.invoice_line_id lid
from FII_AR_REVENUE_STG A
union all
select /*+ ordered parallel(b) parallel(y)
pq_distibute(y, none, broadcast) */
decode(y.a,
1,
b.from_cust_trx_line_id,
b.to_cust_trx_line_id) lid
from ( select /*+ no_merge */ 1 a, org_id
from ar_system_parameters_all
union all
select 2 a, org_id
from ar_system_parameters_all ) y,
ar_revenue_adjustments_all b
where y.org_id = b.org_id
union all
select /*+ PARALLEL(line) PARALLEL(rule) */
line.CUSTOMER_TRX_LINE_ID lid
from ra_customer_trx_lines_all line,
RA_RULES rule
where line.ACCOUNTING_RULE_ID = rule.RULE_ID
and rule.DEFERRED_REVENUE_FLAG = ''Y''' ||l_stmt2|| '
) z
where lid is not null ';
SELECT NVL(item_value, 'N')
INTO l_result
FROM fii_change_log
WHERE log_item = 'AR_RESUMMARIZE';
SELECT 1
INTO l_count1
FROM fii_ar_revenue_b
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_ar_revenue_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 = 'AR_RESUMMARIZE'
AND item_value = 'Y';
SELECT NVL(item_value, 'N')
INTO l_result
FROM fii_change_log
WHERE log_item = 'AR_PROD_CHANGE';
SELECT 1
INTO l_count1
FROM fii_ar_revenue_b
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_ar_revenue_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 = 'AR_PROD_CHANGE'
AND item_value = 'Y';
INSERT INTO FII_AR_REVENUE_JOBS (
function,
phase,
priority,
date_parameter1,
date_parameter2,
date_parameter3,
date_parameter4,
char_parameter1,
status)
SELECT
'IDENTIFY_CHANGE',
1,
t.priority,
g_gl_from_date,
g_gl_to_date,
g_lud_from_date,
g_lud_to_date,
t.data_type,
'UNASSIGNED'
FROM (SELECT 'AR INV' data_type, 1 priority FROM DUAL UNION ALL
SELECT 'AR ADJ' data_type, 3 priority FROM DUAL) t;
INSERT INTO FII_AR_REVENUE_JOBS (
function,
phase,
priority,
date_parameter1,
date_parameter2,
date_parameter3,
date_parameter4,
char_parameter1,
status)
VALUES (
'IDENTIFY_CHANGE',
1,
3,
l_from_temp,
l_to_temp,
g_lud_from_date,
g_lud_to_date,
'AR DL',
'UNASSIGNED');
INSERT INTO FII_AR_REVENUE_JOBS (
function,
phase,
priority,
date_parameter1,
date_parameter2,
date_parameter3,
date_parameter4,
char_parameter1,
status)
VALUES (
'IDENTIFY_CHANGE',
1,
3,
sysdate + ONE_SECOND,
g_gl_to_date,
g_lud_from_date,
g_lud_to_date,
'AR DL',
'UNASSIGNED');
INSERT INTO FII_AR_REVENUE_JOBS (
function,
phase,
priority,
date_parameter1,
date_parameter2,
status)
SELECT
function,
phase,
priority,
g_gl_from_date,
g_gl_to_date,
'UNASSIGNED'
FROM (select 'VERIFY_CCID_UP_TO_DATE' function, 2 phase, 1 priority from dual union all
select 'REGISTER_EXTRACT_JOBS' function, 3 phase, 1 priority from dual) t;
insert into fii_ar_revenue_id (
view_type_id,
job_num,
primary_key1)
select
l_view_type_id,
l_curr_job_num + ceil(rownum / l_batch_size(l_view_type_id)) - 1,
primary_key1
from (select distinct
primary_key1
from fii_ar_revenue_id
where view_type_id = l_view_type_id + 4) t;
insert into FII_AR_REVENUE_JOBS (
function,
phase,
priority,
number_parameter1,
number_parameter2,
status)
values (
'POPULATE_STG',
4,
l_priority,
l_view_type_id,
l_curr_job_num,
'UNASSIGNED' );
FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
program');
FUNCTION DETECT_DELETED_INV RETURN NUMBER IS
l_gl_from VARCHAR2(80);
select distinct
invoice_id
from FII_AR_REVENUE_B
where transaction_class <> ''ADJ'' ';
select
wh.invoice_id invoice_id
from '||g_fii_schema||'.fii_ar_revenue_sum_del1 wh,
ra_customer_trx_all trx
where wh.invoice_id = trx.customer_trx_id (+)
AND trx.customer_trx_id IS NULL ';
delete from FII_AR_REVENUE_B
where transaction_class <> ''ADJ''
and invoice_id in (select invoice_id
FROM '||g_fii_schema||'.fii_ar_revenue_sum_del2) ';
fii_util.put_line('Identified '||l_count||' invoices deleted in transaction system');
Error in Procedure: DETECT_DELETED_INV
Section: '||g_section||'
Message: '||sqlerrm;
END DETECT_DELETED_INV;
SELECT fin_category_id cur_rev_acct_id,
fin_cat_type_code cur_fin_cat_type_code
FROM fii_fin_cat_type_assgns ffcta
WHERE ffcta.fin_cat_type_code in (''R'', ''DR'') ';
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
'''||g_program_code_R||''', ''R'',
'''||g_program_code_DR||''', ''DR'',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code in ('''||g_program_code_R||''',
'''||g_program_code_DR||''')
AND XAD.Enabled_Flag = ''Y''
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT * FROM ACCNT_CLASS ';
SELECT COUNT(*)
FROM fii_ar_rev_accts fra,
'||g_fii_schema||'.fii_ar_rev_accts_temp temp
WHERE fra.rev_acct_id = temp.cur_rev_acct_id(+)
AND fra.fin_cat_type_code = temp.cur_fin_cat_type_code(+)
AND temp.cur_rev_acct_id IS NULL ';
SELECT COUNT(*)
FROM fii_ar_rev_accts fra
WHERE NOT EXISTS (
SELECT 1
FROM '||g_fii_schema||'.fii_ar_rev_accts_temp temp
WHERE ( fra.rev_acct_id = temp.Ledger_ID
OR temp.Ledger_ID IS NULL )
AND fra.rev_acct = temp.Accounting_Class_Code
AND fra.fin_cat_type_code = temp.Fin_Cat_Type_Code
) ';
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_STG F (
REVENUE_PK,
GL_DATE_ID,
GL_DATE,
INVENTORY_ITEM_ID,
OPERATING_UNIT_ID,
COMPANY_ID,
COST_CENTER_ID,
INVOICE_NUMBER,
INVOICE_DATE,
ORDER_LINE_ID,
BILL_TO_PARTY_ID,
FUNCTIONAL_CURRENCY,
TRANSACTION_CURRENCY,
LEDGER_ID,
INVOICE_ID,
AMOUNT_T,
AMOUNT_B,
EXCHANGE_DATE,
TOP_MODEL_ITEM_ID,
ORGANIZATION_ID,
item_organization_id,
om_product_revenue_flag,
TRANSACTION_CLASS,
FIN_CATEGORY_ID,
INVOICE_LINE_ID,
SALES_CHANNEL,
ORDER_NUMBER,
POSTED_FLAG,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
PROD_CATEGORY_ID,
CHART_OF_ACCOUNTS_ID,
FIN_CAT_TYPE_CODE,
REV_BOOKED_DATE,
CHILD_ORDER_LINE_ID)
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code in (g_program_code_R,
g_program_code_DR)
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT /*+ ORDERED use_hash(v1,gcc,ccdim,slga,fslg,ctl_parent,bill_acct,ct,ctl)
use_hash(ctl) use_nl(sob,ctt) pq_distribute(ct,hash,hash) */ DISTINCT
decode(v1.transaction_class,'ADJ','ADJ-'||v1.REVENUE_PK,'AR-'||v1.REVENUE_PK||'-'||to_char(v1.gl_date,'YYYY/MM/DD')
||'-'||v1.code_combination_id) REVENUE_PK,
to_number(to_char(v1.gl_date,'J')) GL_DATE_ID,
--Bug 3455965: use TRUNC for date
TRUNC(v1.gl_date) GL_DATE,
-- ctl_parent.inventory_item_id INVENTORY_ITEM_ID,
CASE
when (ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_child.ship_from_org_id IS NOT NULL )
THEN ctl_parent.inventory_item_id
when (ctl_parent.line_type like 'LINE' and ctl_parent.WAREHOUSE_ID IS NOT NULL)
THEN ctl_parent.inventory_item_id
ELSE
to_number(NULL)
END INVENTORY_ITEM_ID,
--bug 3361888
DECODE(v1.transaction_class, 'ADJ', v1.org_id, ct.org_id) OPERATING_UNIT_ID,
ccdim.company_id COMPANY_ID,
ccdim.cost_center_id COST_CENTER_ID,
substrb(ct.trx_number,1,30) INVOICE_NUMBER,
trunc(ct.trx_date) INVOICE_DATE,
DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.line_id,
'INTERCOMPANY', sl_parent.line_id, to_number(NULL)), to_number(NULL)),
to_number(NULL)) ORDER_LINE_ID,
bill_acct.party_id BILL_TO_PARTY_ID,
sob.currency_code FUNCTIONAL_CURRENCY,
nvl(ct.invoice_currency_code,sob.currency_code) TRANSACTION_CURRENCY,
--bug 3361888
DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
SET_OF_BOOKS_ID,
ct.customer_trx_id INVOICE_ID,
nvl2(v1.transaction_class,decode(gcc.account_type,'A', nvl(v1.amount_dr,0) - nvl(v1.amount_cr,0),
nvl(v1.amount_cr,0) - nvl(v1.amount_dr,0)), AMOUNT_DR) AMOUNT_T,
nvl2(v1.transaction_class,decode(gcc.account_type,'A', nvl(v1.acctd_amount_dr,0) - nvl(v1.acctd_amount_cr,0),
nvl(v1.acctd_amount_cr,0) - nvl(v1.acctd_amount_dr,0)),
ACCTD_AMOUNT_DR) AMOUNT_B,
trunc(nvl2(v1.transaction_class,v1.gl_date,ct.trx_date)) EXCHANGE_DATE,
/* DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.inventory_item_id,
'INTERCOMPANY', sl_parent.inventory_item_id, to_number(NULL)), to_number(NULL)),
to_number(NULL)) TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_parent.ship_from_org_id, 'INTERCOMPANY',
sl_parent.ship_from_org_id, to_number(NULL)), to_number(NULL)),
to_number(NULL)) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE', DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY', sl_child.ship_from_org_id, 'INTERCOMPANY',
sl_child.ship_from_org_id, to_number(NULL)), to_number(NULL)),
to_number(NULL)) item_organization_id, */
CASE
when ( ctl_parent.line_type like 'LINE'
and ctl_parent.inventory_item_id = sl_child.inventory_item_id
and sl_parent.ship_from_org_id IS NOT NULL)
THEN sl_parent.inventory_item_id
ELSE
to_number(NULL)
END TOP_MODEL_ITEM_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_parent.ship_from_org_id, to_number(null)),
to_number(NULL) ) ORGANIZATION_ID,
DECODE(ctl_parent.line_type, 'LINE',
DECODE(ctl_parent.inventory_item_id, sl_child.inventory_item_id,
sl_child.ship_from_org_id, ctl_parent.WAREHOUSE_ID ),
to_number(NULL)) item_organization_id,
decode(ctl_parent.interface_line_context, 'ORDER ENTRY',
decode(nvl( sl_child.item_type_code, 'X' ), 'SERVICE',
'N', 'Y'),
'N') om_product_revenue_flag,
nvl(v1.transaction_class,decode(ctt.type,'GUAR','GUR',substrb(ctt.type,1,3))) TRANSACTION_CLASS,
ccdim.natural_account_id FIN_CATEGORY_ID,
nvl(v1.customer_trx_line_id,ctl.customer_trx_line_id) INVOICE_LINE_ID,
nvl(substrb(sh.sales_channel_code,1,30), '-1') SALES_CHANNEL,
substrb( DECODE(ctl_parent.interface_line_context, 'ORDER ENTRY',ctl_parent.interface_line_attribute1,
'INTERCOMPANY', ctl_parent.interface_line_attribute1,
ctl_parent.sales_order),1,30) ORDER_NUMBER,
v1.POSTED_FLAG,
-1 PRIM_CONVERSION_RATE,
-1 SEC_CONVERSION_RATE,
ccdim.prod_category_id PROD_CATEGORY_ID,
sob.chart_of_accounts_id CHART_OF_ACCOUNTS_ID,
-- ffcta.fin_cat_type_code FIN_CAT_TYPE_CODE,
v1.fin_cat_type_code FIN_CAT_TYPE_CODE,
decode(sh.booked_flag, 'Y', trunc(nvl(sl_child.order_firmed_date,
sh.booked_date)), to_date(null)) REV_BOOKED_DATE,
decode(ctl.interface_line_context, 'ORDER ENTRY',ctl.interface_line_attribute6,
null) CHILD_ORDER_LINE_ID
FROM (select /*+ PARALLEL(a) */ * from fii_source_ledger_groups a) fslg,
(select /*+ PARALLEL(a) */ * from fii_slg_assignments a) slga,
(select /*+ PARALLEL(a) */ * from fii_gl_ccid_dimensions a) ccdim,
-- (select /*+ PARALLEL(a) */ * from fii_fin_cat_type_assgns a) ffcta,
(select /*+ PARALLEL(a) */ * from gl_code_combinations a) gcc,
(
SELECT /*+ PARALLEL(adj) PARALLEL(ad) parallel(lidset)
PARALLEL(AC) PARALLEL(lnk) PARALLEL(ael) PARALLEL(aeh) */
ad.line_id REVENUE_PK,
trunc(aeh.accounting_date) GL_DATE,
adj.org_id,
aeh.ledger_id SET_OF_BOOKS_ID,
sum( NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_DR,
sum( NVL(lnk.unrounded_entered_cr,0) ) AMOUNT_CR,
sum( NVL(lnk.unrounded_accounted_dr,0) ) ACCTD_AMOUNT_DR,
sum( NVL(lnk.unrounded_accounted_cr,0) ) ACCTD_AMOUNT_CR,
'ADJ' TRANSACTION_CLASS,
0 customer_trx_line_id,
adj.customer_trx_id,
ael.code_combination_id,
'Y' POSTED_FLAG,
AC.Fin_Cat_Type_Code
FROM ar_adjustments_all adj,
ar_distributions_all ad,
(
select /*+ no_merge use_hash(slga,fslg) */ distinct 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
) lidset,
ACCNT_CLASS AC,
xla_distribution_links lnk,
xla_ae_lines ael,
xla_ae_headers aeh
WHERE aeh.accounting_date BETWEEN g_gl_from_date AND g_gl_to_date
AND adj.gl_date BETWEEN g_gl_from_date AND g_gl_to_date
AND NVL(adj.status, 'A') = 'A'
AND NVL(adj.postable,'Y') = 'Y'
-- AND adj.amount <> 0
AND ad.source_id = adj.adjustment_id
AND ad.source_table = 'ADJ'
AND aeh.ledger_id = lidset.ledger_id
AND aeh.application_id = 222
AND aeh.balance_type_code = 'A'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.application_id = 222
AND aeh.ae_header_id = ael.ae_header_id
AND lnk.application_id = 222
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = ad.line_id
AND aeh.ledger_id = adj.set_of_books_id
AND ael.accounting_class_code = AC.Accounting_Class_Code
AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
group by
ad.line_id,
trunc(aeh.accounting_date),
adj.org_id,
aeh.ledger_id,
adj.customer_trx_id,
ael.code_combination_id,
AC.Fin_Cat_Type_Code
UNION ALL
SELECT /*+ PARALLEL(ctlgd) parallel(lidset)
PARALLEL(AC) PARALLEL(lnk) PARALLEL(ael) PARALLEL(aeh) */
ctlgd.customer_trx_line_id REVENUE_PK,
trunc(aeh.accounting_date),
to_number(null), -- ctlgd.org_id,
to_number(null), -- ctlgd.set_of_books_id,
sum( NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) ) AMOUNT_T,
0,
sum( NVL(lnk.unrounded_accounted_cr,0) - NVL(lnk.unrounded_accounted_dr,0) ) AMOUNT_B,
0,
NULL TRANSACTION_CLASS,
ctlgd.customer_trx_line_id,
NULL,
ael.code_combination_id,
'Y' POSTED_FLAG,
AC.Fin_Cat_Type_Code
FROM ra_cust_trx_line_gl_dist_all ctlgd,
(
select /*+ no_merge use_hash(slga,fslg) */ distinct 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
) lidset,
ACCNT_CLASS AC,
xla_distribution_links lnk,
xla_ae_lines ael,
xla_ae_headers aeh
WHERE aeh.accounting_date BETWEEN g_gl_from_date AND g_gl_to_date
AND ctlgd.gl_date BETWEEN g_gl_from_date AND g_gl_to_date
AND ctlgd.account_set_flag = 'N'
AND NVL(lnk.unrounded_entered_cr,0) - NVL(lnk.unrounded_entered_dr,0) <> 0
AND aeh.ledger_id = lidset.ledger_id
AND ctlgd.customer_trx_line_id IS NOT NULL
AND aeh.application_id = 222
AND aeh.balance_type_code = 'A'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.application_id = 222
AND aeh.ae_header_id = ael.ae_header_id
AND lnk.application_id = 222
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
AND aeh.ledger_id = ctlgd.set_of_books_id
AND ael.accounting_class_code = AC.Accounting_Class_Code
AND ( aeh.ledger_id = AC.Ledger_ID OR AC.Ledger_ID IS NULL )
GROUP BY ctlgd.customer_trx_line_id,
trunc(aeh.accounting_date),
ael.code_combination_id,
AC.Fin_Cat_Type_Code
) v1,
(select /*+ PARALLEL(a) */ * from ra_customer_trx_lines_all a) ctl,
(select /*+ PARALLEL(a) */ * from ra_customer_trx_all a) ct,
--**bug 3437052: move sob 2 places down
(select /*+ PARALLEL(a) */ * from gl_ledgers_public_v a) sob,
(select /*+ PARALLEL(a) */ * from ra_cust_trx_types_all a) ctt,
(select /*+ PARALLEL(a) */ * from hz_cust_accounts a) bill_acct ,
(select /*+ PARALLEL(a) */ * from ra_customer_trx_lines_all a) ctl_parent,
(select /*+ PARALLEL(a) */ * from oe_order_lines_all a) sl_child,
(select /*+ PARALLEL(a) */ * from oe_order_headers_all a) sh,
(select /*+ PARALLEL(a) */ * from oe_order_lines_all a) sl_parent
WHERE ccdim.code_combination_id = gcc.code_combination_id
AND slga.chart_of_accounts_id = ccdim.chart_of_accounts_id
AND ( slga.bal_seg_value_id = ccdim.company_id
OR slga.bal_seg_value_id = -1 )
AND slga.ledger_id = DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
-- AND ffcta.fin_category_id = ccdim.natural_account_id
-- AND ffcta.fin_cat_type_code in ('R', 'DR')
AND ctl_parent.customer_trx_line_id (+) =
nvl(ctl.previous_customer_trx_line_id,ctl.customer_trx_line_id)
AND sl_child.line_iD (+) =
case when (ctl_parent.interface_line_context in ('ORDER ENTRY', 'INTERCOMPANY')
and ltrim(ctl_parent.interface_line_attribute6, '0123456789') is NULL)
then to_number(ctl_parent.interface_line_attribute6)
else to_number(NULL) end
AND sh.header_id (+) = sl_child.header_id
AND sl_parent.line_id(+) = NVL(sl_child.top_model_line_id, sl_child.line_id)
--**bug 3361888
AND sob.ledger_id = DECODE(v1.transaction_class, 'ADJ', v1.set_of_books_id, ct.set_of_books_id)
AND gcc.code_combination_id = v1.code_combination_id
AND bill_acct.cust_account_id(+) = ct.bill_to_customer_id
AND ct.complete_flag = 'Y'
AND nvl(ctl.interface_line_context, 'xxx') <> 'PA INVOICES'
AND ctl.customer_trx_line_id (+) = v1.customer_trx_line_id
AND ct.customer_trx_id = DECODE(v1.transaction_class,'ADJ',v1.customer_trx_id,ctl.customer_trx_id)
AND nvl(ct.org_id, -999) = DECODE(v1.transaction_class,'ADJ',nvl(v1.org_id, -999),nvl(ct.org_id, -999))
AND ctt.cust_trx_type_id(+) = ct.cust_trx_type_id
AND ctt.org_id (+) = ct.org_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND NVL(ctt.post_to_gl,'Y') = 'Y';
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into staging table.');
insert into fii_ar_revenue_rates_temp
(FUNCTIONAL_CURRENCY,
TRX_DATE,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE)
select cc functional_currency,
dt trx_date,
decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(dt, sysdate))) PRIM_CONVERSION_RATE,
decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(dt, sysdate))) SEC_CONVERSION_RATE
from (
select /*+ no_merge parallel(stg) */ distinct
FUNCTIONAL_CURRENCY cc,
TRUNC(GL_DATE) dt
from FII_AR_REVENUE_STG stg
);
SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_B F (
REVENUE_PK,
GL_DATE_ID,
GL_DATE,
INVENTORY_ITEM_ID,
OPERATING_UNIT_ID,
--commented by ilavenil COMPANY_COST_CENTER_ORG_ID,
COMPANY_ID,
COST_CENTER_ID,
--above columns added by ilavenil
INVOICE_NUMBER,
ORDER_LINE_ID,
BILL_TO_PARTY_ID,
FUNCTIONAL_CURRENCY,
TRANSACTION_CURRENCY,
LEDGER_ID,
INVOICE_ID,
AMOUNT_T,
AMOUNT_B,
PRIM_AMOUNT_G,
SEC_AMOUNT_G,
TOP_MODEL_ITEM_ID,
ORGANIZATION_ID,
item_organization_id,
om_product_revenue_flag,
TRANSACTION_CLASS,
FIN_CATEGORY_ID,
ORDER_NUMBER,
SALES_CHANNEL,
INVOICE_LINE_ID,
LAST_UPDATE_DATE,
CREATION_DATE,
POSTED_FLAG,
PROD_CATEGORY_ID,
CHART_OF_ACCOUNTS_ID,
UPDATE_SEQUENCE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVOICE_DATE,
FIN_CAT_TYPE_CODE,
REV_BOOKED_DATE,
CHILD_ORDER_LINE_ID)
SELECT /*+ ORDERED PARALLEL(stg) PARALLEL(rates) USE_HASH(stg, rates) */
stg.REVENUE_PK,
stg.GL_DATE_ID,
stg.GL_DATE,
stg.INVENTORY_ITEM_ID,
stg.OPERATING_UNIT_ID,
--commented by ilavenil stg.COMPANY_COST_CENTER_ORG_ID,
stg.company_id COMPANY_ID,
stg.cost_center_id COST_CENTER_ID,
--above 2 columns added by ilavenil
stg.INVOICE_NUMBER,
stg.ORDER_LINE_ID,
stg.BILL_TO_PARTY_ID,
stg.FUNCTIONAL_CURRENCY,
stg.TRANSACTION_CURRENCY,
stg.LEDGER_ID,
stg.INVOICE_ID,
stg.AMOUNT_T,
stg.AMOUNT_B,
ROUND(stg.AMOUNT_B * NVL(rates.prim_conversion_rate, 1) /
to_char(g_mau_prim)) * to_char(g_mau_prim),
ROUND(stg.AMOUNT_B * NVL(rates.sec_conversion_rate, 1) /
to_char(g_mau_sec)) * to_char(g_mau_sec),
stg.TOP_MODEL_ITEM_ID,
stg.ORGANIZATION_ID,
stg.item_organization_id,
stg.om_product_revenue_flag,
stg.TRANSACTION_CLASS,
stg.FIN_CATEGORY_ID,
stg.ORDER_NUMBER,
stg.SALES_CHANNEL,
stg.INVOICE_LINE_ID,
SYSDATE,
SYSDATE,
stg.POSTED_FLAG,
stg.PROD_CATEGORY_ID,
stg.CHART_OF_ACCOUNTS_ID,
seq_id,
g_fii_user_id,
g_fii_user_id,
g_fii_login_id,
stg.invoice_date,
stg.fin_cat_type_code,
stg.REV_BOOKED_DATE,
stg.CHILD_ORDER_LINE_ID
FROM fii_ar_revenue_rates_temp rates, FII_AR_REVENUE_STG stg
where TRUNC(stg.GL_DATE) = rates.trx_date
and stg.functional_currency = rates.functional_currency;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into base summary table.');
FUNCTION UPDATE_GLOBAL_START_DATE_TBL( p_glbl_strt_dt DATE ) RETURN NUMBER IS
l_count NUMBER;
l_updated NUMBER;
l_updated := 0;
select count(*) into l_count
from FII_GLOBAL_START_DATES;
select GLOBAL_START_DATE into l_glbl_strt_dt
from FII_GLOBAL_START_DATES;
update FII_GLOBAL_START_DATES
set GLOBAL_START_DATE = p_glbl_strt_dt,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_fii_user_id,
LAST_UPDATE_LOGIN = g_fii_login_id;
l_updated := 1;
insert into FII_GLOBAL_START_DATES(
GLOBAL_START_DATE,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
)
values(
p_glbl_strt_dt,
sysdate, g_fii_user_id,
sysdate, g_fii_user_id, g_fii_login_id
);
l_updated := 1;
return l_updated;
Error in Procedure: UPDATE_GLOBAL_START_DATE_TBL
Message: '||sqlerrm;
END UPDATE_GLOBAL_START_DATE_TBL;
BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_I');
BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_L');
l_count := UPDATE_GLOBAL_START_DATE_TBL( g_gl_from_date );
SELECT trunc(min(stu.start_date))
INTO l_gl_from_date1
FROM gl_period_statuses stu,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.ledger_id = stu.set_of_books_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND stu.application_id = 222
AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
AND stu.last_update_date > l_last_start_date))
AND stu.end_date >= g_global_start_date;
SELECT trunc(min(stu.start_date))
INTO l_gl_from_date2
FROM gl_period_statuses stu,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE slga.ledger_id = stu.set_of_books_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND stu.application_id = 222
AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
AND stu.last_update_date > l_last_start_date))
AND stu.end_date >= g_global_start_date;
fii_util.put_line('This program will collect data with Last update date range between '||
to_char(g_lud_from_date,'MM/DD/YYYY HH24:MI:SS')||' and '||
to_char(g_lud_to_date,'MM/DD/YYYY HH24:MI:SS'));
and/or Deferred Revenue since the last Load / Update program.
Please run the Request Set in the Initial mode to repopulate the summaries.');
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 = 'AR_RESUMMARIZE'
AND item_value = 'Y';
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 = 'AR_PROD_CHANGE'
AND item_value = 'Y';
SELECT 1
INTO l_stg_count
FROM fii_ar_revenue_stg
WHERE rownum = 1;
select 1 into l_count
from fii_fin_cat_type_assgns
where fin_cat_type_code = 'DR'
and rownum = 1;
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code = g_program_code_DR
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
select 1 into l_count
from ACCNT_CLASS
where rownum = 1;
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_AR_REVENUE_JOBS;
Update FII_AR_REVENUE_STG stg
SET prim_conversion_rate =
fii_currency.get_global_rate_primary(stg.functional_currency, least(stg.exchange_date, sysdate))
WHERE stg.prim_conversion_rate < 0;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
Update FII_AR_REVENUE_STG stg
SET sec_conversion_rate =
fii_currency.get_global_rate_secondary(stg.functional_currency, least(stg.exchange_date, sysdate))
WHERE stg.sec_conversion_rate < 0;
FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
SELECT count(*) INTO l_dup
from (SELECT 1
FROM FII_AR_REVENUE_STG b2
GROUP BY b2.revenue_pk
HAVING count(*)>1
);
fii_util.put_line('Inserting new revenue accounts in fii_ar_rev_accts ');
INSERT INTO fii_ar_rev_accts (rev_acct_id, fin_cat_type_code)
SELECT ffcta.fin_category_id, ffcta.fin_cat_type_code
FROM fii_fin_cat_type_assgns ffcta
WHERE ffcta.fin_cat_type_code in ('R', 'DR');
INSERT INTO fii_ar_rev_accts (rev_acct_id, fin_cat_type_code)
SELECT temp.cur_rev_acct_id, temp.cur_fin_cat_type_code
FROM '||g_fii_schema||'.fii_ar_rev_accts_temp temp,
fii_ar_rev_accts fra
WHERE temp.cur_rev_acct_id = fra.rev_acct_id(+)
AND temp.cur_fin_cat_type_code = fra.fin_cat_type_code(+)
AND fra.rev_acct_id IS NULL ';
INSERT INTO fii_ar_rev_accts (rev_acct_id, rev_acct, fin_cat_type_code)
WITH ACCNT_CLASS AS (SELECT XAD.Ledger_ID,
XACA.Accounting_Class_Code,
decode(XAD.Program_Code,
g_program_code_R, 'R',
g_program_code_DR, 'DR',
NULL) Fin_Cat_Type_Code
FROM XLA_Assignment_Defns_B XAD,
XLA_Acct_Class_Assgns XACA
WHERE XAD.Program_Code in (g_program_code_R,
g_program_code_DR)
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
SELECT * FROM ACCNT_CLASS;
SELECT sum(rows_processed)
INTO l_count
FROM fii_ar_revenue_jobs
WHERE function = 'POPULATE_STG';
/* SELECT sum(rows_processed)
INTO l_count
FROM fii_ar_revenue_jobs
WHERE function = 'DETECT_DELETED_INV';
fii_util.put_line('Found '||l_count||' invoice(s) deleted');
USING ( SELECT 'AR_MAX_GROUP_ID' LOG_ITEM,
to_char( nvl(max(group_id), -1) ) ITEM_VALUE
FROM xla_ae_headers ) new
ON ( new.LOG_ITEM = log.LOG_ITEM )
WHEN MATCHED THEN
UPDATE SET
log.ITEM_VALUE = new.ITEM_VALUE,
log.LAST_UPDATE_DATE = SYSDATE,
log.LAST_UPDATE_LOGIN = g_fii_login_id,
log.LAST_UPDATED_BY = g_fii_user_id
WHEN NOT MATCHED THEN
INSERT( LOG_ITEM,
ITEM_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY )
VALUES( new.LOG_ITEM,
new.ITEM_VALUE,
SYSDATE,
g_fii_user_id,
SYSDATE,
g_fii_login_id,
g_fii_user_id );
UPDATE FII_AR_REVENUE_JOBS
SET status = 'FAILED'
WHERE rownum < 2;
UPDATE FII_AR_REVENUE_JOBS
SET status = 'FAILED'
WHERE rownum < 2;
SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
NVL(sum(decode(status,'FAILED',1,0)),0),
NVL(sum(decode(status,'UNASSIGNED',
decode(phase, l_curr_phase, 1, 0), 0)),0),
NVL(sum(decode(status,'COMPLETED',
decode(phase, l_curr_phase, 1, 0), 0)),0),
NVL(sum(decode(phase, l_curr_phase, 1, 0)),0)
INTO l_unassigned_cnt,
l_failed_cnt,
l_curr_unasgn_cnt,
l_curr_comp_cnt,
l_curr_tot_cnt
FROM FII_AR_REVENUE_JOBS;
UPDATE FII_AR_REVENUE_JOBS
SET status = 'IN PROCESS',
start_time = sysdate,
worker = g_worker_num
WHERE status = 'UNASSIGNED'
AND phase = l_curr_phase
AND priority = (
SELECT min(priority)
FROM fii_ar_revenue_jobs
WHERE status = 'UNASSIGNED'
AND phase = l_curr_phase)
AND rownum < 2;
SELECT function,
date_parameter1,
date_parameter2,
date_parameter3,
date_parameter4,
number_parameter1,
number_parameter2,
char_parameter1,
char_parameter2
INTO l_function,
g_gl_from_date,
g_gl_to_date,
g_lud_from_date,
g_lud_to_date,
l_num_parameter1,
l_num_parameter2,
l_char_parameter1,
l_char_parameter2
FROM FII_AR_REVENUE_JOBS
WHERE worker = p_worker_no
AND status = 'IN PROCESS';
fii_util.put_line(' last updated from date='||
to_char(g_lud_from_date,'YYYY/MM/DD HH24:MI:SS'));
fii_util.put_line(' last updated to date='||
to_char(g_lud_to_date,'YYYY/MM/DD HH24:MI:SS'));
UPDATE FII_AR_REVENUE_JOBS
SET status = 'COMPLETED',
end_time = sysdate,
rows_processed = l_count
WHERE worker = p_worker_no
AND status = 'IN PROCESS';
UPDATE FII_AR_REVENUE_JOBS
SET status = 'FAILED',
end_time = sysdate
WHERE worker = p_worker_no
AND status = 'IN PROCESS';
v1 => 'Update Receivables Revenue Summary Subworker',
t2 => 'STATUS',
v2 => 'failed!');