[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id
FROM ic_item_mst_b_mig
WHERE item_id = p_item_id
AND inventory_item_id IS NOT NULL
AND ROWNUM = 1;
SELECT to_number(a.org_information2) legal_entity_id
FROM hr_organization_information a, gl_plcy_mst b
WHERE a.organization_id = b.org_id
AND b.co_code = p_co_code
AND p_source_type = 'O'
and a.org_information_context = 'Operating Unit Information'
UNION
SELECT a.legal_entity_id
FROM gl_plcy_mst a
WHERE a.co_code = p_co_code
AND p_source_type = 'N';
SELECT to_number(a.org_information2) legal_entity_id
FROM hr_organization_information a
WHERE a.organization_id = p_organization_id
AND a.org_information_context = 'Accounting Information';
SELECT to_number(a.org_information2) legal_entity_id
FROM hr_organization_information a, ic_whse_mst b
WHERE a.organization_id = decode(nvl(b.subinventory_ind_flag, 'N'), 'Y', b.organization_id, b.mtl_organization_id)
AND b.whse_code = p_whse_code
AND a.org_information_context = 'Accounting Information';
SELECT item_number
FROM mtl_item_flexfields
WHERE inventory_item_id = p_inventory_item_id
AND ROWNUM = 1;
SELECT cust_no
FROM op_cust_mst
WHERE cust_id = p_cust_id;
SELECT of_vendor_site_id
FROM po_vend_mst
WHERE vendor_id = p_vendor_id;
SELECT vendor_no
FROM po_vend_mst
WHERE vendor_id = p_vendor_id;
SELECT reason_id
FROM mtl_transaction_Reasons
WHERE reason_name = p_reason_code;
SELECT routing_no,
routing_vers
FROM GMD_ROUTINGS_B
WHERE routing_id = p_routing_id;
SELECT price_element_type_id
FROM po_cost_mst
WHERE aqui_cost_id = p_aqui_cost_id
AND nvl(migrated_ind, 0) = 1;
SELECT cost_cmpntcls_code
FROM cm_cmpt_mst
WHERE cost_cmpntcls_id = p_cost_cmpntcls_id;
SELECT order_type_code
FROM op_ordr_typ
WHERE lang_code = userenv('LANG')
AND nvl(p_source_type,0) = 0
AND order_type = p_Order_type
UNION ALL
SELECT tl.name
FROM oe_transaction_types_all t,
oe_transaction_types_tl tl
WHERE t.transaction_type_id = tl.transaction_type_id
AND tl.language = userenv('LANG')
AND nvl(p_source_type,0) = 11
AND t.transaction_type_code = 'ORDER'
AND t.transaction_type_id = p_Order_type;
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'LINE_TYPE'
AND nvl(start_date_active,sysdate) <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND enabled_flag = 'Y'
AND lookup_code = p_line_type;
SELECT rctta.name
FROM ra_cust_trx_types_all rctta, ar_lookups al, gl_plcy_mst plcy
WHERE sysdate between nvl(rctta.start_date, sysdate-1) and nvl(rctta.end_date, sysdate+1)
AND al.lookup_type = 'INV/CM'
AND al.lookup_code = rctta.type
AND rctta.org_id = plcy.org_id
AND plcy.legal_entity_id = p_legal_entity_id
AND rctta.cust_trx_type_id = p_Ar_trx_type_id;
SELECT description
FROM mtl_categories_vl
WHERE structure_id IN (
SELECT fifs.id_flex_num
FROM fnd_id_flex_structures_vl fifs
WHERE fifs.application_id = 401
AND fifs.id_flex_code = 'MCAT'
AND fifs.id_flex_structure_code = 'GL_BUSINESS_CLASS'
AND enabled_flag = 'Y'
)
AND category_id = p_Gl_business_class_cat_id;
SELECT description
FROM mtl_categories_vl
WHERE structure_id IN
(
SELECT fifs.id_flex_num
FROM fnd_id_flex_structures_vl fifs
WHERE fifs.application_id = 401
AND fifs.id_flex_code = 'MCAT'
AND fifs.id_flex_structure_code = 'GL_PRODUCT_LINE'
AND enabled_flag = 'Y'
)
AND category_id = p_Gl_product_line_cat_id;
SELECT uom_code
FROM sy_uoms_mst
WHERE um_code = p_um_code;
SELECT p.type,
p.length,
p.segment_no segment_ref,
pm.segment_delimiter
FROM gl_plcy_seg p,
gl_plcy_mst pm,
fnd_id_flex_segments f,
gl_sets_of_books s
WHERE p.co_code = p_co_code
AND p.delete_mark = 0
AND p.co_code = pm.co_code
AND pm.sob_id = s.set_of_books_id
AND s.chart_of_accounts_id = f.id_flex_num
AND f.application_id = 101
AND f.id_flex_code = 'GL#'
AND LOWER(f.segment_name) = LOWER(p.short_name)
AND f.enabled_flag = 'Y'
ORDER BY f.segment_num;
SELECT concatenated_segment_delimiter,
glsob.chart_of_accounts_id
FROM gl_sets_of_books glsob,
fnd_id_flex_structures fifstr,
fnd_application fa,
gl_plcy_mst gpm
WHERE glsob.chart_of_accounts_id = fifstr.id_flex_num
AND fifstr.id_flex_code = 'GL#'
AND fifstr.application_id = fa.application_id
AND fa.application_short_name = 'SQLGL'
AND gpm.sob_id = glsob.set_of_books_id
AND gpm.co_code = p_co_code;
select a.co_code
from sy_orgn_mst a,
ic_whse_mst b
where a.orgn_code = b.orgn_code
and b.whse_code = p_whse_code;
* variable reference in update *
* 23-Jun-2006 rseshadr bug 5354837 - do not rely on the view *
* gmf_legal_entities. The underlying tables are not populated *
* until after a much later phase (upg+74). Use the same logic *
* as an auto upgrade without the pre-mig ui *
* *
**********************************************************************/
PROCEDURE Migrate_Fiscal_Policies_LE
(
P_migration_run_id IN NUMBER,
P_commit IN VARCHAR2,
X_failure_count OUT NOCOPY NUMBER
)
IS
/***************************
* PL/SQL Table Definitions *
***************************/
/************************
* Local Variables *
************************/
L_legal_entity_id NUMBER(15);
SELECT *
FROM gl_plcy_mst
WHERE NVL(migrated_ind,'~') <> '1'
ORDER BY legal_entity_id NULLS LAST;
* Insert a row into gmf_fiscal_policies *
****************************************/
FOR i IN Cur_get_fiscal_policies
LOOP
IF i.legal_entity_id IS NULL THEN
BEGIN
SELECT to_number(org_information2),
to_number(org_information3)
INTO l_legal_entity_id,
l_ledger_id
FROM hr_organization_information
WHERE org_information_context = 'Operating Unit Information'
AND organization_id = i.org_id;
SELECT to_number(org_information3)
INTO l_ledger_id
FROM hr_organization_information
WHERE org_information_context = 'Operating Unit Information'
AND organization_id = i.org_id;
SELECT count(1)
INTO l_le_count
FROM gmf_fiscal_policies
WHERE legal_entity_id = L_legal_entity_id;
INSERT INTO gmf_fiscal_policies
(
LEGAL_ENTITY_ID,
BASE_CURRENCY_CODE,
LEDGER_ID,
MTL_CMPNTCLS_ID,
MTL_ANALYSIS_CODE,
GL_COST_MTHD,
COST_BASIS,
TEXT_CODE,
DELETE_MARK,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
ATTRIBUTE_CATEGORY
)
VALUES
(
L_legal_entity_id,
i.base_currency_code,
L_ledger_id,
i.mtl_cmpntcls_id,
i.mtl_analysis_code,
NVL(i.new_le_cost_mthd_code,i.gl_cost_mthd),
i.cost_basis,
i.text_code,
i.delete_mark,
i.created_by,
i.creation_date,
i.last_update_login,
i.last_update_date,
i.last_updated_by,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
i.attribute15,
i.attribute16,
i.attribute17,
i.attribute18,
i.attribute19,
i.attribute20,
i.attribute21,
i.attribute22,
i.attribute23,
i.attribute24,
i.attribute25,
i.attribute26,
i.attribute27,
i.attribute28,
i.attribute29,
i.attribute30,
i.attribute_category
);
UPDATE gl_plcy_mst
SET migrated_ind = '1',
legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
last_update_date = SYSDATE
WHERE co_code = i.co_code;
UPDATE gl_plcy_mst
SET migrated_ind = '1',
legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
last_update_date = SYSDATE
WHERE co_code = i.co_code;
SELECT count(*)
INTO x_failure_count
FROM gl_plcy_mst
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL);
* 22-aug-2006 bug 5473365, pmarada, inserting records in *
* gmf_ledger_valuation_methods table *
* *
**********************************************************************/
PROCEDURE Migrate_Fiscal_Policies_Others
(
P_migration_run_id IN NUMBER,
P_commit IN VARCHAR2,
X_failure_count OUT NOCOPY NUMBER
)
IS
/**************************
* PL/SQL Table Definition *
**************************/
/******************
* Local Variables *
******************/
BEGIN
G_Migration_run_id := P_migration_run_id;
* Update a row in GMF_FISCAL_POLICIES for GL cost Types *
********************************************************/
BEGIN
UPDATE gmf_fiscal_policies a
SET a.cost_type_id = (
SELECT x.cost_type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.gl_cost_mthd
)
WHERE a.cost_type_id IS NULL AND a.gl_cost_mthd IS NOT NULL;
* Insert rows in GMF_ledger_valuation_methods table for the legal entity *
**************************************************************************/
INSERT INTO gmf_ledger_valuation_methods
(
legal_entity_id,
ledger_id,
cost_type_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
text_code,
delete_mark
)
SELECT gfp.legal_entity_id,
gfp.ledger_id,
gfp.cost_type_id,
gfp.creation_date,
gfp.created_by,
gfp.last_update_date,
gfp.last_updated_by,
gfp.last_update_login,
gfp.text_code,
0
FROM gmf_fiscal_policies gfp
WHERE NOT EXISTS (
SELECT '1'
FROM gmf_ledger_valuation_methods glvm
WHERE glvm.legal_entity_id = gfp.legal_entity_id
AND glvm.ledger_id = gfp.ledger_id
);
SELECT count(*)
INTO x_failure_count
FROM gmf_fiscal_policies
WHERE (cost_type_id IS NULL AND gl_cost_mthd IS NOT NULL);
* Update rows For Cost Type Identifier *
*****************************************/
UPDATE cm_mthd_mst
SET cost_type_id = gmf_cost_type_id_s.NEXTVAL
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL);
SELECT count(*)
INTO x_failure_count
FROM cm_mthd_mst
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL);
* Update a row in cm_mthd_mst for Lot Costs *
*********************************************/
BEGIN
UPDATE cm_mthd_mst a
SET a.default_lot_cost_type_id = (
SELECT x.cost_type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.default_lot_cost_mthd
),
a.cost_type = 6,
a.lot_actual_cost = NULL
WHERE cost_type_id IS NOT NULL
AND a.cost_type = 1
AND nvl(a.lot_actual_cost,-1) = 1;
SELECT count(*)
INTO x_failure_count
FROM cm_mthd_mst
WHERE (
(default_lot_cost_type_id IS NULL AND default_lot_cost_mthd IS NOT NULL)
OR (cost_type = 1 AND nvl(lot_actual_cost,-1) = 1)
);
SELECT a.legal_entity_id,
a.cost_Type_id,
a.calendar_code
FROM gmf_calendar_assignments a
ORDER BY a.legal_entity_id,
a.cost_Type_id,
a.calendar_code;
* Insert a row in gmf_calendar_assignments for Direct Calendar Assignments *
*****************************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
SELECT gmf_calendar_assignments_s.NEXTVAL,
a.calendar_code,
b.legal_entity_id,
c.cost_type_id,
a.creation_date,
a.created_by,
a.last_update_date,
a.last_updated_by,
a.last_update_login,
a.text_code,
a.delete_mark
FROM cm_cldr_hdr_b a,
gl_plcy_mst b,
cm_mthd_mst c
WHERE a.cost_mthd_code IS NOT NULL
AND a.co_code IS NOT NULL
AND b.legal_entity_id IS NOT NULL
AND c.cost_type_id IS NOT NULL
AND a.co_code = b.co_code
AND c.cost_mthd_code = a.cost_mthd_code
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = a.calendar_code
AND x.cost_type_id = c.cost_type_id
AND x.legal_entity_id = b.legal_entity_id
);
* Insert a row in gmf_calendar_assignments for transaction table data *
**********************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
(
SELECT gmf_calendar_assignments_s.NEXTVAL,
g.calendar_code,
i.legal_entity_id,
h.cost_type_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
g.last_update_login,
g.text_code,
1
FROM cm_cldr_hdr_b g,
cm_mthd_mst h,
gl_plcy_mst i
WHERE g.co_code IS NOT NULL
AND i.legal_entity_id IS NOT NULL
AND g.cost_mthd_code <> h.cost_mthd_code
AND i.co_code = g.co_code
AND EXISTS (
SELECT 'CM_RSRC_DTL'
FROM cm_rsrc_dtl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_CMPT_DTL'
FROM cm_cmpt_dtl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_BRDN_DTL'
FROM cm_brdn_dtl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_ADJS_DTL'
FROM cm_adjs_dtl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_RLUP_CTL'
FROM cm_rlup_ctl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_ACPR_CTL'
FROM cm_acpr_ctl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
UNION
SELECT 'CM_CUPD_CTL'
FROM cm_cupd_ctl a
WHERE a.calendar_code is not null
AND a.cost_mthd_Code is not null
AND a.calendar_code = g.calendar_code
AND a.cost_mthd_code = h.cost_mthd_code
)
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = g.calendar_code
AND x.cost_type_id = h.cost_type_id
AND x.legal_entity_id = i.legal_entity_id
)
);
* Insert a row in gmf_calendar_assignments for CM_RSRC_DTL table data *
**********************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
(
SELECT gmf_calendar_assignments_s.NEXTVAL,
x.*
FROM (
SELECT DISTINCT
g.calendar_code,
i.legal_entity_id,
h.cost_type_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
g.last_update_login,
g.text_code,
1
FROM cm_cldr_hdr_b g,
cm_mthd_mst h,
gl_plcy_mst i,
sy_orgn_mst j,
cm_rsrc_dtl k
WHERE g.co_code IS NOT NULL
AND j.orgn_code = k.orgn_code
AND i.co_code = j.co_code
AND i.legal_entity_id IS NOT NULL
AND j.co_code <> g.co_code
AND h.cost_mthd_code = k.cost_mthd_code
AND g.calendar_code = k.calendar_code
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = g.calendar_code
AND x.cost_type_id = h.cost_Type_id
AND x.legal_entity_id = i.legal_Entity_id
)
) x
);
* Insert a row in gmf_calendar_assignments for CM_BRDN_DTL table data *
**********************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
(
SELECT gmf_calendar_assignments_s.NEXTVAL,
x.*
FROM (
SELECT DISTINCT
g.calendar_code,
i.legal_entity_id,
h.cost_type_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
g.last_update_login,
g.text_code,
1
FROM cm_cldr_hdr_b g,
cm_mthd_mst h,
gl_plcy_mst i,
sy_orgn_mst j,
cm_brdn_dtl k
WHERE g.co_code IS NOT NULL
AND j.orgn_code = k.orgn_code
AND i.co_code = j.co_code
AND i.legal_entity_id IS NOT NULL
AND j.co_code <> g.co_code
AND h.cost_mthd_code = k.cost_mthd_code
AND g.calendar_code = k.calendar_code
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = g.calendar_code
AND x.cost_type_id = h.cost_Type_id
AND x.legal_entity_id = i.legal_Entity_id
)
) x
);
* Insert a row in gmf_calendar_assignments for CM_CMPT_DTL table data *
**********************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
(
SELECT gmf_calendar_assignments_s.NEXTVAL,
x.*
FROM (
SELECT DISTINCT
g.calendar_code,
i.legal_entity_id,
h.cost_type_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
g.last_update_login,
g.text_code,
1
FROM cm_cldr_hdr_b g,
cm_mthd_mst h,
gl_plcy_mst i,
sy_orgn_mst j,
cm_cmpt_dtl k,
ic_whse_mst l
WHERE g.co_code IS NOT NULL
AND l.whse_code = k.whse_code
AND j.orgn_code = l.orgn_code
AND i.co_code = j.co_code
AND i.legal_entity_id IS NOT NULL
AND j.co_code <> g.co_code
AND h.cost_mthd_code = k.cost_mthd_code
AND g.calendar_code = k.calendar_code
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = g.calendar_code
AND x.cost_type_id = h.cost_Type_id
AND x.legal_entity_id = i.legal_Entity_id
)
) x
);
* Insert a row in gmf_calendar_assignments for CM_ADJS_DTL table data *
**********************************************************************/
BEGIN
INSERT INTO gmf_calendar_assignments
(
ASSIGNMENT_ID,
CALENDAR_CODE,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
(
SELECT gmf_calendar_assignments_s.NEXTVAL,
x.*
FROM (
SELECT DISTINCT
g.calendar_code,
i.legal_entity_id,
h.cost_type_id,
g.creation_date,
g.created_by,
g.last_update_date,
g.last_updated_by,
g.last_update_login,
g.text_code,
1
FROM cm_cldr_hdr_b g,
cm_mthd_mst h,
gl_plcy_mst i,
sy_orgn_mst j,
cm_adjs_dtl k,
ic_whse_mst l
WHERE g.co_code IS NOT NULL
AND l.whse_code = k.whse_code
AND j.orgn_code = l.orgn_code
AND i.co_code = j.co_code
AND i.legal_entity_id IS NOT NULL
AND j.co_code <> g.co_code
AND h.cost_mthd_code = k.cost_mthd_code
AND g.calendar_code = k.calendar_code
AND NOT EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.calendar_code = g.calendar_code
AND x.cost_type_id = h.cost_Type_id
AND x.legal_entity_id = i.legal_Entity_id
)
) x
);
UPDATE GMF_CALENDAR_ASSIGNMENTS g
SET g.delete_mark = 1
WHERE g.delete_mark <> 1
AND EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments a,
cm_cldr_dtl b
WHERE a.calendar_code = b.calendar_code
AND a.calendar_code = g.calendar_code
AND a.legal_entity_id = g.legal_entity_id
AND a.cost_Type_id = g.cost_type_id
AND EXISTS (
SELECT 'X' FROM (
SELECT m.legal_entity_id,
m.cost_type_id,
m.calendar_code,
min(n.start_date) mindate,
max(n.end_date) maxdate
FROM gmf_calendar_assignments m,
cm_cldr_dtl n
WHERE m.calendar_code = n.calendar_code
AND m.calendar_code = i.calendar_code
AND m.legal_entity_id = i.legal_entity_id
AND m.cost_type_id = i.cost_type_id
AND m.delete_mark <> 1
GROUP by m.legal_entity_id,
m.calendar_code,
m.cost_type_id
) x
WHERE x.legal_entity_id = a.legal_entity_id
AND x.cost_type_id = a.cost_Type_id
AND x.calendar_code <> a.calendar_Code
AND (
b.start_date BETWEEN x.mindate AND x.maxdate
OR
b.end_date BETWEEN x.mindate AND x.maxdate
)
)
);
* Insert a row into gmf_period_statuses *
*****************************************/
BEGIN
INSERT INTO gmf_period_statuses
(
PERIOD_ID,
LEGAL_ENTITY_ID,
COST_TYPE_ID,
CALENDAR_CODE,
PERIOD_CODE,
START_DATE,
END_DATE,
PERIOD_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TEXT_CODE,
DELETE_MARK
)
SELECT gmf_period_id_s.NEXTVAL,
a.legal_entity_id,
a.cost_type_id,
a.calendar_code,
b.period_code,
b.start_date, /* Bug#5716122 ANTHIYAG 16-Dec-2006 */
b.end_date, /* Bug#5716122 ANTHIYAG 16-Dec-2006 */
decode(b.period_status, 0, 'O', 1, 'F', 2, 'C', 'O') period_status,
b.creation_date,
b.created_by,
b.last_update_date,
b.last_updated_by,
b.last_update_login,
b.text_code,
decode(a.delete_mark + b.delete_mark, 0, 0, 1)
FROM gmf_calendar_assignments a,
cm_cldr_dtl b,
cm_cldr_hdr_b h
WHERE a.calendar_code = b.calendar_code
AND b.calendar_code = h.calendar_code
AND h.co_code IS NOT NULL
AND h.cost_mthd_code IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM gmf_period_statuses p
WHERE p.legal_entity_id = a.legal_entity_id
AND p.cost_type_id = a.cost_type_id
AND p.calendar_code = b.calendar_code
AND p.period_code = b.period_code
);
UPDATE gmf_period_statuses a
SET a.delete_mark = 1
WHERE EXISTS (
SELECT 'X'
FROM gmf_calendar_assignments x
WHERE x.legal_entity_id = a.legal_entity_id
AND x.calendar_code = a.calendar_code
AND x.cost_type_id = a.cost_type_id
AND x.delete_mark = 1
);
SELECT DISTINCT item_id,
organization_id
FROM (
SELECT a.item_id,
nvl(DECODE(NVL(c.subinventory_ind_flag,'N'), 'Y', c.organization_id, c.mtl_organization_id), DECODE(NVL(b.subinventory_ind_flag,'N'), 'Y', b.organization_id, b.mtl_organization_id)) organization_id
FROM gmf_burden_percentages a,
ic_whse_mst b,
ic_whse_mst c
WHERE a.item_id IS NOT NULL
AND b.orgn_code = a.orgn_code
AND c.whse_code(+) = a.whse_code
);
* Update a row in GMF_BURDEN_PERCENTAGES *
********************************************************/
BEGIN
INSERT
INTO gmf_burden_percentages
(
burden_percentage_id,
calendar_code,
period_code,
cost_mthd_code,
burden_id,
orgn_code,
whse_code,
item_id,
percentage,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
gl_business_category_id,
gl_category_id,
cost_category_id,
gl_prod_line_category_id
)
(
SELECT gmf_burden_percentage_id_s.NEXTVAL,
a.calendar_code,
a.period_code,
a.cost_mthd_code,
a.burden_id,
a.orgn_code,
e.whse_code,
a.item_id,
a.percentage,
a.created_by,
sysdate,
a.last_updated_by,
sysdate,
a.last_update_login,
a.delete_mark,
a.gl_business_category_id,
a.gl_category_id,
a.cost_category_id,
a.gl_prod_line_category_id
FROM gmf_burden_percentages a,
ic_whse_mst e
WHERE a.orgn_code IS NOT NULL
AND a.whse_code IS NULL
AND a.orgn_code = e.orgn_code
AND e.mtl_organization_id IS NOT NULL
AND nvl(e.subinventory_ind_flag,'N') <> 'Y'
AND NOT EXISTS (
SELECT 'X'
FROM gmf_burden_percentages x
WHERE x.calendar_code = a.calendar_code
AND x.period_code = a.period_code
AND x.cost_mthd_code = a.cost_mthd_code
AND x.burden_id = a.burden_id
AND x.orgn_code = a.orgn_code
AND x.whse_code = e.whse_code
AND nvl(x.item_id, -1) = nvl(a.item_id, -1)
AND nvl(x.gl_category_id, -1) = nvl(a.gl_category_id, -1)
AND nvl(x.cost_category_id, -1) = nvl(a.cost_category_id, -1)
AND nvl(x.gl_business_category_id, -1) = nvl(a.gl_business_category_id, -1)
AND nvl(x.gl_prod_line_category_id, -1) = nvl(a.gl_prod_line_category_id, -1)
)
);
UPDATE gmf_burden_percentages a
SET (
a.cost_type_id,
a.period_id,
a.legal_entity_id
)
= (
SELECT x.cost_type_id,
x.period_id,
x.legal_entity_id
FROM gmf_period_statuses x,
cm_mthd_mst y,
cm_cldr_hdr_b z,
gl_plcy_mst w
WHERE y.cost_mthd_code = a.cost_mthd_code
AND x.cost_type_id = y.cost_type_id
AND x.calendar_code = a.calendar_code
AND x.period_code = a.period_code
AND z.calendar_code = x.calendar_code
AND z.co_code = w.co_code
AND x.legal_entity_id= w.legal_entity_id
),
(
a.organization_id,
a.delete_mark
)
= (
SELECT DECODE(a.whse_code, null, DECODE(NVL(Y.INVENTORY_ORG_IND,'N'), 'Y', y.organization_id, NULL), DECODE(NVL(x.subinventory_ind_flag,'N'), 'Y', x.organization_id, x.mtl_organization_id)),
DECODE(a.delete_mark, 1, a.delete_mark, DECODE(a.whse_code, null, DECODE(NVL(Y.INVENTORY_ORG_IND,'N'), 'Y', 0, 1), DECODE(NVL(x.subinventory_ind_flag,'N'), 'Y', 1, 0)))
FROM ic_whse_mst x, sy_orgn_mst y
WHERE x.whse_code = nvl(a.whse_code, x.whse_code)
and y.orgn_code = DECODE(a.whse_code, NULL, a.orgn_code, x.orgn_code)
AND ROWNUM = 1
)
WHERE (
(a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.calendar_code IS NOT NULL AND a.period_code IS NOT NULL AND a.period_id IS NULL)
OR (a.calendar_code IS NOT NULL AND a.legal_entity_id IS NULL)
OR (a.organization_id IS NULL AND (a.whse_code IS NOT NULL OR a.orgn_code IS NOT NULL))
);
UPDATE gmf_burden_percentages a
SET (
a.master_organization_id,
a.inventory_item_id
)
= (
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = a.legal_entity_id
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.master_organization_id IS NULL AND a.item_id IS NOT NULL);
UPDATE gmf_burden_percentages a
SET a.delete_mark = 1
WHERE ROWID NOT IN (
SELECT MIN(x.ROWID)
FROM gmf_burden_percentages x
WHERE x.legal_entity_id = a.legal_entity_id
AND x.period_id = a.period_id
AND x.cost_type_id = a.cost_type_id
AND x.burden_id = a.burden_id
AND nvl(x.inventory_item_id, -1) = nvl(a.inventory_item_id, -1)
AND nvl(x.organization_id, -1) = nvl(a.organization_id, -1)
AND nvl(x.gl_category_id, -1) = nvl(a.gl_category_id, -1)
AND nvl(x.cost_category_id, -1) = nvl(a.cost_category_id, -1)
AND nvl(x.gl_business_category_id, -1) = nvl(a.gl_business_category_id, -1)
AND nvl(x.gl_prod_line_category_id, -1) = nvl(a.gl_prod_line_category_id, -1)
AND x.delete_mark <> 1
);
SELECT count(*)
INTO x_failure_count
FROM gmf_burden_percentages
WHERE (
(cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
OR (calendar_code IS NOT NULL AND period_code IS NOT NULL AND period_id IS NULL)
OR (calendar_code IS NOT NULL AND legal_entity_id IS NULL)
OR (organization_id IS NULL AND delete_mark = 0 AND (whse_code IS NOT NULL OR orgn_code IS NOT NULL))
OR (inventory_item_id IS NULL AND item_id IS NOT NULL)
OR (master_organization_id IS NULL AND item_id IS NOT NULL)
);
* Modified Code to add insertion of Lot cost records for new *
* lots created by the Lot migration process for lot id's *
* *
**********************************************************************/
PROCEDURE Migrate_Lot_Costs
(
P_migration_run_id IN NUMBER,
P_commit IN VARCHAR2,
X_failure_count OUT NOCOPY NUMBER
)
IS
/**************************
* PL/SQL Table Definition *
**************************/
/******************
* Local Variables *
******************/
BEGIN
G_Migration_run_id := P_migration_run_id;
* Update a row for cost Types *
******************************/
UPDATE gmf_lot_costs a
SET a.cost_type_id
= (
SELECT x.cost_Type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.cost_mthd_code
),
(
a.organization_id,
a.inventory_item_id
)
= (
SELECT decode(x.cost_organization_id, -1, -1, y.organization_id),
y.inventory_item_id
FROM ic_whse_mst x,
ic_item_mst_b_mig y
WHERE x.whse_code = a.whse_code
AND y.item_id = a.item_id
AND y.organization_id = NVL(DECODE(x.cost_organization_id, -1, x.mtl_organization_id, x.cost_organization_id), x.mtl_organization_id)
)
WHERE (
(a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.organization_id IS NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
);
* Insert rows for Additional Lots Created as part of Lot Balances Migration *
****************************************************************************/
INSERT INTO gmf_lot_costs
(
header_id,
unit_cost,
cost_date,
onhand_qty,
frozen_ind,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
text_code,
delete_mark,
final_cost_flag,
cost_type_id,
inventory_item_id,
lot_number,
organization_id
)
(
SELECT gmf_cost_header_id_s.NEXTVAL,
a.unit_cost,
a.cost_date,
a.onhand_qty,
a.frozen_ind,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.attribute16,
a.attribute17,
a.attribute18,
a.attribute19,
a.attribute20,
a.attribute21,
a.attribute22,
a.attribute23,
a.attribute24,
a.attribute25,
a.attribute26,
a.attribute27,
a.attribute28,
a.attribute29,
a.attribute30,
a.attribute_category,
sysdate,
a.created_by,
sysdate,
a.last_updated_by,
a.last_update_login,
a.header_id,
a.delete_mark,
a.final_cost_flag,
a.cost_type_id,
a.inventory_item_id,
b.lot_number,
a.organization_id
FROM gmf_lot_costs a,
ic_lots_mst_mig b
WHERE a.lot_id = b.lot_id
AND nvl(b.additional_status_lot,0) = 1
AND (
(a.cost_type_id IS NOT NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.organization_id IS NOT NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NOT NULL AND a.item_id IS NOT NULL)
OR (a.lot_number IS NOT NULL AND a.lot_id IS NOT NULL)
)
AND NOT EXISTS (
SELECT 'RECORD_ALREADY_EXISTS'
FROM gmf_lot_costs x
WHERE x.organization_id = a.organization_id
AND x.inventory_item_id = a.inventory_item_id
AND x.cost_type_id = a.cost_type_id
AND x.lot_number = b.lot_number
AND x.cost_date = a.cost_date
)
);
* Insert rows for Additional Lots Created as part of Lot Balances Migration *
****************************************************************************/
INSERT INTO gmf_lot_cost_details
(
header_id,
detail_id,
cost_cmpntcls_id,
cost_analysis_code,
cost_level,
component_cost,
burden_ind,
cost_origin,
frozen_ind,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
text_code,
delete_mark,
final_cost_flag
)
(
SELECT b.header_id,
gmf_cost_detail_id_s.NEXTVAL,
a.cost_cmpntcls_id,
a.cost_analysis_code,
a.cost_level,
a.component_cost,
a.burden_ind,
a.cost_origin,
a.frozen_ind,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.attribute16,
a.attribute17,
a.attribute18,
a.attribute19,
a.attribute20,
a.attribute21,
a.attribute22,
a.attribute23,
a.attribute24,
a.attribute25,
a.attribute26,
a.attribute27,
a.attribute28,
a.attribute29,
a.attribute30,
a.attribute_category,
SYSDATE,
a.created_by,
SYSDATE,
a.last_updated_by,
a.last_update_login,
a.detail_id,
a.delete_mark,
a.final_cost_flag
FROM gmf_lot_cost_details a,
gmf_lot_costs b
WHERE a.header_id = b.text_code
AND b.text_code IS NOT NULL
AND (
(b.cost_type_id IS NOT NULL AND b.cost_mthd_code IS NULL)
OR (b.organization_id IS NOT NULL AND b.whse_code IS NULL)
OR (b.inventory_item_id IS NOT NULL AND b.item_id IS NULL)
OR (b.lot_number IS NOT NULL AND b.lot_id IS NULL)
)
AND NOT EXISTS (
SELECT 'RECORD_ALREADY_EXISTS'
FROM gmf_lot_cost_details x
WHERE b.header_id = x.header_id
)
);
* Update a row for cost Types, LE, Organization Id and Item Id *
***************************************************************/
UPDATE gmf_lot_costed_items a
SET a.cost_type_id
= (
SELECT x.cost_Type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.cost_mthd_code
),
a.legal_entity_id
= (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
)
WHERE (a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
* Update a row for Master_Organization Id and Item Id *
***************************************************************/
UPDATE gmf_lot_costed_items a
SET (
a.master_organization_id,
a.inventory_item_id
)
=
(
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = a.legal_entity_id
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.master_organization_id IS NULL AND a.item_id IS NOT NULL);
* Update a row in GMF_LOT_COST_ADJUSTMENTS for cost Types *
**********************************************************/
UPDATE gmf_lot_cost_adjustments a
SET a.cost_type_id
= (
SELECT x.cost_Type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.cost_mthd_code
),
a.legal_entity_id
= (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
),
(
a.organization_id,
a.inventory_item_id
)
= (
SELECT decode(x.cost_organization_id, -1, -1, y.organization_id),
y.inventory_item_id
FROM ic_whse_mst x,
ic_item_mst_b_mig y
WHERE x.whse_code = a.whse_code
AND y.item_id = a.item_id
AND y.organization_id = NVL(DECODE(x.cost_organization_id, -1, x.mtl_organization_id, x.cost_organization_id), x.mtl_organization_id)
)
WHERE (
(a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.organization_id IS NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL)
);
* Insert rows for Additional Lots Created as part of Lot Balances Migration *
****************************************************************************/
INSERT INTO gmf_lot_cost_adjustments
(
adjustment_id,
adjustment_date,
reason_code,
applied_ind,
gl_posted_ind,
delete_mark,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
onhand_qty,
cost_type_id,
inventory_item_id,
legal_entity_id,
lot_number,
organization_id
)
(
SELECT gmf_lot_cost_adjs_id_s.NEXTVAL,
a.adjustment_date,
a.reason_code,
a.applied_ind,
a.gl_posted_ind,
a.delete_mark,
a.adjustment_id,
a.created_by,
SYSDATE,
a.last_updated_by,
a.last_update_login,
SYSDATE,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.attribute16,
a.attribute17,
a.attribute18,
a.attribute19,
a.attribute20,
a.attribute21,
a.attribute22,
a.attribute23,
a.attribute24,
a.attribute25,
a.attribute26,
a.attribute27,
a.attribute28,
a.attribute29,
a.attribute30,
a.attribute_category,
a.onhand_qty,
a.cost_type_id,
a.inventory_item_id,
a.legal_entity_id,
b.lot_number,
a.organization_id
FROM gmf_lot_cost_adjustments a,
ic_lots_mst_mig b
WHERE a.lot_id = b.lot_id
AND nvl(b.additional_status_lot,0) = 1
AND (
(a.cost_type_id IS NOT NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.organization_id IS NOT NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NOT NULL AND a.item_id IS NOT NULL)
OR (a.legal_entity_id IS NOT NULL AND a.co_code IS NOT NULL)
)
AND NOT EXISTS (
SELECT 'RECORD_ALREADY_EXISTS'
FROM gmf_lot_cost_adjustments x
WHERE x.legal_entity_id = a.legal_entity_id
AND x.organization_id = a.organization_id
AND x.inventory_item_id = a.inventory_item_id
AND x.cost_type_id = a.cost_type_id
AND x.lot_number = b.lot_number
AND x.adjustment_date = a.adjustment_date
)
);
* Insert rows for Additional Lots Created as part of Lot Balances Migration *
****************************************************************************/
INSERT INTO gmf_lot_cost_adjustment_dtls
(
adjustment_dtl_id,
adjustment_id,
cost_cmpntcls_id,
cost_analysis_code,
adjustment_cost,
delete_mark,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date
)
(
SELECT gmf_lot_cost_adjs_dtl_id_s.NEXTVAL,
b.adjustment_id,
a.cost_cmpntcls_id,
a.cost_analysis_code,
a.adjustment_cost,
a.delete_mark,
a.adjustment_dtl_id,
a.created_by,
SYSDATE,
a.last_updated_by,
a.last_update_login,
SYSDATE
FROM gmf_lot_cost_adjustment_dtls a,
gmf_lot_cost_adjustments b
WHERE a.adjustment_id = b.text_code
AND b.text_code IS NOT NULL
AND (
(b.cost_type_id IS NOT NULL AND b.cost_mthd_code IS NULL)
OR (b.organization_id IS NOT NULL AND b.whse_code IS NULL)
OR (b.inventory_item_id IS NOT NULL AND b.item_id IS NULL)
OR (b.legal_entity_id IS NOT NULL AND b.co_code IS NULL)
)
AND NOT EXISTS (
SELECT 'RECORD_ALREADY_EXISTS'
FROM gmf_lot_cost_adjustment_dtls x
WHERE b.adjustment_id = x.adjustment_id
)
);
* Update a row in GMF_MATERIAL_LOT_COST_TXNS for cost Types *
**********************************************************/
UPDATE gmf_material_lot_cost_txns a
SET a.cost_type_id = (
SELECT x.cost_Type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.cost_type_code
),
a.cost_trans_um = (
SELECT x.uom_Code
FROM sy_uoms_mst x
WHERE x.um_code = a.cost_trans_uom
)
WHERE (
(a.cost_type_id IS NULL AND a.cost_type_code IS NOT NULL)
OR (a.cost_trans_um IS NULL AND a.cost_trans_uom IS NOT NULL)
);
* Update a row in GMF_LOT_COST_BURDENS for cost Types *
******************************************************/
UPDATE gmf_lot_cost_burdens a
SET a.cost_type_id
= (
SELECT x.cost_Type_id
FROM cm_mthd_mst x
WHERE x.cost_mthd_code = a.cost_mthd_code
),
a.item_uom
= (
SELECT x.uom_code
FROM sy_uoms_mst x
WHERE x.um_code = a.item_um
),
a.resource_uom
= (
SELECT y.uom_code
FROM sy_uoms_mst y
WHERE y.um_code = a.resource_um
),
(
a.organization_id,
a.inventory_item_id
)
= (
SELECT decode(x.cost_organization_id, -1, -1, y.organization_id),
y.inventory_item_id
FROM ic_whse_mst x,
ic_item_mst_b_mig y
WHERE x.whse_code = a.whse_code
AND y.item_id = a.item_id
AND y.organization_id = NVL(DECODE(x.cost_organization_id, -1, x.mtl_organization_id, x.cost_organization_id), x.mtl_organization_id)
)
WHERE (
(a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.item_uom IS NULL AND a.item_um IS NOT NULL)
OR (a.resource_uom IS NULL AND a.resource_um IS NOT NULL)
OR (a.organization_id IS NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
);
* Insert rows for Additional Lots Created as part of Lot Balances Migration *
****************************************************************************/
INSERT INTO gmf_lot_cost_burdens
(
lot_burden_line_id,
resources,
cost_cmpntcls_id,
cost_analysis_code,
start_date,
end_date,
resource_usage,
resource_count,
item_qty,
burden_factor,
applied_ind,
delete_mark,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
cost_type_id,
inventory_item_id,
item_uom,
lot_number,
organization_id,
resource_uom
)
(
SELECT GMF_LOT_BURDEN_LINE_ID_S.NEXTVAL,
a.resources,
a.cost_cmpntcls_id,
a.cost_analysis_code,
a.start_date,
a.end_date,
a.resource_usage,
a.resource_count,
a.item_qty,
a.burden_factor,
a.applied_ind,
a.delete_mark,
a.lot_burden_line_id,
a.created_by,
SYSDATE,
a.last_updated_by,
SYSDATE,
a.last_update_login,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.attribute16,
a.attribute17,
a.attribute18,
a.attribute19,
a.attribute20,
a.attribute21,
a.attribute22,
a.attribute23,
a.attribute24,
a.attribute25,
a.attribute26,
a.attribute27,
a.attribute28,
a.attribute29,
a.attribute30,
a.attribute_category,
a.cost_type_id,
a.inventory_item_id,
a.item_uom,
b.lot_number,
a.organization_id,
a.resource_uom
FROM gmf_lot_cost_burdens a,
ic_lots_mst_mig b
WHERE a.lot_id = b.lot_id
AND nvl(b.additional_status_lot,0) = 1
AND (
(a.cost_type_id IS NOT NULL AND a.cost_mthd_code IS NOT NULL)
OR (a.item_uom IS NOT NULL AND a.item_um IS NOT NULL)
OR (a.resource_uom IS NOT NULL AND a.resource_um IS NOT NULL)
OR (a.organization_id IS NOT NULL AND a.whse_code IS NOT NULL)
OR (a.inventory_item_id IS NOT NULL AND a.item_id IS NOT NULL)
)
AND NOT EXISTS (
SELECT 'RECORD_ALREADY_EXISTS'
FROM gmf_lot_cost_burdens x
WHERE x.organization_id = a.organization_id
AND x.inventory_item_id = a.inventory_item_id
AND x.cost_type_id = a.cost_type_id
AND x.lot_number = b.lot_number
AND x.resources = a.resources
AND x.cost_cmpntcls_id = a.cost_cmpntcls_id
AND x.cost_analysis_code = a.cost_analysis_code
)
);
SELECT DISTINCT
item_id,
organization_id
FROM (
SELECT a.item_id,
DECODE(NVL(b.subinventory_ind_flag,'N'), 'Y', b.organization_id, b.mtl_organization_id) organization_id
FROM gl_aloc_bas a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND b.whse_code = a.whse_code
);
* Update a row in GL_ALOC_BAS for Account Codes *
**********************************************************/
BEGIN
UPDATE gl_aloc_bas a
SET a.basis_account_id
= (
SELECT gmf_migration.get_account_id(a.basis_Account_key, x.co_code)
FROM gl_aloc_mst x
WHERE x.alloc_id = a.alloc_id
),
a.basis_type = decode(a.alloc_method, 0, a.basis_type, 1),
(
a.organization_id,
a.inventory_item_id
)
= (
SELECT y.organization_id,
y.inventory_item_id
FROM ic_whse_mst x,
ic_item_mst_b_mig y
WHERE x.whse_code = a.whse_code
AND y.item_id = a.item_id
AND y.organization_id = DECODE(NVL(x.subinventory_ind_flag, 'N'), 'Y', x.organization_id, x.mtl_organization_id)
)
WHERE (
(a.basis_account_key IS NOT NULL AND a.basis_account_id IS NULL)
OR (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.organization_id IS NULL AND a.whse_code IS NOT NULL)
);
SELECT count(*)
INTO x_failure_count
FROM gl_aloc_bas
WHERE (
(basis_account_key IS NOT NULL AND basis_account_id IS NULL)
OR (inventory_item_id IS NULL AND item_id IS NOT NULL)
OR (organization_id IS NULL AND whse_code IS NOT NULL)
);
* Update a row in GL_ALOC_EXP for Account Codes *
**********************************************************/
BEGIN
UPDATE gl_aloc_exp a
SET (
a.from_account_id,
a.to_account_id
)
= (
SELECT gmf_migration.get_account_id(a.from_account, x.co_code),
gmf_migration.get_account_id(a.to_account, x.co_code)
FROM gl_aloc_mst x
WHERE x.alloc_id = a.alloc_id
)
WHERE (
(from_account_id IS NULL AND from_account IS NOT NULL)
OR (to_account_id IS NULL AND to_account IS NOT NULL)
);
SELECT count(*)
INTO x_failure_count
FROM gl_aloc_exp
WHERE (
(from_account_id IS NULL AND from_account IS NOT NULL)
OR (to_account_id IS NULL AND to_account IS NOT NULL)
);
SELECT a.legal_entity_id,
d.organization_code,
c.legal_entity_name organization_name,
b.segment_delimiter,
b.co_code,
e.chart_of_accounts_id,
e.short_name,
e.name,
e.ledger_id
FROM gmf_fiscal_policies a,
gl_plcy_mst b,
gmf_legal_entities c,
mtl_parameters d,
gl_ledgers e
WHERE a.legal_entity_id = b.legal_entity_id
AND c.legal_entity_id = a.legal_entity_id
AND d.organization_id(+) = c.legal_entity_id
AND e.ledger_id = a.ledger_id
ORDER BY a.legal_entity_id;
SELECT DISTINCT
DECODE(acct_ttl_code, 'PCO', 'COGS', 'IPF', 'IOPR', 'XFC', 'XTC', acct_ttl_code) acct_ttl_code,
DECODE(acct_ttl_code, 'PCO', 'Cost of Goods Sold' , 'IPF', 'Inter-Org Profit', 'XFC', 'Inter-org Transfer Credit', acct_ttl_desc) acct_ttl_desc,
acct_ttl_type
FROM gl_acct_ttl
ORDER BY acct_ttl_code;
SELECT a.segment_no,
a.type,
b.segment_name short_name,
b.application_column_name,
c.id_flex_structure_code structure_code,
c.id_flex_structure_name structure_name
FROM gl_plcy_seg a,
fnd_id_flex_segments b,
fnd_id_flex_structures_vl c
WHERE a.co_code = p_co_code
AND b.segment_num = a.segment_ref
AND b.id_flex_num = p_coa_id
AND b.enabled_flag = 'Y'
AND b.id_flex_code = 'GL#'
AND b.application_id = 101
AND c.application_id = b.application_id
AND c.id_flex_code = b.id_flex_code
AND c.id_flex_num = b.id_flex_num
ORDER BY a.type,
a.segment_no;
SELECT a.co_code,
a.orgn_code,
NVL(c.inventory_org_ind,'N') inventory_org_ind,
c.organization_id,
a.whse_code,
NVL(d.subinventory_ind_flag,'N') subinventory_ind_flag,
d.mtl_organization_id,
b.acctg_unit_id,
b.acctg_unit_no,
a.accu_map_id
FROM gl_accu_map a,
gl_accu_mst b,
sy_orgn_mst c,
ic_whse_mst d
WHERE a.co_code = p_co_code
AND b.acctg_unit_id = a.acctg_unit_id
AND c.orgn_code(+) = a.orgn_code
AND d.whse_code(+) = a.whse_code
AND nvl(a.migrated_ind,0) <> 1
ORDER BY a.co_code,
a.orgn_code NULLS LAST,
a.whse_code NULLS LAST;
SELECT a.whse_code,
a.whse_name,
NVL(a.subinventory_ind_flag,'N') subinventory_ind_flag,
a.mtl_organization_id
FROM ic_whse_mst a
WHERE a.orgn_code = p_orgn_code
AND NOT EXISTS
(
SELECT 'X'
FROM gl_accu_map x
WHERE x.whse_code = a.whse_code
AND x.co_code = p_co_code
AND x.orgn_code = a.orgn_code
AND x.acctg_unit_id = p_acctg_unit_id
)
ORDER BY a.whse_code;
SELECT a.whse_code,
a.whse_name,
NVL(a.subinventory_ind_flag,'N') subinventory_ind_flag,
a.mtl_organization_id
FROM ic_whse_mst a
WHERE a.orgn_code = p_orgn_code
AND NOT EXISTS
(
SELECT 'X'
FROM gl_acct_map x
WHERE x.whse_code = a.whse_code
AND x.co_code = p_co_code
AND x.orgn_code = a.orgn_code
AND x.acct_id = p_acct_id
)
ORDER BY a.whse_code;
SELECT DISTINCT c.sub_event_code,
d.event_class_code,
d.event_Type_code
FROM gl_sevt_ttl a,
gl_sevt_mst c,
gmf_xla_event_model d
WHERE a.acct_ttl_type = p_acct_ttl_type
AND c.sub_event_type = a.sub_event_type
AND c.sub_event_type = d.sub_event_type
AND 1 = 2; /* Stubbed to avoid migration for Line Assignments */
PROCEDURE insert_conditions
(
p_condition_tag IN VARCHAR2,
p_sequence IN OUT NOCOPY NUMBER,
p_source IN VARCHAR2,
p_comparision_operator IN VARCHAR2,
p_value_type IN VARCHAR2,
p_value IN VARCHAR2,
p_logical_operator IN VARCHAR2,
p_segment_rule_detail_id IN NUMBER
)
IS
l_logical_operator_code VARCHAR2(1);
INSERT INTO xla_conditions_t
(
CONDITION_ID,
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
BRACKET_LEFT_CODE,
BRACKET_RIGHT_CODE,
VALUE_TYPE_CODE,
SOURCE_APPLICATION_ID,
SOURCE_TYPE_CODE,
SOURCE_CODE,
FLEXFIELD_SEGMENT_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
LINE_OPERATOR_CODE,
LOGICAL_OPERATOR_CODE,
INDEPENDENT_VALUE_CONSTANT,
ERROR_VALUE
)
VALUES
(
xla_conditions_s.NEXTVAL,
G_Application_id,
l_amb_context,
p_segment_rule_detail_id,
p_sequence,
NULL,
NULL,
p_value_type,
G_Application_id,
'S',
p_source,
NULL,
NULL,
NULL,
NULL,
NULL,
p_value,
p_comparision_operator,
l_logical_operator_code,
NULL,
0
);
END insert_conditions;
SELECT count(1)
INTO l_acctg_unit_count
FROM gl_plcy_seg a
WHERE TYPE = 0
AND a.co_code = i.co_code;
SELECT count(*)
INTO xrt
FROM xla_rules_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rules_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
TRANSACTION_COA_ID,
ACCOUNTING_COA_ID,
FLEXFIELD_ASSIGN_MODE_CODE,
FLEXFIELD_SEGMENT_CODE,
ENABLED_FLAG,
NAME,
DESCRIPTION,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
i.chart_of_accounts_id,
i.chart_of_accounts_id,
'S',
j.application_column_name,
'Y',
l_adr_rule_name,
'ADR for Ledger: '|| i.name ||' - JLT: ('|| k.acct_ttl_code ||') '||SUBSTRB(k.acct_ttl_desc, 1, 25)||' - Segment: '||j.short_name,
0
);
SELECT nvl(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
SELECT count(*)
INTO xlat
FROM xla_line_assgns_t
WHERE application_id = G_Application_id
AND amb_context_code = l_amb_context
AND event_class_code = m.event_class_code
AND event_type_code = m.event_type_code
AND line_definition_code = m.event_type_code
AND accounting_line_code = k.acct_ttl_code
AND segment_rule_code = l_adr_rule_code
AND flexfield_segment_code = j.application_column_name;
INSERT INTO xla_line_assgns_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
ACCOUNTING_LINE_TYPE_CODE,
ACCOUNTING_LINE_CODE,
FLEXFIELD_SEGMENT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
m.event_class_code,
m.event_type_code,
'C',
m.event_type_code,
'S',
k.acct_ttl_code,
j.application_column_name,
'C',
l_adr_rule_code,
0
);
SELECT substrb(a, decode(b-1, 0, 1, instr(a, c, 1, (b-1))+ 1), decode(instr(a, c, 1, b), 0, (length(a) - instr(a, c, 1, (b-1))+ 1), (instr(a, c, 1, b)) - decode(b-1, 0, 1, instr(a, c, 1, (b-1))+ 1)))
INTO l_segment_value
FROM (
SELECT l.acctg_unit_no a,
j.segment_no b,
i.segment_delimiter c
FROM dual
);
SELECT nvl(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
insert_conditions (
p_condition_tag => 'FIRST',
p_sequence => l_adr_condition_priority,
p_source => G_Journal_Line_Type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => k.acct_ttl_code,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_ledger_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.ledger_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_legal_entity,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.legal_entity_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.organization_id,
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_subinventory,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.whse_code,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.mtl_organization_id,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
* Warehouse Code Is not specified, so inserting a record for all warehouses under the OPM organization *
*******************************************************************************************************/
mcnt := 1;
SELECT nvl(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
insert_conditions (
p_condition_tag => 'FIRST',
p_sequence => l_adr_condition_priority,
p_source => G_Journal_Line_Type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => k.acct_ttl_code,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_ledger_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.ledger_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_legal_entity,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.legal_entity_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.organization_id,
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_subinventory,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => m.whse_code,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => m.mtl_organization_id,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
SELECT nvl(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
insert_conditions (
p_condition_tag => 'FIRST',
p_sequence => l_adr_condition_priority,
p_source => G_Journal_Line_Type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => k.acct_ttl_code,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_ledger_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.ledger_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_legal_entity,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.legal_entity_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.organization_id,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
UPDATE xla_conditions_t
SET logical_operator_code = NULL
WHERE user_sequence = l_adr_condition_priority
AND segment_rule_detail_id = l_segment_rule_detail_id
AND amb_context_code = l_amb_context;
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l.organization_id,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
UPDATE xla_conditions_t
SET logical_operator_code = NULL
WHERE user_sequence = l_adr_condition_priority
AND segment_rule_detail_id = l_segment_rule_detail_id
AND amb_context_code = l_amb_context;
SELECT count(*)
INTO xrt
FROM xla_rules_t
WHERE application_id = G_Application_id
AND SEGMENT_RULE_CODE = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rules_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
TRANSACTION_COA_ID,
ACCOUNTING_COA_ID,
FLEXFIELD_ASSIGN_MODE_CODE,
FLEXFIELD_SEGMENT_CODE,
ENABLED_FLAG,
NAME,
DESCRIPTION,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
i.chart_of_accounts_id,
i.chart_of_accounts_id,
'S',
j.application_column_name,
'Y',
l_adr_rule_name,
'ADR for Ledger: '|| i.name ||' - JLT: ('|| k.acct_ttl_code ||') '||SUBSTRB(k.acct_ttl_desc, 1, 25)||' - Segment: '||j.short_name,
0
);
SELECT nvl(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
SELECT count(*)
INTO xlat
FROM xla_line_assgns_t
WHERE application_id = G_Application_id
AND amb_context_code = l_amb_context
AND event_class_code = m.event_class_code
AND event_type_code = m.event_type_code
AND line_definition_code = m.event_type_code
AND accounting_line_code = k.acct_ttl_code
AND segment_rule_code = l_adr_rule_code
AND flexfield_segment_code = j.application_column_name;
INSERT INTO xla_line_assgns_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
ACCOUNTING_LINE_TYPE_CODE,
ACCOUNTING_LINE_CODE,
FLEXFIELD_SEGMENT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
m.event_class_code,
m.event_type_code,
'C',
m.event_type_code,
'S',
k.acct_ttl_code,
j.application_column_name,
'C',
l_adr_rule_code,
0
);
X_sqlstmt := 'SELECT a.co_code,
a.orgn_code_pri,
a.whse_code_pri,
a.icgl_class_pri,
a.custgl_class_pri,
a.vendgl_class_pri ,
a.item_pri,
a.customer_pri,
a.vendor_pri,
a.tax_auth_pri,
a.charge_pri,
a.currency_code_pri,
a.reason_code_pri,
a.routing_pri,
a.aqui_cost_code_pri,
a.resource_pri,
a.cost_cmpntcls_pri,
a.cost_analysis_pri,
a.order_type_pri,
a.gl_business_class_pri,
a.gl_product_line_pri,
a.line_type_pri,
a.ar_trx_type_pri
FROM gl_acct_hrc a
WHERE a.acct_ttl_type = :p_acct_ttl_type
AND a.co_code = :p_co_code
ORDER BY 1 desc';
X_sqlcolumns:= ' SELECT a.co_code,
a.orgn_code,
a.whse_code,
a.gl_category_id,
a.custgl_class,
a.vendgl_class,
a.item_id,
a.cust_id,
a.vendor_id,
a.taxauth_id,
a.charge_id,
a.currency_code,
a.reason_code,
a.routing_id,
a.aqui_cost_id,
a.resources,
a.cost_cmpntcls_id,
a.cost_analysis_code,
a.order_type,
a.gl_business_class_cat_id,
a.gl_product_line_cat_id,
a.line_type,
a.ar_trx_type_id,
b.acct_id,
b.acct_no,
NVL(c.inventory_org_ind,''N'') inventory_org_ind,
NVL(d.subinventory_ind_flag,''N'') subinventory_ind_flag,
a.ROWID,
a.source_type,
c.organization_id,
d.mtl_organization_id ';
SELECT substrb(a, decode(b-1, 0, 1, instr(a, c, 1, (b-1))+ 1), decode(instr(a, c, 1, b), 0, (length(a) - instr(a, c, 1, (b-1))+ 1), (instr(a, c, 1, b)) - decode(b-1, 0, 1, instr(a, c, 1, (b-1))+ 1)))
INTO l_segment_value
FROM (
SELECT l_acct_no(m) a,
(j.segment_no - l_acctg_unit_count) b,
i.segment_delimiter c
FROM dual
);
SELECT NVL(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
insert_conditions (
p_condition_tag => 'FIRST',
p_sequence => l_adr_condition_priority,
p_source => G_Journal_Line_Type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => k.acct_ttl_code,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_ledger_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.ledger_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_legal_entity,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => i.legal_entity_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_gl_category_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_gl_category_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_custgl_class,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_custgl_class(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_vendgl_class,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_vendgl_class(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_Inventory_item_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_inventory_item_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_customer,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_cust_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_vendor,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_vendor_site_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_currency_code,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_currency_code(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_reason_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_reason_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_routing_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_routing_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_price_element_type_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_price_element_type_id,
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_resources,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_resources(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_cost_cmpntcls_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_cost_cmpntcls_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_cost_analysis_code,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_cost_analysis_code(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_order_type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_order_type(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_gl_business_class_cat_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_gl_business_class_cat_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_gl_product_line_cat_id,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_gl_product_line_cat_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_line_type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_line_type(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_ar_trx_type,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_ar_trx_type_id(m),
p_logical_operator => G_And,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_organization_id(m),
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_subinventory,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_whse_code(m),
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_mtl_organization_id(m),
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
SELECT NVL(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
INSERT INTO xla_conditions_t
(
CONDITION_ID,
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
BRACKET_LEFT_CODE,
BRACKET_RIGHT_CODE,
VALUE_TYPE_CODE,
SOURCE_APPLICATION_ID,
SOURCE_TYPE_CODE,
SOURCE_CODE,
FLEXFIELD_SEGMENT_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
LINE_OPERATOR_CODE,
LOGICAL_OPERATOR_CODE,
INDEPENDENT_VALUE_CONSTANT,
ERROR_VALUE
)
(
SELECT xla_conditions_s.NEXTVAL,
APPLICATION_ID,
AMB_CONTEXT_CODE,
l_segment_rule_detail_id,
USER_SEQUENCE,
BRACKET_LEFT_CODE,
BRACKET_RIGHT_CODE,
VALUE_TYPE_CODE,
SOURCE_APPLICATION_ID,
SOURCE_TYPE_CODE,
SOURCE_CODE,
FLEXFIELD_SEGMENT_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
LINE_OPERATOR_CODE,
LOGICAL_OPERATOR_CODE,
INDEPENDENT_VALUE_CONSTANT,
ERROR_VALUE
FROM xla_conditions_t
WHERE segment_rule_detail_id = l_old_segment_rule_detail_id
AND amb_context_code = l_amb_context
AND user_sequence <= l_old_adr_condition_priority
);
insert_conditions (
p_condition_tag => 'MID',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_organization_id(m),
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_subinventory,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => n.whse_code,
p_logical_operator => NULL,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => n.mtl_organization_id,
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
SELECT NVL(MAX(nvl(user_sequence,0)) + 10,10)
INTO l_adr_priority
FROM xla_rule_details_t
WHERE application_id = G_Application_id
AND segment_rule_code = l_adr_rule_code
AND amb_context_code = l_amb_context;
INSERT INTO xla_rule_details_t
(
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_TYPE_CODE,
SEGMENT_RULE_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
VALUE_TYPE_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
VALUE_CODE_COMBINATION_ID,
VALUE_MAPPING_SET_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
INPUT_SOURCE_APPLICATION_ID,
INPUT_SOURCE_TYPE_CODE,
INPUT_SOURCE_CODE,
VALUE_SEGMENT_RULE_APPL_ID,
VALUE_SEGMENT_RULE_TYPE_CODE,
VALUE_SEGMENT_RULE_CODE,
VALUE_ADR_VERSION_NUM,
ERROR_VALUE
)
VALUES
(
G_Application_id,
l_amb_context,
'C',
l_adr_rule_code,
xla_seg_rule_details_s.NEXTVAL,
l_adr_priority,
'C',
NULL,
NULL,
NULL,
l_segment_value,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
) returning segment_rule_detail_id INTO l_segment_rule_detail_id;
INSERT INTO xla_conditions_t
(
CONDITION_ID,
APPLICATION_ID,
AMB_CONTEXT_CODE,
SEGMENT_RULE_DETAIL_ID,
USER_SEQUENCE,
BRACKET_LEFT_CODE,
BRACKET_RIGHT_CODE,
VALUE_TYPE_CODE,
SOURCE_APPLICATION_ID,
SOURCE_TYPE_CODE,
SOURCE_CODE,
FLEXFIELD_SEGMENT_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
LINE_OPERATOR_CODE,
LOGICAL_OPERATOR_CODE,
INDEPENDENT_VALUE_CONSTANT,
ERROR_VALUE
)
(
SELECT xla_conditions_s.NEXTVAL,
APPLICATION_ID,
AMB_CONTEXT_CODE,
l_segment_rule_detail_id,
USER_SEQUENCE,
BRACKET_LEFT_CODE,
BRACKET_RIGHT_CODE,
VALUE_TYPE_CODE,
SOURCE_APPLICATION_ID,
SOURCE_TYPE_CODE,
SOURCE_CODE,
FLEXFIELD_SEGMENT_CODE,
VALUE_FLEXFIELD_SEGMENT_CODE,
VALUE_SOURCE_APPLICATION_ID,
VALUE_SOURCE_TYPE_CODE,
VALUE_SOURCE_CODE,
VALUE_CONSTANT,
LINE_OPERATOR_CODE,
LOGICAL_OPERATOR_CODE,
INDEPENDENT_VALUE_CONSTANT,
ERROR_VALUE
FROM xla_conditions_t
WHERE segment_rule_detail_id = l_old_segment_rule_detail_id
AND amb_context_code = l_amb_context
AND user_sequence <= l_old_adr_condition_priority
);
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_organization_id(m),
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
UPDATE xla_conditions_t
SET logical_operator_code = NULL
WHERE user_sequence = l_adr_condition_priority
AND segment_rule_detail_id = l_segment_rule_detail_id
AND amb_context_code = l_amb_context;
insert_conditions (
p_condition_tag => 'LAST',
p_sequence => l_adr_condition_priority,
p_source => G_Organization,
p_comparision_operator => G_Equal,
p_value_type => G_constant,
p_value => l_organization_id(m),
p_logical_operator => G_and,
p_segment_rule_detail_id => l_segment_rule_detail_id
);
UPDATE xla_conditions_t
SET logical_operator_code = NULL
WHERE user_sequence = l_adr_condition_priority
AND segment_rule_detail_id = l_segment_rule_detail_id
AND amb_context_code = l_amb_context;
UPDATE gl_acct_map
SET migrated_ind = 1
WHERE co_code = i.co_code
AND acct_ttl_type = k.acct_ttl_type;
UPDATE GL_ACCU_MAP
SET MIGRATED_IND = 1;
l_insert_update_flag VARCHAR2(10);
SELECT ROWID,
aqui_cost_code,
aqui_cost_desc,
cmpntcls_id,
analysis_code,
incl_ind
BULK COLLECT INTO l_rowid,
l_aqui_cost_code,
l_aqui_cost_desc,
l_cmpntcls_id,
l_analysis_code,
l_incl_ind
FROM po_cost_mst
WHERE price_element_type_id IS NULL;
PON_CF_TYPE_GRP.opm_create_update_cost_factor
(
p_api_version => 1.0
, p_price_element_code => l_aqui_cost_code(i)
, p_pricing_basis => 'PER_UNIT'
, p_cost_component_class_id => l_cmpntcls_id(i)
, p_cost_analysis_code => l_analysis_code(i)
, p_cost_acquisition_code => l_cost_acquisition_code
, p_name => l_aqui_cost_code(i)
, p_description => l_aqui_cost_desc(i)
, x_insert_update_action => l_insert_update_flag
, x_price_element_type_id => l_price_element_type_id(i)
, x_pricing_basis => l_pricing_basis
, x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
);
UPDATE po_cost_mst
SET migrated_ind = 1,
price_element_type_id = l_price_element_type_id(j)
WHERE ROWID = l_rowid(j)
AND price_element_type_id IS NULL;
SELECT count(*)
INTO x_failure_count
FROM po_cost_mst
WHERE price_element_type_id IS NULL;
SELECT DISTINCT a.orgn_code,
e.whse_code,
NVL(e.subinventory_ind_flag,'N') subinventory_ind_flag,
DECODE(NVL(e.subinventory_ind_flag,'N'), 'Y', e.organization_id, e.mtl_organization_id) organization_id,
d.acct_period_id,
d.period_start_date,
d.schedule_close_date,
b.period_id curr_period_id,
b.period_end_date curr_period_end_date,
c.period_id prior_period_id,
c.period_end_date prior_period_end_date,
c.closed_period_ind prior_period_closed_ind
FROM sy_orgn_mst a,
ic_cldr_dtl b,
ic_cldr_dtl c,
org_acct_periods d,
hr_organization_information hoi,
ic_whse_mst e,
gl_ledgers f
WHERE a.orgn_code = b.orgn_code
AND c.orgn_code = a.orgn_code
AND e.orgn_code = a.orgn_code
AND d.organization_id = e.cost_organization_id
AND hoi.organization_id = d.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = f.ledger_id
AND f.period_set_name = d.period_Set_name
AND c.period_end_date = d.schedule_close_date
AND nvl(c.closed_period_ind, 1) = 3
AND b.period_end_date = (
SELECT MIN(x.period_end_date)
FROM ic_cldr_dtl x
WHERE a.orgn_code = x.orgn_code
AND SYSDATE < x.period_end_date
)
AND c.period_end_date = (
SELECT MAX(y.period_end_date)
FROM ic_cldr_dtl y
WHERE a.orgn_code = y.orgn_code
AND SYSDATE > y.period_end_Date
);
SELECT count(1)
INTO l_perd_bal_count
FROM ic_perd_bal
WHERE whse_code = i.whse_code
AND period_id = i.prior_period_id;
INSERT INTO gmf_period_balances
(
period_balance_id,
acct_period_id,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
lot_number,
locator_id,
primary_quantity,
secondary_quantity,
intransit_primary_quantity,
intransit_secondary_quantity,
accounted_value,
intransit_accounted_value,
costed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
(
SELECT gmf_period_balances_s.NEXTVAL,
i.acct_period_id,
i.organization_id,
decode(i.subinventory_ind_flag, 'N', NULL, e.default_cost_group_id),
decode(i.subinventory_ind_flag, 'N', NULL, e.secondary_inventory_name),
b.inventory_item_id,
c.lot_number,
d.inventory_location_id,
a.loct_onhand,
a.loct_onhand2,
0,
0,
a.loct_value,
0,
NULL,
SYSDATE,
1,
SYSDATE,
1,
1,
NULL,
NULL,
NULL,
NULL
FROM ic_perd_bal a,
ic_item_mst_b_mig b,
ic_lots_mst_mig c,
ic_loct_mst d,
mtl_secondary_inventories e
WHERE a.whse_code = i.whse_code
AND a.period_id = i.prior_period_id
AND b.organization_id = i.organization_id
AND e.secondary_inventory_name(+) = i.whse_code
AND e.organization_id(+) = i.organization_id
AND b.item_id = a.item_id
AND c.item_id = a.item_id
AND c.lot_id = a.lot_id
AND c.whse_code = a.whse_code
AND c.location = a.location
AND d.whse_code = a.whse_code
AND d.location = a.location
AND NOT EXISTS (
SELECT 'X'
FROM gmf_period_balances x
WHERE x.acct_period_id = i.acct_period_id
AND x.organization_id = i.organization_id
AND x.inventory_item_id = b.inventory_item_id
AND nvl(x.subinventory_code, '~') = nvl(decode(i.subinventory_ind_flag, 'N', NULL, e.secondary_inventory_name), '~')
AND nvl(x.lot_number,'~') = nvl(c.lot_number, '~')
AND nvl(x.locator_id, -1) = nvl(d.inventory_location_id, -1)
)
);
UPDATE org_acct_periods
SET period_close_date = SYSDATE,
open_flag = 'N',
summarized_flag = 'Y'
WHERE acct_period_id = i.prior_period_id
AND organization_id = i.organization_id;
SELECT count(*)
INTO x_failure_count
FROM ic_perd_bal
WHERE whse_code = i.whse_code
AND period_id = i.prior_period_id;
* Update a row in GL_ALOC_INP for Account Codes *
**********************************************************/
BEGIN
UPDATE gl_aloc_inp a
SET a.account_id = (
SELECT gmf_migration.get_account_id(a.account_key, x.co_code)
FROM gl_aloc_mst x
WHERE x.alloc_id = a.alloc_id
)
WHERE (account_id IS NULL AND a.account_key IS NOT NULL);
SELECT count(*)
INTO x_failure_count
FROM gl_aloc_inp
WHERE (account_id IS NULL AND account_key IS NOT NULL);
* Update rows For Organization Priority *
*****************************************/
UPDATE gmf_burden_priorities a
SET a.organization_pri = nvl(a.whse_code_pri, a.orgn_code_pri),
a.legal_entity_id
= (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
)
WHERE ((a.whse_code_pri IS NOT NULL OR a.orgn_code_pri IS NOT null) AND a.organization_pri IS NULL)
OR (a.co_code IS NOT NULL AND a.legal_entity_id IS NULL);
UPDATE gmf_burden_priorities a
SET a.delete_mark = 1
WHERE a.ROWID NOT IN (
SELECT MIN(x.ROWID)
FROM gmf_burden_priorities x
WHERE x.burden_id = a.burden_id
AND x.legal_entity_id = a.legal_Entity_id
AND x.delete_mark <> 1
);
UPDATE gmf_burden_priorities
SET organization_pri = decode(trunc(nvl(organization_pri,0) / orgn_code_pri), 0, organization_pri, organization_pri - 1),
item_id_pri = decode(trunc(nvl(item_id_pri,0) / orgn_code_pri), 0, item_id_pri, item_id_pri - 1),
icgl_class_pri = decode(trunc(nvl(icgl_class_pri,0) / orgn_code_pri), 0, icgl_class_pri, icgl_class_pri - 1),
itemcost_class_pri = decode(trunc(nvl(itemcost_class_pri,0) / orgn_code_pri), 0, itemcost_class_pri, itemcost_class_pri - 1),
gl_prod_line_pri = decode(trunc(nvl(gl_prod_line_pri,0) / orgn_code_pri), 0, gl_prod_line_pri, gl_prod_line_pri - 1),
gl_business_class_pri = decode(trunc(nvl(gl_business_class_pri,0) / orgn_code_pri), 0, gl_business_class_pri, gl_business_class_pri - 1),
orgn_code_pri = NULL
WHERE orgn_code_pri IS NOT NULL
AND orgn_code_pri < 7
AND whse_code_pri IS NOT NULL;
SELECT count(*)
INTO x_failure_count
FROM gmf_burden_priorities a
WHERE ((a.whse_code_pri IS NOT NULL OR a.orgn_code_pri IS NOT null) AND a.organization_pri IS NULL)
OR (a.co_code IS NOT NULL AND a.legal_entity_id IS NULL);
SELECT DISTINCT
item_id,
organization_id
FROM (
SELECT a.item_id,
decode(NVL(c.subinventory_ind_flag,'N'), 'Y', c.organization_id, c.mtl_organization_id) organization_id
FROM cm_cmpt_mtl a,
sy_orgn_mst b,
ic_whse_mst c
WHERE a.item_id IS NOT NULL
AND a.co_code = b.co_code
AND b.orgn_code = c.orgn_code
AND nvl(c.subinventory_ind_flag, 'N') <> 'Y'
);
* Update rows For Legal Entity and Item *
*****************************************/
UPDATE cm_cmpt_mtl a
SET a.legal_entity_id
= (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
)
WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
UPDATE cm_cmpt_mtl a
SET (
a.master_organization_id,
a.inventory_item_id
)
= (
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = a.legal_entity_id
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.master_organization_id IS NULL AND a.item_id IS NOT NULL);
UPDATE cm_cmpt_mtl a
SET a.delete_mark = 1
WHERE a.ROWID NOT IN (
SELECT MIN(x.ROWID)
FROM cm_cmpt_mtl x
WHERE x.legal_entity_id = a.legal_Entity_id
AND nvl(x.inventory_item_id, -1) = nvl(a.inventory_item_id, -1)
AND nvl(x.cost_category_id, -1) = nvl(a.cost_category_id, -1)
AND x.delete_mark <> 1
AND (
a.eff_start_date BETWEEN x.eff_start_date and x.eff_end_date
OR
a.eff_end_date BETWEEN x.eff_start_date and x.eff_end_date
)
);
SELECT count(*)
INTO x_failure_count
FROM cm_cmpt_mtl
WHERE (
(inventory_item_id IS NULL AND item_id IS NOT NULL)
OR (legal_entity_id IS NULL AND co_code IS NOT NULL)
OR (master_organization_id IS NULL AND item_id IS NOT NULL)
);
SELECT y.alloc_id,
y.mina,
count(x.alloc_id) cnt
FROM gl_aloc_bas x, (
SELECT a.alloc_id,
(
SELECT MIN(h.alloc_id)
FROM gl_aloc_mst h
WHERE (h.legal_entity_id, h.alloc_code) IN (
SELECT i.legal_entity_id, i.alloc_code
FROM gl_aloc_mst i
WHERE i.alloc_id = a.alloc_id
)
) mina
FROM gl_aloc_bas a
GROUP BY a.alloc_id
) y
WHERE x.alloc_id(+) = y.mina
GROUP BY y.alloc_id,
x.alloc_id,
y.mina
HAVING y.alloc_id <> y.mina;
SELECT y.alloc_id,
y.mina,
count(x.alloc_id) cnt
FROM gl_aloc_exp x, (
SELECT a.alloc_id,
(
SELECT MIN(h.alloc_id)
FROM gl_aloc_mst h
WHERE (h.legal_entity_id, h.alloc_code) IN (
SELECT i.legal_entity_id, i.alloc_code
FROM gl_aloc_mst i
WHERE i.alloc_id = a.alloc_id
)
) mina
FROM gl_aloc_exp a
GROUP BY a.alloc_id
) y
WHERE x.alloc_id(+) = y.mina
GROUP BY y.alloc_id,
x.alloc_id,
y.mina
HAVING y.alloc_id <> y.mina;
SELECT y.alloc_id,
y.mina,
count(x.alloc_id) cnt
FROM gl_aloc_inp x, (
SELECT a.alloc_id,
(
SELECT MIN(h.alloc_id)
FROM gl_aloc_mst h
WHERE (h.legal_entity_id, h.alloc_code) IN (
SELECT i.legal_entity_id, i.alloc_code
FROM gl_aloc_mst i
WHERE i.alloc_id = a.alloc_id
)
) mina
FROM gl_aloc_inp a
GROUP BY a.alloc_id
) y
WHERE x.alloc_id(+) = y.mina
GROUP BY y.alloc_id,
x.alloc_id,
y.mina
HAVING y.alloc_id <> y.mina;
* Update rows For Legal Entity *
*****************************************/
UPDATE gl_aloc_mst a
SET a.legal_entity_id = (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
)
WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
* are merged together to form the legal entities allocation records. so we delete the duplicate records *
* from the allocation tables. Since there are some references too the allocation codes in Allocation basis *
* we have to delete the records from those tables as well. *
**************************************************************************************************************/
UPDATE gl_aloc_mst a
SET a.delete_mark = 1
WHERE a.ROWID NOT IN (
SELECT MIN(x.ROWID)
FROM gl_aloc_mst x
WHERE x.alloc_code = a.alloc_code
AND x.legal_entity_id = a.legal_Entity_id
AND x.delete_mark <> 1
);
UPDATE gl_aloc_bas a
SET a.delete_mark = 1
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
UPDATE gl_aloc_bas a
SET a.alloc_id = i.mina
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
UPDATE gl_aloc_exp a
SET a.delete_mark = 1
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
UPDATE gl_aloc_exp a
SET a.alloc_id = i.mina
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
UPDATE gl_aloc_inp a
SET a.delete_mark = 1
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
UPDATE gl_aloc_inp a
SET a.alloc_id = i.mina
WHERE a.alloc_id = i.alloc_id
AND a.delete_mark <> 1;
SELECT count(*)
INTO x_failure_count
FROM gl_aloc_mst
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL);
* Update rows For Legal Entity *
*****************************************/
UPDATE gl_evnt_plc a
SET a.legal_entity_id = (
SELECT x.legal_entity_id
FROM gl_plcy_mst x
WHERE x.co_code = a.co_code
),
a.entity_code = decode(a.trans_source_type, 12, 'PURCHASING', NULL),
a.event_class_code = decode(a.event_type, 110, 'DELIVER', NULL)
WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
* are merged together to form the LE Event Fiscal Policy records.so we delete the duplicate records *
* from the Event Fiscal Policy tables. *
**************************************************************************************************************/
UPDATE gl_evnt_plc a
SET a.delete_mark = 1
WHERE a.ROWID NOT IN (
SELECT MIN(x.ROWID)
FROM gl_evnt_plc x
WHERE x.legal_entity_id = a.legal_Entity_id
AND nvl(x.trans_source_type, -1) = nvl(a.trans_source_type, -1)
AND nvl(x.event_type, -1) = nvl(a.event_type, -1)
AND x.delete_mark <> 1
);
SELECT count(*)
INTO x_failure_count
FROM gl_evnt_plc
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL);
* Update rows For Source Warehouses *
***********************************************/
UPDATE cm_whse_src a
SET (
a.organization_id,
a.legal_entity_id,
a.delete_mark
)
= (
SELECT w.organization_id, z.legal_entity_id, decode(a.delete_mark, 1, 1, decode(nvl(w.inventory_org_ind, 'N'), 'Y', 0, 1))
FROM gl_plcy_mst z, sy_orgn_mst w
WHERE w.orgn_code = a.orgn_code
AND w.co_code = z.co_code
),
a.source_organization_id = (
SELECT DECODE(NVL(subinventory_ind_flag,'N'), 'Y', organization_id, mtl_organization_id)
FROM ic_whse_mst w1
WHERE w1.whse_code = a.whse_code
)
WHERE (a.legal_entity_id IS NULL AND a.orgn_code IS NOT NULL)
OR (a.organization_id IS NULL AND a.orgn_code IS NOT NULL)
OR (a.source_organization_id IS NULL AND a.whse_code IS NOT NULL);
UPDATE cm_whse_src a
SET (
a.master_organization_id,
a.inventory_item_id
)
=
(
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = a.legal_entity_id
AND y.organization_id = nvl(a.organization_id, y.organization_id)
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.master_organization_id IS NULL AND a.item_id IS NOT NULL);
* Insert records for Warehouses falling under OPM Organizations not migrated as Inventory Organizations *
********************************************************************************************************/
INSERT
INTO cm_whse_src
(
src_whse_id,
calendar_code,
period_code,
sourcing_alloc_pct,
creation_date,
created_by,
last_update_date,
trans_cnt,
text_code,
delete_mark,
last_updated_by,
last_update_login,
cost_category_id,
inventory_item_id,
organization_id,
source_organization_id,
master_organization_id,
legal_entity_id
)
(
SELECT /*+ ROWID(a) */
GEM5_src_whse_id_s.NEXTVAL,
a.calendar_code,
a.period_code,
a.sourcing_alloc_pct,
a.creation_date,
a.created_by,
a.last_update_date,
a.trans_cnt,
a.text_code,
0,
a.last_updated_by,
a.last_update_login,
a.cost_category_id,
a.inventory_item_id,
e.mtl_organization_id,
a.source_organization_id,
a.master_organization_id,
a.legal_entity_id
FROM cm_whse_src a,
ic_whse_mst e
WHERE NOT EXISTS (
SELECT 'X'
FROM cm_whse_src x
WHERE x.legal_entity_id = a.legal_entity_id
AND nvl(x.organization_id, -1) = nvl(e.mtl_organization_id, -1)
AND x.calendar_code = a.calendar_code
AND x.period_code = a.period_code
AND nvl(x.inventory_item_id, -1) = nvl(a.inventory_item_id, -1)
AND nvl(x.cost_category_id, -1) = nvl(a.cost_category_id, -1)
)
AND e.orgn_code = a.orgn_code
AND nvl(e.subinventory_ind_flag,'N') <> 'Y'
AND e.mtl_organization_id IS NOT NULL
AND a.source_organization_id IS NOT NULL
AND a.inventory_item_id IS NOT NULL
AND a.legal_entity_id IS NOT NULL
);
SELECT DISTINCT
item_id,
organization_id
FROM
(
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM cm_acst_led a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM cm_adjs_dtl a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM cm_brdn_dtl a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM cm_cmpt_dtl a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM cm_scst_led a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
nvl(DECODE(NVL(c.subinventory_ind_flag,'N'), 'Y', c.organization_id, c.mtl_organization_id), DECODE(NVL(b.subinventory_ind_flag,'N'), 'Y', b.organization_id, b.mtl_organization_id)) organization_id
FROM cm_whse_src a,
ic_whse_mst b,
ic_whse_mst c
WHERE a.item_id IS NOT NULL
AND b.orgn_code = a.orgn_code
AND c.whse_code(+) = a.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM gl_item_cst a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
DECODE(NVL(c.subinventory_ind_flag,'N'), 'Y', c.organization_id, c.mtl_organization_id) organization_id
FROM gmf_lot_costed_items a,
sy_orgn_mst b,
ic_whse_mst c
WHERE a.item_id IS NOT NULL
AND a.co_code = b.co_Code
AND b.orgn_code = c.orgn_code
AND nvl(c.subinventory_ind_flag,'N') <> 'Y'
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM gmf_lot_Costs a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM gmf_lot_Cost_adjustments a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
UNION
SELECT a.item_id,
NVL(DECODE(b.cost_organization_id, -1, b.mtl_organization_id, b.cost_organization_id), b.mtl_organization_id) organization_id
FROM gmf_lot_Cost_burdens a,
ic_whse_mst b
WHERE a.item_id IS NOT NULL
AND a.whse_code = b.whse_code
) x
WHERE NOT EXISTS
(
SELECT 'X'
FROM ic_item_mst_b_mig y
WHERE y.item_id = x.item_id
AND y.organization_id = x.organization_id
);
* Update row in CM_ACPR_CTL table *
***********************************/
BEGIN
UPDATE cm_acpr_ctl cac
SET (
cac.legal_entity_id,
cac.period_id,
cac.cost_type_id
)
=
(
select gps.legal_entity_id, gps.period_id, gps.cost_type_id
from gmf_period_statuses gps,
cm_mthd_mst cmm,
cm_cldr_hdr_b cch,
gl_plcy_mst gpm
where gps.calendar_code = cac.calendar_code
and cch.calendar_code = cac.calendar_code
and cch.co_code = gpm.co_code
and gps.legal_entity_id = gpm.legal_entity_id
and gps.period_code = cac.period_code
and cmm.cost_mthd_code = cac.cost_mthd_code
and cmm.cost_type_id = gps.cost_type_id
)
where (cac.calendar_code is not null and cac.legal_entity_id is null)
OR (cac.cost_mthd_code is not null AND cac.cost_type_id is null)
OR (cac.calendar_code is not null and cac.period_code is not NULL AND cac.period_id is null);
* Update row in CM_RLUP_CTL table *
***********************************/
BEGIN
UPDATE cm_rlup_ctl crc
SET (
crc.legal_entity_id,
crc.period_id,
crc.cost_type_id
)
=
(
SELECT gps.legal_entity_id,
gps.period_id,
gps.cost_type_id
FROM gmf_period_statuses gps,
cm_mthd_mst cmm,
cm_cldr_hdr_b cch,
gl_plcy_mst gpm
WHERE gps.calendar_code = crc.calendar_code
AND cch.calendar_code = crc.calendar_code
AND cch.co_code = gpm.co_code
AND gps.legal_entity_id = gpm.legal_entity_id
AND gps.period_code = crc.period_code
AND cmm.cost_mthd_code = crc.cost_mthd_code
AND cmm.cost_type_id = gps.cost_type_id
)
WHERE (crc.CALENDAR_CODE IS NOT NULL AND crc.PERIOD_CODE IS NOT NULL AND crc.PERIOD_ID IS NULL)
OR (crc.COST_MTHD_CODE IS NOT NULL AND crc.COST_TYPE_ID IS NULL)
OR (crc.CALENDAR_CODE IS NOT NULL AND crc.LEGAL_ENTITY_ID IS NULL);
UPDATE cm_rlup_ctl a
SET (
a.master_organization_id,
a.inventory_item_id
)
= (
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = a.legal_entity_id
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.master_organization_id IS NULL AND a.item_id IS NOT NULL);
* Update row in CM_RLUP_ITM table *
***********************************/
BEGIN
UPDATE cm_rlup_itm a
SET (
a.organization_id,
a.inventory_item_id
)
= (
SELECT z.master_organization_id,
y.inventory_item_id
FROM ic_item_mst_b_mig y,
mtl_parameters z,
hr_organization_information hoi,
cm_rlup_ctl x
WHERE y.item_id = a.item_id
AND y.organization_id = z.organization_id
AND hoi.organization_id = z.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information2 = x.legal_entity_id
AND x.rollup_id = a.rollup_id
AND ROWNUM = 1
)
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
OR (a.organization_id IS NULL AND a.item_id IS NOT NULL);
* Migrate_CostUpdate_control *
* *
* DESCRIPTION: *
* This PL/SQL procedure is used to migrate the Cost Update control *
* date Records *
* *
* PARAMETERS: *
* P_migration_run_id - id to use to right to migration log *
* x_exception_count - Number of exceptions occurred. *
* *
* SYNOPSIS: *
* Migrate_CostUpdate_control(p_migartion_id => l_migration_id, *
* p_commit => 'T', *
* x_exception_count => l_exception_count ); *
PROCEDURE Migrate_CostUpdate_control
(
P_migration_run_id IN NUMBER,
P_commit IN VARCHAR2,
X_failure_count OUT NOCOPY NUMBER
)
IS
/***************************
* PL/SQL Table Definitions *
***************************/
/******************
* Local Variables *
******************/
BEGIN
G_Migration_run_id := P_migration_run_id;
G_Context := 'Cost Update control data Migration';
* Update row in CM_ACPR_CTL table *
***********************************/
BEGIN
UPDATE cm_cupd_ctl ccc
SET (
ccc.legal_entity_id,
ccc.period_id,
ccc.cost_type_id
)
=
(
select gps.legal_entity_id, gps.period_id, gps.cost_type_id
from gmf_period_statuses gps,
cm_mthd_mst cmm,
gl_plcy_mst gpm
where gps.calendar_code = ccc.calendar_code
and gpm.co_code = ccc.co_code
and gps.period_code = ccc.period_code
and cmm.cost_mthd_code = ccc.cost_mthd_code
and gps.legal_entity_id = gpm.legal_entity_id
and cmm.cost_type_id = gps.cost_type_id
)
where (ccc.calendar_code is not null and ccc.legal_entity_id is null)
OR (ccc.cost_mthd_code is not null AND ccc.cost_type_id is null)
OR (ccc.calendar_code is not null and ccc.period_code is not NULL AND ccc.period_id is null);
END Migrate_CostUpdate_control;
* Update row in GL_SUBR_STA table *
***********************************/
BEGIN
UPDATE gl_subr_sta a
SET (
a.legal_entity_id,
a.legal_entity_name,
a.base_currency,
a.ledger_id,
a.cost_mthd_code,
a.cost_type,
a.cost_type_id,
a.default_cost_mthd_code,
a.default_cost_type_id,
a.cost_basis
)
=
(
select gfp.legal_entity_id,
xep.name,
gfp.base_currency_code,
gfp.ledger_id,
cmm.cost_mthd_code,
cmm.cost_type,
cmm.cost_type_id,
dcmm.cost_mthd_code default_lot_cost_mthd_code,
cmm.default_lot_cost_type_id,
gfp.cost_basis
from cm_mthd_mst cmm,
cm_mthd_mst dcmm,
gl_plcy_mst gpm,
gmf_fiscal_policies gfp,
xle_entity_profiles xep
where gpm.co_code = a.co_code
and gfp.legal_entity_id = gpm.legal_entity_id
and xep.legal_entity_id = gfp.legal_entity_id
and cmm.cost_type_id = gfp.cost_type_id
and cmm.default_lot_cost_type_id = dcmm.cost_type_id(+)
),
a.post_cm_rval = decode(a.post_cm, 1, 1, 0),
a.post_cm_cadj = 0
where (a.co_code is not null and a.legal_entity_id is null)
OR (a.co_code is not null and a.cost_type_id is null);
UPDATE gl_subr_sta gss
SET (
gss.crev_curr_cost_type_id,
gss.crev_curr_period_id
)
=
(
select gps.cost_type_id , gps.period_id
from gmf_period_statuses gps,
cm_mthd_mst cmm
where gps.calendar_code = gss.crev_curr_calendar
and gps.period_code = gss.crev_curr_period
and cmm.cost_mthd_code = gss.crev_curr_mthd
and gps.legal_entity_id = gss.legal_entity_id
and cmm.cost_type_id = gps.cost_type_id
),
(
gss.crev_prev_cost_type_id,
gss.crev_prev_period_id
)
=
(
select gps.cost_type_id , gps.period_id
from gmf_period_statuses gps,
cm_mthd_mst cmm
where gps.calendar_code = gss.crev_prev_calendar
and gps.period_code = gss.crev_prev_period
and cmm.cost_mthd_code = gss.crev_prev_mthd
and gps.legal_entity_id = gss.legal_entity_id
and cmm.cost_type_id = gps.cost_type_id
),
gss.period_id
= (
SELECT x.period_id
FROM gmf_period_statuses x
WHERE x.legal_entity_id = gss.legal_entity_id
AND x.cost_type_id = gss.cost_type_id
AND gss.period_start_date between x.start_date and x.end_date
AND gss.period_end_date between x.start_date and x.end_date
AND x.delete_mark <> 1
AND ROWNUM = 1
)
where (gss.crev_curr_mthd is not null AND gss.crev_curr_cost_type_id IS NULL)
OR (gss.crev_curr_calendar is not null and gss.crev_curr_period is not NULL AND gss.crev_curr_period_id is null)
OR (gss.crev_prev_mthd is not null AND gss.crev_prev_cost_type_id IS NULL)
OR (gss.crev_prev_calendar is not null and gss.crev_prev_period is not NULL AND gss.crev_prev_period_id is null)
OR (gss.legal_entity_id IS NOT NULL AND gss.cost_type_id IS NOT NULL AND gss.period_id IS NULL);
SELECT a.whse_code,
NVL(a.subinventory_ind_flag, 'N') subinventory_ind_flag,
a.mtl_organization_id,
a.organization_id,
b.orgn_code,
NVL(b.inventory_org_ind, 'N') inventory_org_ind,
NVL(b.migrate_as_ind, 0) orgn_migrated_as_ind,
decode(a.organization_id, a.mtl_organization_id, 'Y', 'N') same_plant_whse,
SUM(decode(NVL(c.subinventory_ind_flag, 'N'), 'N', 0, 1)) Subinventory_count,
DECODE(COUNT(d.cost_whse_code), 0, 'N', 'Y') cost_warehouse,
DECODE(COUNT(f.cost_whse_code), 0, 'N', 'Y') same_plant_cost_warehouse,
DECODE(SUM(DECODE(f.cost_whse_code, NULL, 0, DECODE(NVL(c.subinventory_ind_flag, 'N'), 'N', 0, 1))), 0, 'N', 'Y') cost_whse_is_subinv,
DECODE(COUNT(e.whse_code), 0, 'N', 'Y') inv_warehouse
FROM ic_whse_mst a,
sy_orgn_mst b,
ic_whse_mst c,
cm_whse_asc d,
cm_whse_asc e,
cm_whse_asc f
WHERE a.orgn_code = b.orgn_code
AND c.orgn_code = a.orgn_code
AND d.cost_whse_code(+) = a.whse_code
AND f.cost_whse_code(+) = c.whse_code
AND e.whse_code(+) = a.whse_code
AND SYSDATE BETWEEN d.eff_start_date(+) AND d.eff_end_date(+)
AND SYSDATE BETWEEN e.eff_start_date(+) AND e.eff_end_date(+)
AND SYSDATE BETWEEN f.eff_start_date(+) AND f.eff_end_date(+)
GROUP BY a.whse_code,
a.subinventory_ind_flag,
a.mtl_organization_id,
a.organization_id,
b.orgn_code,
b.inventory_org_ind,
b.migrate_as_ind,
b.organization_id
ORDER BY a.whse_code;
UPDATE ic_whse_mst a
SET a.cost_organization_id = l_costing_organization_id
WHERE a.whse_code = i.whse_code;
UPDATE cm_whse_asc a
SET (
a.organization_id
)
= (
SELECT x.cost_organization_id
FROM ic_whse_mst x
WHERE x.whse_code = a.whse_code
),
(
a.cost_organization_id
)
= (
SELECT x.cost_organization_id
FROM ic_whse_mst x
WHERE x.whse_code = a.cost_whse_code
);
UPDATE cm_whse_asc a
SET delete_mark = 1
WHERE (
ROWID NOT IN (
SELECT MIN(ROWID)
FROM cm_whse_asc x
WHERE x.cost_organization_id = a.cost_organization_id
AND x.organization_id = a.organization_id
AND x.delete_mark <> 1
AND (
(a.eff_start_date BETWEEN x.eff_start_date AND x.eff_end_date)
OR
(a.eff_end_date BETWEEN x.eff_start_date AND x.eff_end_date)
)
)
)
OR cost_organization_id = -1;
SELECT count(*)
INTO x_failure_count
FROM cm_whse_asc
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
OR (cost_organization_id IS NULL AND cost_whse_code IS NOT NULL);
l_sql_statement VARCHAR2(32000) := 'SELECT count(*) FROM '||l_table_name||' WHERE ';
l_cm_rsrc_dtl VARCHAR2(32000) := 'SELECT ''CM_RSRC_DTL'' table_name,
cm_rsrc_dtl.*
FROM (
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Orgn Code: ''|| orgn_code parameters,
count(*) records
FROM cm_rsrc_dtl
WHERE (legal_entity_id IS NULL AND orgn_code IS NOT NULL)
GROUP BY orgn_code
HAVING count(*) > 0
UNION
SELECT ''ORGANIZATION_ID'' column_name,
''Orgn Code: ''|| orgn_code parameters,
count(*) records
FROM cm_rsrc_dtl
WHERE (organization_id IS NULL AND delete_mark = 0 AND orgn_code IS NOT NULL)
GROUP BY orgn_code
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_rsrc_dtl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_rsrc_dtl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''USAGE_UOM'' column_name,
''UM Code: ''|| usage_um parameters,
count(*) records
FROM cm_rsrc_dtl
WHERE (usage_uom IS NULL AND usage_um IS NOT NULL)
GROUP BY usage_um
HAVING count(*) > 0
) cm_rsrc_dtl';
l_cm_adjs_dtl VARCHAR2(32000) := 'SELECT ''CM_ADJS_DTL'' table_name,
cm_adjs_dtl.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_adjs_dtl
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_adjs_dtl a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_adjs_dtl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_adjs_dtl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''ADJUST_QTY_UOM'' column_name,
''Adjust qty UM: ''|| adjust_qty_um parameters,
count(*) records
FROM cm_adjs_dtl
WHERE (adjust_qty_uom IS NULL AND adjust_qty_um IS NOT NULL)
GROUP BY adjust_qty_um
HAVING count(*) > 0
UNION
SELECT ''ADJUSTMENT_IND'' column_name,
''NULL'' parameters,
count(*) records
FROM cm_adjs_dtl
WHERE (adjustment_ind IS NULL)
HAVING count(*) > 0
) cm_adjs_dtl';
l_cm_cmpt_dtl VARCHAR2(32000) := 'SELECT ''CM_CMPT_DTL'' table_name,
cm_cmpt_dtl.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_cmpt_dtl
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_cmpt_dtl a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_cmpt_dtl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_cmpt_dtl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
) cm_cmpt_dtl';
l_cm_brdn_dtl VARCHAR2(32000) := 'SELECT ''CM_BRDN_DTL'' table_name,
cm_brdn_dtl.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_brdn_dtl
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_brdn_dtl a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_brdn_dtl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_brdn_dtl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''ITEM_UOM'' column_name,
''Item UM: ''|| item_um parameters,
count(*) records
FROM cm_brdn_dtl
WHERE (item_uom IS NULL AND item_um IS NOT NULL)
GROUP BY item_um
HAVING count(*) > 0
UNION
SELECT ''BURDEN_UOM'' column_name,
''Burden UM: ''|| burden_um parameters,
count(*) records
FROM cm_brdn_dtl
WHERE (burden_uom IS NULL AND burden_um IS NOT NULL)
GROUP BY burden_um
HAVING count(*) > 0
) cm_brdn_dtl';
l_gl_item_cst VARCHAR2(32000) := 'SELECT ''GL_ITEM_CST'' table_name,
gl_item_cst.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM gl_item_cst
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM gl_item_cst a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM gl_item_cst
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM gl_item_cst
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
) gl_item_cst';
l_cm_scst_led VARCHAR2(32000) := 'SELECT ''CM_SCST_LED'' table_name,
cm_scst_led.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_scst_led
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_scst_led a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''FORM_PROD_UOM'' column_name,
''Formula UM: ''|| form_prod_um parameters,
count(*) records
FROM cm_scst_led
WHERE (form_prod_uom IS NULL AND form_prod_um IS NOT NULL)
GROUP BY form_prod_um
HAVING count(*) > 0
UNION
SELECT ''ITEM_FMQTY_UOM'' column_name,
''Item UOM: ''|| item_fmqty_um parameters,
count(*) records
FROM cm_scst_led
WHERE (item_fmqty_uom IS NULL AND item_fmqty_um IS NOT NULL)
GROUP BY item_fmqty_um
HAVING count(*) > 0
UNION
SELECT ''USAGE_UOM'' column_name,
''Usage UOM: ''|| usage_um parameters,
count(*) records
FROM cm_scst_led
WHERE (usage_uom IS NULL AND usage_um IS NOT NULL)
GROUP BY usage_um
HAVING count(*) > 0
) cm_scst_led';
l_cm_acst_led VARCHAR2(32000) := 'SELECT ''CM_ACST_LED'' table_name,
cm_acst_led.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_acst_led
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_acst_led a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_acst_led
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_acst_led
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
) cm_acst_led';
l_gmf_lot_costs VARCHAR2(32000) := 'SELECT ''GMF_LOT_COSTS'' table_name,
gmf_lot_costs.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM gmf_lot_costs
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM gmf_lot_costs a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM gmf_lot_costs
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''LOT_NUMBER'' column_name,
''Lot Id: ''|| lot_id parameters,
count(*) records
FROM gmf_lot_costs
WHERE (lot_number IS NULL AND lot_id IS NOT NULL)
GROUP BY lot_id
HAVING count(*) > 0
) gmf_lot_costs';
l_gmf_lot_costed_items VARCHAR2(32000) := 'SELECT ''GMF_LOT_COSTED_ITEMS'' table_name,
gmf_lot_costed_items.*
FROM (
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Co Code: ''|| co_code parameters,
count(*) records
FROM gmf_lot_costed_items
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL)
GROUP BY co_code
HAVING count(*) > 0
UNION
SELECT ''MASTER_ORGANIZATION_ID'' column_name,
''Item No: ''|| b.item_no ||'' Legal Entity: ''||a.legal_entity_id parameters,
count(*) records
FROM gmf_lot_costed_items a, ic_item_mst b
WHERE (a.legal_entity_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no, a.legal_entity_id
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM gmf_lot_costed_items a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM gmf_lot_costed_items
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
) gmf_lot_costed_items';
l_gmf_lot_cost_burdens VARCHAR2(32000) := 'SELECT ''GMF_LOT_COST_BURDENS'' table_name,
gmf_lot_cost_burdens.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM gmf_lot_cost_burdens
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM gmf_lot_cost_burdens a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM gmf_lot_cost_burdens
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''ITEM_UOM'' column_name,
''Item UOM: ''|| Item_um parameters,
count(*) records
FROM gmf_lot_cost_burdens
WHERE (item_uom IS NULL AND item_um IS NOT NULL)
GROUP BY item_um
HAVING count(*) > 0
UNION
SELECT ''RESOURCE_UOM'' column_name,
''Resource UOM: ''|| Resource_um parameters,
count(*) records
FROM gmf_lot_cost_burdens
WHERE (resource_uom IS NULL AND resource_um IS NOT NULL)
GROUP BY resource_um
HAVING count(*) > 0
UNION
SELECT ''LOT_NUMBER'' column_name,
''Lot Id: ''|| lot_id parameters,
count(*) records
FROM gmf_lot_cost_burdens
WHERE (lot_number IS NULL AND lot_id IS NOT NULL)
GROUP BY lot_id
HAVING count(*) > 0
) gmf_lot_cost_burdens';
l_gmf_lot_cost_adjustments VARCHAR2(32000) := 'SELECT ''GMF_LOT_COST_ADJUSTMENTS'' table_name,
gmf_lot_cost_adjustments.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM gmf_lot_cost_adjustments
WHERE (organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM gmf_lot_cost_adjustments a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM gmf_lot_cost_adjustments
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Co Code: ''|| co_code parameters,
count(*) records
FROM gmf_lot_cost_adjustments
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL)
GROUP BY co_code
HAVING count(*) > 0
UNION
SELECT ''LOT_NUMBER'' column_name,
''Lot Id: ''|| lot_id parameters,
count(*) records
FROM gmf_lot_cost_adjustments
WHERE (lot_number IS NULL AND lot_id IS NOT NULL)
GROUP BY lot_id
HAVING count(*) > 0
) gmf_lot_cost_adjustments';
l_gmf_material_lot_cost_txns VARCHAR2(32000) := 'SELECT ''GMF_MATERIAL_LOT_COST_TXNS'' table_name,
gmf_material_lot_cost_txns.*
FROM (
SELECT ''COST_TYPE_ID'' column_name,
''Cost Type Code: ''|| cost_type_code parameters,
count(*) records
FROM gmf_material_lot_cost_txns
WHERE (cost_type_id IS NULL AND cost_type_code IS NOT NULL)
GROUP BY cost_type_code
HAVING count(*) > 0
UNION
SELECT ''COST_TRANS_UOM'' column_name,
''Cost Trans UOM: ''|| cost_trans_uom parameters,
count(*) records
FROM gmf_material_lot_cost_txns
WHERE (cost_trans_um IS NULL AND cost_trans_uom IS NOT NULL)
GROUP BY cost_trans_uom
HAVING count(*) > 0
) gmf_material_lot_cost_txns';
l_cm_whse_src VARCHAR2(32000) := 'SELECT ''CM_WHSE_SRC'' table_name,
cm_whse_src.*
FROM (
SELECT ''SOURCE_ORGANIZATION_ID'' column_name,
''Warehouse Code: ''|| whse_code parameters,
count(*) records
FROM cm_whse_src
WHERE (source_organization_id IS NULL AND whse_code IS NOT NULL)
GROUP BY whse_code
HAVING count(*) > 0
UNION
SELECT ''ORGANIZATION_ID'' column_name,
''Orgn Code: ''|| orgn_code parameters,
count(*) records
FROM cm_whse_src
WHERE (organization_id IS NULL AND orgn_code IS NOT NULL AND delete_mark = 0)
GROUP BY orgn_code
HAVING count(*) > 0
UNION
SELECT ''MASTER_ORGANIZATION_ID'' column_name,
''Item No: ''|| b.item_no ||'' Legal Entity: ''||a.legal_entity_id parameters,
count(*) records
FROM cm_whse_src a, ic_item_mst b
WHERE (a.legal_entity_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no, a.legal_entity_id
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_whse_src a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Orgn Code: ''|| orgn_code parameters,
count(*) records
FROM cm_whse_src
WHERE (legal_entity_id IS NULL AND orgn_code IS NOT NULL)
GROUP BY orgn_code
HAVING count(*) > 0
) cm_whse_src';
l_cm_acpr_ctl VARCHAR2(32000) := 'SELECT ''CM_ACPR_CTL'' table_name,
cm_acpr_ctl.*
FROM (
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_acpr_ctl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_acpr_ctl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Calendar Code: ''|| calendar_code parameters,
count(*) records
FROM cm_acpr_ctl
WHERE (legal_entity_id IS NULL AND calendar_code IS NOT NULL)
GROUP BY calendar_code
HAVING count(*) > 0
) cm_acpr_ctl';
l_cm_rlup_ctl VARCHAR2(32000) := 'SELECT ''CM_RLUP_CTL'' table_name,
cm_rlup_ctl.*
FROM (
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_rlup_ctl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_rlup_ctl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Calendar Code: ''|| calendar_code parameters,
count(*) records
FROM cm_rlup_ctl
WHERE (legal_entity_id IS NULL AND calendar_code IS NOT NULL)
GROUP BY calendar_code
HAVING count(*) > 0
UNION
SELECT ''MASTER_ORGANIZATION_ID'' column_name,
''Item No: ''|| b.item_no ||'' Legal Entity: ''||a.legal_entity_id parameters,
count(*) records
FROM cm_rlup_ctl a, ic_item_mst b
WHERE (a.legal_entity_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no, a.legal_entity_id
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_rlup_ctl a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
) cm_rlup_ctl';
l_cm_rlup_itm VARCHAR2(32000) := 'SELECT ''CM_RLUP_ITM'' table_name,
cm_rlup_itm.*
FROM (
SELECT ''ORGANIZATION_ID'' column_name,
''Item No: ''|| b.item_no ||'' Legal Entity: ''||c.legal_entity_id parameters,
count(*) records
FROM cm_rlup_ctl a, ic_item_mst b, cm_rlup_ctl c
WHERE (a.legal_entity_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
AND c.rollup_id = a.rollup_id
GROUP BY b.item_no, c.legal_entity_id
HAVING count(*) > 0
UNION
SELECT ''INVENTORY_ITEM_ID'' column_name,
''Item No: ''|| b.item_no parameters,
count(*) records
FROM cm_rlup_itm a, ic_item_mst b
WHERE (a.inventory_item_id IS NULL AND a.item_id IS NOT NULL)
AND b.item_id = a.item_id
GROUP BY b.item_no
HAVING count(*) > 0
) cm_rlup_itm';
l_cm_cupd_ctl VARCHAR2(32000) := 'SELECT ''CM_CUPD_CTL'' table_name,
cm_cupd_ctl.*
FROM (
SELECT ''COST_TYPE_ID'' column_name,
''Cost Method Code: ''|| cost_mthd_code parameters,
count(*) records
FROM cm_cupd_ctl
WHERE (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL)
GROUP BY cost_mthd_code
HAVING count(*) > 0
UNION
SELECT ''PERIOD_ID'' column_name,
''Calendar Code: ''|| calendar_code ||'', Period Code: ''|| period_code parameters,
count(*) records
FROM cm_cupd_ctl
WHERE (period_id IS NULL AND calendar_code IS NOT NULL AND period_code IS NOT NULL)
GROUP BY calendar_code, period_code
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Calendar Code: ''|| calendar_code parameters,
count(*) records
FROM cm_cupd_ctl
WHERE (legal_entity_id IS NULL AND calendar_code IS NOT NULL)
GROUP BY calendar_code
HAVING count(*) > 0
) cm_cupd_ctl';
l_gl_subr_sta VARCHAR2(32000) := 'SELECT ''GL_SUBR_STA'' table_name,
gl_subr_sta.*
FROM (
SELECT ''CREV_CURR_COST_TYPE_ID'' column_name,
''Current Cost Method Code: ''|| crev_curr_mthd parameters,
count(*) records
FROM gl_subr_sta
WHERE (crev_curr_cost_type_id IS NULL AND crev_curr_mthd IS NOT NULL)
GROUP BY crev_curr_mthd
HAVING count(*) > 0
UNION
SELECT ''CREV_CURR_PERIOD_ID'' column_name,
''Current Calendar Code: ''|| crev_curr_calendar ||'', Period Code: ''|| crev_curr_period parameters,
count(*) records
FROM gl_subr_sta
WHERE (crev_curr_calendar is not null and crev_curr_period is not NULL AND crev_curr_period_id is null)
GROUP BY crev_curr_calendar, crev_curr_period
HAVING count(*) > 0
UNION
SELECT ''CREV_PREV_COST_TYPE_ID'' column_name,
''Previous Cost Method Code: ''|| crev_prev_mthd parameters,
count(*) records
FROM gl_subr_sta
WHERE (crev_prev_cost_type_id IS NULL AND crev_prev_mthd IS NOT NULL)
GROUP BY crev_prev_mthd
HAVING count(*) > 0
UNION
SELECT ''CREV_PREV_PERIOD_ID'' column_name,
''Previous Calendar Code: ''|| crev_prev_calendar ||'', Period Code: ''|| crev_prev_period parameters,
count(*) records
FROM gl_subr_sta
WHERE (crev_prev_calendar is not null and crev_prev_period is not NULL AND crev_prev_period_id is null)
GROUP BY crev_prev_calendar, crev_prev_period
HAVING count(*) > 0
UNION
SELECT ''LEGAL_ENTITY_ID'' column_name,
''Co Code: ''|| co_code parameters,
count(*) records
FROM gl_subr_sta
WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL)
GROUP BY co_code
HAVING count(*) > 0
UNION
SELECT ''COST_TYPE_ID'' column_name,
''Co Code: ''|| co_code parameters,
count(*) records
FROM gl_subr_sta
WHERE (cost_type_id IS NULL AND co_code IS NOT NULL)
GROUP BY co_code
HAVING count(*) > 0
) gl_subr_sta';
l_xla_rules_t VARCHAR2(32000) := 'SELECT ''XLA_RULES_T'' table_name,
xla_rules_t.*
FROM (
SELECT ''ALL'' column_name,
''Unique Constraint Error'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = -1
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Not Null Constraint'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = -1400
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Value Error'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = -6502
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Parent-Key Not Found Error'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = -2291
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Value Too Long Error'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value in (-1438, -12899)
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Number Error'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = -1722
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Records not Picked up'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value = 0
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Other Errors'' parameters,
count(*) records
FROM xla_rules_t
WHERE error_value not in (-1, -1400, -6502, -2291, -1438, -12899, -1722, 1, 0)
HAVING count(*) > 0
) xla_rules_t';
l_xla_rule_details_t VARCHAR2(32000) := 'SELECT ''XLA_RULE_DETAILS_T'' table_name,
xla_rule_details_t.*
FROM (
SELECT ''ALL'' column_name,
''Unique Constraint Error'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = -1
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Not Null Constraint'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = -1400
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Value Error'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = -6502
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Parent-Key Not Found Error'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = -2291
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Value Too Long Error'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value in (-1438, -12899)
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Number Error'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = -1722
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Records not Picked up'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value = 0
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Other Errors'' parameters,
count(*) records
FROM xla_rule_details_t
WHERE error_value not in (-1, -1400, -6502, -2291, -1438, -12899, -1722, 1, 0)
HAVING count(*) > 0
) xla_rule_details_t';
l_xla_conditions_t VARCHAR2(32000) := 'SELECT ''XLA_CONDITIONS_T'' table_name,
xla_conditions_t.*
FROM (
SELECT ''ALL'' column_name,
''Unique Constraint Error'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = -1
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Not Null Constraint'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = -1400
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Value Error'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = -6502
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Parent-Key Not Found Error'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = -2291
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Value Too Long Error'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value in (-1438, -12899)
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Number Error'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = -1722
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Records not Picked up'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value = 0
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Other Errors'' parameters,
count(*) records
FROM xla_conditions_t
WHERE error_value not in (-1, -1400, -6502, -2291, -1438, -12899, -1722, 1, 0)
HAVING count(*) > 0
) xla_conditions_t';
l_xla_line_assgns_t VARCHAR2(32000) := 'SELECT ''XLA_LINE_ASSGNS_T'' table_name,
xla_line_assgns_t.*
FROM (
SELECT ''ALL'' column_name,
''Unique Constraint Error'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = -1
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Not Null Constraint'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = -1400
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Value Error'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = -6502
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Parent-Key Not Found Error'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = -2291
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Value Too Long Error'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value in (-1438, -12899)
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Invalid Number Error'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = -1722
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Records not Picked up'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value = 0
HAVING count(*) > 0
UNION
SELECT ''ALL'' column_name,
''Other Errors'' parameters,
count(*) records
FROM xla_line_assgns_t
WHERE error_value not in (-1, -1400, -6502, -2291, -1438, -12899, -1722, 1, 0)
HAVING count(*) > 0
) xla_line_assgns_t';
OR (organization_id IS NULL AND delete_mark = 0 AND orgn_code IS NOT NULL)
)';
SELECT SUM(CASE WHEN (legal_entity_id IS NULL AND orgn_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (usage_uom IS NULL AND usage_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (organization_id IS NULL AND delete_mark = 0 AND orgn_code IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_legal_entity_count,
l_cost_type_count,
l_period_count,
l_uom_count1,
l_organization_count
FROM cm_rsrc_dtl;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (adjust_qty_uom IS NULL AND adjust_qty_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (adjustment_ind IS NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_period_count,
l_uom_count1,
l_adjustment_ind_count
FROM cm_adjs_dtl;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_period_count
FROM cm_cmpt_dtl;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (item_uom IS NULL AND item_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (burden_uom IS NULL AND burden_um IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_period_count,
l_uom_count1,
l_uom_count2
FROM cm_brdn_dtl;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_period_count
FROM gl_item_cst;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (form_prod_uom IS NULL AND form_prod_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (item_fmqty_uom IS NULL AND item_fmqty_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (usage_uom IS NULL AND usage_um IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_uom_count1,
l_uom_count2,
l_uom_count3
FROM cm_scst_led;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (period_id IS NULL AND period_code IS NOT NULL AND calendar_code IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_period_count
FROM cm_acst_led;
SELECT SUM(DECODE(error_value, -1, 1, 0)),
SUM(DECODE(error_value, -1400, 1, 0)),
SUM(DECODE(error_value, -6502, 1, 0)),
SUM(DECODE(error_value, -2291, 1, 0)),
SUM(DECODE(error_value, -1438, 1, -12899, 1, 0)),
SUM(DECODE(error_value, -1722, 1, 0)),
SUM(DECODE(error_value, 0, 1, 0)),
SUM(DECODE(error_value, 1, 0, 1))
INTO l_unique_error_count,
l_not_null_error_count,
l_value_error_count,
l_parent_key_error_count,
l_too_long_error_count,
l_invalid_number_error_count,
l_not_picked_up_error_count,
l_total_error_count
FROM xla_rules_t;
SELECT SUM(DECODE(error_value, -1, 1, 0)),
SUM(DECODE(error_value, -1400, 1, 0)),
SUM(DECODE(error_value, -6502, 1, 0)),
SUM(DECODE(error_value, -2291, 1, 0)),
SUM(DECODE(error_value, -1438, 1, -12899, 1, 0)),
SUM(DECODE(error_value, -1722, 1, 0)),
SUM(DECODE(error_value, 0, 1, 0)),
SUM(DECODE(error_value, 1, 0, 1))
INTO l_unique_error_count,
l_not_null_error_count,
l_value_error_count,
l_parent_key_error_count,
l_too_long_error_count,
l_invalid_number_error_count,
l_not_picked_up_error_count,
l_total_error_count
FROM xla_rule_details_t;
SELECT SUM(DECODE(error_value, -1, 1, 0)),
SUM(DECODE(error_value, -1400, 1, 0)),
SUM(DECODE(error_value, -6502, 1, 0)),
SUM(DECODE(error_value, -2291, 1, 0)),
SUM(DECODE(error_value, -1438, 1, -12899, 1, 0)),
SUM(DECODE(error_value, -1722, 1, 0)),
SUM(DECODE(error_value, 0, 1, 0)),
SUM(DECODE(error_value, 1, 0, 1))
INTO l_unique_error_count,
l_not_null_error_count,
l_value_error_count,
l_parent_key_error_count,
l_too_long_error_count,
l_invalid_number_error_count,
l_not_picked_up_error_count,
l_total_error_count
FROM xla_conditions_t;
SELECT SUM(DECODE(error_value, -1, 1, 0)),
SUM(DECODE(error_value, -1400, 1, 0)),
SUM(DECODE(error_value, -6502, 1, 0)),
SUM(DECODE(error_value, -2291, 1, 0)),
SUM(DECODE(error_value, -1438, 1, -12899, 1, 0)),
SUM(DECODE(error_value, -1722, 1, 0)),
SUM(DECODE(error_value, 0, 1, 0)),
SUM(DECODE(error_value, 1, 0, 1))
INTO l_unique_error_count,
l_not_null_error_count,
l_value_error_count,
l_parent_key_error_count,
l_too_long_error_count,
l_invalid_number_error_count,
l_not_picked_up_error_count,
l_total_error_count
FROM xla_line_assgns_t;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (lot_number IS NULL AND lot_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_lot_number_count
FROM gmf_lot_costs;
SELECT SUM(CASE WHEN (master_organization_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (legal_entity_id IS NULL AND co_code IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_master_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_legal_entity_count
FROM gmf_lot_costed_items;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (item_uom IS NULL AND item_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (resource_uom IS NULL AND resource_um IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (lot_number IS NULL AND lot_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_uom_count1,
l_uom_count2,
l_lot_number_count
FROM gmf_lot_cost_burdens;
SELECT SUM(CASE WHEN (organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_type_id IS NULL AND cost_mthd_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (legal_entity_id IS NULL AND co_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (lot_number IS NULL AND lot_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_organization_count,
l_inventory_item_count,
l_cost_type_count,
l_legal_entity_count,
l_lot_number_count
FROM gmf_lot_cost_adjustments;
SELECT SUM(CASE WHEN (cost_type_id IS NULL AND cost_type_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_trans_um IS NULL AND cost_trans_uom IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_cost_type_count,
l_uom_count1
FROM gmf_material_lot_cost_txns;
OR (organization_id IS NULL AND delete_mark = 0 AND orgn_code IS NOT NULL)
)';
SELECT SUM(CASE WHEN (source_organization_id IS NULL AND whse_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (organization_id IS NULL AND delete_mark = 0 AND orgn_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (legal_entity_id IS NULL AND orgn_code IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (master_organization_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_source_organization_count,
l_inventory_item_count,
l_organization_count,
l_legal_entity_count,
l_master_organization_count
FROM cm_whse_src;
SELECT SUM(CASE WHEN (calendar_code IS NOT NULL AND period_code IS NOT NULL and period_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_mthd_code IS NOT NULL AND cost_type_id is NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (calendar_code IS NOT NULL AND legal_entity_id IS NULL) THEN 1 ELSE 0 END)
INTO l_period_count,
l_cost_type_count,
l_legal_entity_count
FROM cm_acpr_ctl;
SELECT SUM(CASE WHEN (calendar_code IS NOT NULL AND period_code IS NOT NULL and period_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_mthd_code IS NOT NULL AND cost_type_id is NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (calendar_code IS NOT NULL AND legal_entity_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (master_organization_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_period_count,
l_cost_type_count,
l_legal_entity_count,
l_inventory_item_count,
l_master_organization_count
FROM cm_rlup_ctl;
SELECT SUM(CASE WHEN (inventory_item_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (organization_id IS NULL AND item_id IS NOT NULL) THEN 1 ELSE 0 END)
INTO l_inventory_item_count,
l_master_organization_count
FROM cm_rlup_itm;
SELECT SUM(CASE WHEN (calendar_code IS NOT NULL AND period_code IS NOT NULL and period_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (cost_mthd_code IS NOT NULL AND cost_type_id is NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (calendar_code IS NOT NULL AND legal_entity_id IS NULL) THEN 1 ELSE 0 END)
INTO l_period_count,
l_cost_type_count,
l_legal_entity_count
FROM cm_cupd_ctl;
SELECT SUM(CASE WHEN (crev_curr_mthd is not null AND crev_curr_cost_type_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (crev_curr_calendar is not null and crev_curr_period is not NULL AND crev_curr_period_id is null) THEN 1 ELSE 0 END),
SUM(CASE WHEN (crev_prev_mthd is not null AND crev_prev_cost_type_id IS NULL) THEN 1 ELSE 0 END),
SUM(CASE WHEN (crev_prev_calendar is not null and crev_prev_period is not NULL AND crev_prev_period_id is null) THEN 1 ELSE 0 END),
SUM(CASE WHEN (co_code is not null and legal_entity_id is null) THEN 1 ELSE 0 END),
SUM(CASE WHEN (co_code is not null AND cost_type_id is null) THEN 1 ELSE 0 END)
INTO l_curr_cost_type_count,
l_curr_period_count,
l_prev_cost_type_count,
l_prev_period_count,
l_legal_entity_count,
l_cost_type_count
FROM gl_subr_sta;
UPDATE gl_acct_map gam
SET gam.vendor_id
= (
SELECT v.of_vendor_site_id
FROM po_vend_mst v
WHERE v.vendor_id = gam.vendor_id
),
gam.migrated_ind = 1
WHERE gam.vendor_id IS NOT NULL
AND nvl(gam.migrated_ind, -1) <> 1;