run
{
duplicate database to "DEV"
spfile
set db_file_name_convert='+EBS/ADPF/DATAFILE','/u01/app/oracle/oradata'
set log_file_name_convert='+EBS/adpf/logfile','/u01/app/oracle/oradata'
SET CLUSTER_DATABASE='FALSE'
set UNDO_TABLESPACE='undotbs1'
set control_files='/u01/app/oracle/oradata/cntrl01.dbf','/u01/app/oracle/oradata/cntrl02.dbf','/u01/app/oracle/oradata/cntrl03.dbf'
backup location '/BIDEVDISK/clone_15042021/data/'
nofilenamecheck ;
}
Solution is to delete all files under oraInventory:
rm -rf /u01/app/oraInventory/*
DROP VIEW JEDOX.JEDOX_VIEW_COST_CENTER;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_COST_CENTER
(
"Value",
"Description"
)
AS
SELECT DISTINCT FFV.FLEX_VALUE "Value", FFVT.DESCRIPTION "Description"
FROM apps.FND_ID_FLEXS FIF,
apps.FND_ID_FLEX_STRUCTURES_TL FIFST,
apps.FND_ID_FLEX_SEGMENTS FIFS,
apps.FND_FLEX_VALUES FFV,
apps.FND_FLEX_VALUES_TL FFVT,
apps.FND_LOOKUPS FL,
apps.FND_LOOKUPS FL_PARENT
WHERE FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield'
AND FIFST.ID_FLEX_CODE = FIF.ID_FLEX_CODE
AND FIFST.LANGUAGE = 'US'
AND FIFS.ID_FLEX_CODE = FIF.ID_FLEX_CODE
AND UPPER (FIFS.SEGMENT_NAME) LIKE '%COST%CENTER'
AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
AND FFVT.LANGUAGE = 'US'
AND FL.LOOKUP_TYPE(+) = 'YES_NO'
AND FL.LOOKUP_CODE(+) = FFV.ENABLED_FLAG
AND FL_PARENT.LOOKUP_TYPE(+) = 'YES_NO'
AND FL_PARENT.LOOKUP_CODE(+) = FFV.SUMMARY_FLAG;
DROP VIEW JEDOX.JEDOX_VIEW_EXPENCE_ACCOUNT;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_EXPENCE_ACCOUNT
(
"Company Code",
"Expense Item",
"Project Type",
"Project Code",
"Cost Center",
"Currency",
"0000",
"Expense Description",
ENABLED_FLAG,
TYPE
)
AS
SELECT gcc.segment1 "Company Code",
gcc.segment2 "Expense Item",
gcc.segment3 "Project Type",
gcc.segment4 "Project Code",
gcc.segment5 "Cost Center",
gcc.segment6 "Currency",
gcc.segment7 "0000",
ffvt.description "Expense Description",
gcc.enabled_flag,
CASE
WHEN gcc.account_type = 'A' THEN 'Asset'
WHEN gcc.account_type = 'E' THEN 'Expense'
WHEN gcc.account_type = 'R' THEN 'Revenue'
WHEN gcc.account_type = 'O' THEN 'Equity'
WHEN gcc.account_type = 'L' THEN 'Liability'
END TYPE
FROM apps.fnd_flex_values ffv,
apps.fnd_flex_values_tl ffvt,
apps.fnd_flex_value_sets ffvs,
apps.fnd_id_flex_segments fifs,
apps.fnd_flex_hierarchies_vl ffhv,
apps.gl_code_combinations gcc
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND UPPER (fifs.application_column_name) = 'SEGMENT2'
AND fifs.id_flex_code = 'GL#'
AND ffv.structured_hierarchy_level = ffhv.hierarchy_ID(+)
AND gcc.segment2 = ffv.flex_value(+);
DROP VIEW JEDOX.JEDOX_VIEW_GRADERATES;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_GRADERATES
(
"Rate",
"Grade Name",
"Currency",
"Value",
"Minimum",
"Maximum",
"Mid Value",
"EffDatesFrom",
"EffDatesTo",
GRADE_ID,
RATE_ID
)
AS
SELECT COALESCE (pr.name, '') AS "Rate",
COALESCE (pgt.name, '') AS "Grade Name",
COALESCE (fcv.name, '') AS "Currency",
COALESCE (pgr.VALUE, '') AS "Value",
COALESCE (pgr.minimum, '') AS "Minimum",
COALESCE (pgr.maximum, '') AS "Maximum",
COALESCE (pgr.mid_value, '') AS "Mid Value",
pgr.effective_start_date AS "EffDatesFrom",
pgr.effective_end_date AS "EffDatesTo",
COALESCE (pgt.grade_id, 0) AS grade_id,
COALESCE (pr.rate_id, 0) AS rate_id
FROM apps.per_grades_tl pgt,
apps.pay_grade_rules_f pgr,
apps.pay_rates pr,
apps.fnd_currencies_vl fcv
WHERE 1 = 1
AND pgt.grade_id(+) = pgr.grade_or_spinal_point_id
AND pr.rate_id = pgr.rate_id
AND fcv.currency_code(+) = pgr.currency_code;
DROP VIEW JEDOX.JEDOX_VIEW_INV_DETAILS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_INV_DETAILS
(
INVOICE_NUM,
DISTRIBUTION_LINE_NUMBER,
DESCRIPTION,
"Item Code",
AMOUNT
)
AS
SELECT DISTINCT
inv.invoice_num,
dis.distribution_line_number,
dis.description,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7 "Item Code",
dis.amount
FROM apps.ap_invoices_all inv,
apps.hr_operating_units hou,
apps.po_headers_all pha,
apps.ap_invoice_distributions_all dis,
apps.gl_code_combinations gcc
WHERE inv.invoice_id = dis.invoice_id
AND inv.po_header_id = pha.po_header_id(+)
AND inv.org_id = hou.organization_id
AND dis.dist_code_combination_id = gcc.code_combination_id;
DROP VIEW JEDOX.JEDOX_VIEW_INV_HEADER;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_INV_HEADER
(
INVOICE_NUM,
CREATION_DATE,
NAME,
PO,
COSTCENTER
)
AS
SELECT DISTINCT inv.invoice_num,
inv.creation_date,
hou.name,
pha.segment1 PO,
gcc.segment5 "COSTCENTER"
FROM apps.ap_invoices_all inv,
apps.hr_operating_units hou,
apps.po_headers_all pha,
apps.ap_invoice_distributions_all dis,
apps.gl_code_combinations gcc
WHERE inv.invoice_id = dis.invoice_id
AND inv.po_header_id = pha.po_header_id(+)
AND inv.org_id = hou.organization_id
AND dis.dist_code_combination_id = gcc.code_combination_id;
DROP VIEW JEDOX.JEDOX_VIEW_ITEMLIST;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_ITEMLIST
(
INVENTORY_ITEM_ID,
"Inventory Item Code",
"INVENTORY ITEM DESC"
)
AS
SELECT DISTINCT
msi.Inventory_Item_ID,
msi.segment1 "Inventory Item Code",
msi.DESCRIPTION "INVENTORY ITEM DESC"
FROM APPS.mtl_system_items_b msi;
DROP VIEW JEDOX.JEDOX_VIEW_ITEM_LINE;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_ITEM_LINE
(
"Company Code",
"Expense Item",
"Project Type",
"Project Code",
"Cost Center",
"Currency",
"0000",
"Expense Description",
GL_CHART_DESC,
ENABLED_FLAG,
TYPE
)
AS
SELECT gcc.segment1 "Company Code",
gcc.segment2 "Expense Item",
gcc.segment3 "Project Type",
gcc.segment4 "Project Code",
gcc.segment5 "Cost Center",
gcc.segment6 "Currency",
gcc.segment7 "0000",
ffvt.description "Expense Description",
apps.GL_FLEXFIELDS_PKG.get_concat_description (
gcc.chart_of_accounts_id,
gcc.code_combination_id) gl_chart_Desc --Gl Account Description
,
gcc.enabled_flag,
CASE
WHEN gcc.account_type = 'A' THEN 'Asset'
WHEN gcc.account_type = 'E' THEN 'Expense'
WHEN gcc.account_type = 'R' THEN 'Revenue'
WHEN gcc.account_type = 'O' THEN 'Equity'
WHEN gcc.account_type = 'L' THEN 'Liability'
END TYPE
FROM apps.fnd_flex_values ffv,
apps.fnd_flex_values_tl ffvt,
apps.fnd_flex_value_sets ffvs,
apps.fnd_id_flex_segments fifs,
apps.fnd_flex_hierarchies_vl ffhv,
apps.gl_code_combinations gcc
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND UPPER (fifs.application_column_name) = 'SEGMENT2'
AND fifs.id_flex_code = 'GL#'
AND ffv.structured_hierarchy_level = ffhv.hierarchy_ID(+)
AND gcc.segment2 = ffv.flex_value(+);
DROP VIEW JEDOX.JEDOX_VIEW_LEDGER_TRIALBALANCE;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_LEDGER_TRIALBALANCE
(
ORG_ID,
PO_HEADER_ID,
PO_NUMBER,
LINE_NUM,
ITEM_ID,
ITEM_DESCRIPTION,
SOURCE_NAME,
CODE_COMBINATION_ID,
COMAPNY,
"Expense Item",
"Project Type",
"Project Code",
"Cost Center",
"Currency",
"Other",
DR,
CR,
BALANCE,
PERIOD_NAME
)
AS
SELECT DISTINCT
COALESCE (pha.org_id, 0) AS org_id,
COALESCE (pla.PO_HEADER_ID, 0) AS PO_HEADER_ID,
COALESCE (pha.segment1, 'NULL') AS PO_NUMBER,
COALESCE (pla.line_num, 0) AS line_num,
COALESCE (pla.item_id, 0) AS item_id,
COALESCE (pla.ITEM_DESCRIPTION, 'NULL') AS ITEM_DESCRIPTION,
COALESCE (gjh.je_source, 'NULL') AS source_name,
COALESCE (pda.CODE_COMBINATION_ID, 0) AS CODE_COMBINATION_ID,
COALESCE (GCC.SEGMENT1, 'NULL') AS comapny, -- comapny
COALESCE (GCC.SEGMENT2, 'NULL') AS "Expense Item", --Expense Item
COALESCE (GCC.SEGMENT3, 'NULL') AS "Project Type", --Project Type
COALESCE (GCC.SEGMENT4, 'NULL') AS "Project Code", ---Project Code
COALESCE (GCC.SEGMENT5, 'NULL') AS "Cost Center", --Cost Center
COALESCE (GCC.SEGMENT6, 'NULL') AS "Currency", --Currency
COALESCE (GCC.SEGMENT7, 'NULL') AS "Other",
COALESCE ((pla.unit_price * pla.quantity), 0) AS "DR",
0 AS "CR",
0 AS BALANCE,
COALESCE (GJH.PERIOD_NAME, 'NULL') AS PERIOD_NAME
FROM apps.GL_JE_BATCHES GJB,
apps.GL_JE_HEADERS GJH,
apps.GL_JE_LINES GJL,
apps.GL_CODE_COMBINATIONS GCC,
apps.GL_IMPORT_REFERENCES GIR,
apps.XLA_AE_LINES XAL,
apps.XLA_AE_HEADERS XAH,
XLA.XLA_TRANSACTION_ENTITIES XTE,
apps.AP_INVOICES_ALL AIA,
apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_distributions_all pda,
apps.ap_invoice_distributions_all aid
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GCC.CODE_COMBINATION_ID = pda.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
AND XTE.ENTITY_ID = XAH.ENTITY_ID
AND AIA.INVOICE_ID(+) = XTE.SOURCE_ID_INT_1
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND pha.po_header_id = pda.po_header_id
AND aid.PO_DISTRIBUTION_ID IS NOT NULL
AND aid.po_distribution_id = pda.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pda.po_line_id
-- AND gjh.PERIOD_NAME='AUG-18'
-- AND pla.PO_HEADER_ID=92758
AND GJH.JE_SOURCE = 'Payables'
--AND GCC.SEGMENT5 = '107'
--AND GCC.SEGMENT2 = '532050'
--AND GCC.SEGMENT3 = 'PRJ'
-- AND GCC.SEGMENT4 = 'P047'
--AND pha.segment1 ='5067'
ORDER BY line_num;
DROP VIEW JEDOX.JEDOX_VIEW_PO_DETAILS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PO_DETAILS
(
"PO Number",
LINE_NUM,
ITEM_DESCRIPTION,
"Item Code",
"Line Amount"
)
AS
SELECT DISTINCT
pha.segment1 "PO Number",
pla.line_num,
pla.ITEM_DESCRIPTION,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7 "Item Code",
(pla.unit_price * pla.quantity) "Line Amount"
FROM apps.po_headers_all pha,
apps.po_distributions_all pda,
apps.po_lines_all pla,
apps.gl_code_combinations gcc
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pda.po_header_id
AND pla.po_line_id = pda.po_line_id
AND pda.code_combination_id = gcc.code_combination_id;
DROP VIEW JEDOX.JEDOX_VIEW_PO_HEADER;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PO_HEADER
(
CREATION_DATE,
"PO Number",
"Cost Center",
NAME
)
AS
SELECT DISTINCT pha.creation_date,
pha.segment1 "PO Number",
gcc.segment5 "Cost Center",
org.name
FROM apps.po_headers_all pha,
apps.po_distributions_all pda,
apps.po_lines_all pla,
apps.gl_code_combinations gcc,
apps.hr_all_organization_units org
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pda.po_header_id
AND pla.po_line_id = pda.po_line_id
AND pha.org_id = org.organization_id
AND pda.po_distribution_id = gcc.code_combination_id;
DROP VIEW JEDOX.JEDOX_VIEW_PROJECTS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PROJECTS
(
PROJECT_CODE,
PROJECT_NAME,
COST_CENTER,
COST_CENTER_NAME,
BUDGET_AMOUNT,
PROJECT_TYPE
)
AS
SELECT "PROJECT_CODE",
"PROJECT_NAME",
"COST_CENTER",
"COST_CENTER_NAME",
"PROJECT_AMOUNT",
"PROJECT_TYPE"
FROM ((SELECT '0000' PROJECT_CODE,
'No Project' PROJECT_NAME,
NULL COST_CENTER,
NULL COST_CENTER_NAME,
NULL PROJECT_AMOUNT,
NULL PROJECT_TYPE
FROM DUAL)
UNION ALL
( SELECT SEGMENT4 PROJECT_CODE,
NOMINAL PROJECT_NAME,
SEGMENT5 COST_CENTER,
COST_CENTER COST_CENTER_NAME,
SUM (NVL (BEGIN_BALANCE_NET, 0)) PROJECT_AMOUNT,
CASE
WHEN SEGMENT4 LIKE '%P%' THEN 'Strategic Project'
WHEN SEGMENT4 LIKE '%R%' THEN 'Operational Project'
WHEN SEGMENT4 LIKE '%J%' THEN 'Program'
END PROJECT_TYPE
FROM (SELECT GLBAL1.PERIOD_YEAR,
GLBAL1.CODE_COMBINATION_ID,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
TRIM (CO.CO_DESCRIPTION)
COMPANY,
TRIM (ACCT.ACCT_DESCRIPTION)
ACCOUNT,
TRIM (PORT.PORT_DESCRIPTION)
PORTFOLIO,
TRIM (
NVL (NOM.NOM_DESCRIPTION,
PRJ.PRJ_DESCRIPTION))
NOMINAL,
TRIM (CC.CC_DESCRIPTION)
COST_CENTER,
TRIM (CURR.CURR_DESCRIPTION)
CURRENCY,
TRIM (FUTURE.FUT_DESCRIPTION)
FUTURE_USE,
NVL (GLBAL1.BEGIN_BALANCE_DR, 0)
- NVL (GLBAL1.BEGIN_BALANCE_CR, 0)
AS BEGIN_BALANCE_NET,
--NVL (GLBAL1.PERIOD_NET_DR, 0) - NVL (GLBAL1.PERIOD_NET_CR, 0) AS PERIOD_NET,
CASE
WHEN oap.period_year IS NOT NULL THEN 'Y'
ELSE 'N'
END
open_flag,
ROW_NUMBER ()
OVER (
PARTITION BY GLBAL1.PERIOD_YEAR,
GLBAL1.CODE_COMBINATION_ID
ORDER BY glbal1.PERIOD_NUM DESC)
LAST_REC
FROM APPS.GL_BALANCES GLBAL1,
APPS.gl_code_combinations gcc,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') PRJ_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value LIKE 'P%'
AND ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_NOMINAL') PRJ,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') CC_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_COST CENTER') CC,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') NOM_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_NOMINAL') NOM,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') ACCT_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_ACCOUNT') ACCT,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') PORT_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_PORTFOLIO') PORT,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') CO_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
applsys.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_COMPANY') CO,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') CURR_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
apps.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_CURRENCY') CURR,
(SELECT ffvv1.flex_value,
REPLACE (ffvv1.description, '&', 'and') FUT_DESCRIPTION
FROM apps.fnd_flex_values_vl ffvv1,
apps.fnd_flex_Values ffv,
apps.fnd_flex_value_sets ffvs
WHERE ffvv1.flex_value = ffv.flex_value
AND ffvv1.flex_value_set_id =
ffv.flex_value_set_id
AND ffvv1.flex_value_set_id =
ffvs.flex_value_set_id
AND ffvs.flex_value_set_name =
'ADRPBF_GL_FUTURE_USE') FUTURE,
( SELECT period_year
FROM INV.ORG_ACCT_PERIODS oap
WHERE open_flag = 'Y'
GROUP BY period_year) oap
WHERE GLBAL1.CODE_COMBINATION_ID =
gcc.CODE_COMBINATION_ID
AND GLBAL1.ACTUAL_FLAG IN ('B')
AND GLBAL1.CURRENCY_CODE = 'AED'
AND GLBAL1.LEDGER_ID = 2021
AND gcc.CHART_OF_ACCOUNTS_ID = 50328
AND gcc.segment4 = PRJ.flex_value(+)
AND gcc.segment4 = NOM.flex_value(+)
AND gcc.segment5 = CC.flex_value(+)
AND gcc.segment2 = ACCT.flex_value(+)
AND gcc.segment3 = PORT.flex_value(+)
AND gcc.segment1 = CO.flex_value(+)
AND gcc.segment6 = CURR.flex_value(+)
AND gcc.segment7 = FUTURE.flex_value(+)
AND glbal1.period_year = oap.period_year(+)
AND ( gcc.SEGMENT4 LIKE ('%P%')
OR gcc.SEGMENT4 LIKE ('%R%')
OR gcc.SEGMENT4 LIKE ('%J%')) -- Order by GLBAL1.CODE_COMBINATION_ID, GLBAL1.PERIOD_YEAR, GLBAL1.PERIOD_NUM
)
WHERE BEGIN_BALANCE_NET <> 0
AND last_rec = 1
AND (open_flag = 'Y' OR SEGMENT4 LIKE ('%J%'))
GROUP BY SEGMENT4,
NOMINAL,
SEGMENT5,
COST_CENTER))
ORDER BY PROJECT_CODE;
DROP VIEW JEDOX.JEDOX_VIEW_PROJECTS_OLD;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PROJECTS_OLD
(
"Project Code",
"Project Type",
"Project Name"
)
AS
SELECT DISTINCT
GCC.SEGMENT4 "Project Code",
GCC.SEGMENT3 "Project Type",
ffvt.description "Project Name"
FROM APPS.fnd_flex_values ffv,
APPS.fnd_flex_values_tl ffvt,
APPS.fnd_flex_value_sets ffvs,
APPS.fnd_id_flex_segments fifs,
APPS.fnd_flex_hierarchies_vl ffhv,
APPS.gl_code_combinations gcc
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND UPPER (fifs.application_column_name) = 'SEGMENT4'
AND fifs.id_flex_code = 'GL#'
AND ffv.structured_hierarchy_level = ffhv.hierarchy_ID(+)
AND gcc.segment4 = ffv.flex_value(+);
DROP VIEW JEDOX.JEDOX_VIEW_PR_APPROVEDDETAILS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PR_APPROVEDDETAILS
(
LAST_UPDATE_DATE,
REQUISITION_LINE_ID,
REQUISITION_HEADER_ID,
ITEM_DESCRIPTION,
UNIT_PRICE,
QUANTITY,
DELIVER_TO_LOCATION_ID,
ITEM_ID,
CATEGORY_ID,
SOURCE_TYPE_CODE,
SEGMENT1,
REQUEST_ID,
APPROVED_DATE,
BASE_UNIT_PRICE,
LINE_NUM,
NEED_BY_DATE,
ATTRIBUTE1,
ORG_ID,
AUTHORIZATION_STATUS
)
AS
SELECT po_requisition_headers_all.LAST_UPDATE_DATE,
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID,
PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID,
PO_REQUISITION_LINES_ALL.ITEM_DESCRIPTION,
PO_REQUISITION_LINES_ALL.UNIT_PRICE,
PO_REQUISITION_LINES_ALL.QUANTITY,
PO_REQUISITION_LINES_ALL.DELIVER_TO_LOCATION_ID,
PO_REQUISITION_LINES_ALL.ITEM_ID,
PO_REQUISITION_LINES_ALL.CATEGORY_ID,
PO_REQUISITION_LINES_ALL.SOURCE_TYPE_CODE,
po_requisition_headers_all.segment1,
po_requisition_headers_all.request_id,
po_requisition_headers_all.approved_date,
PO_REQUISITION_LINES_ALL.base_unit_price,
po_requisition_lines_all.line_num,
po_requisition_lines_all.need_by_date,
po_requisition_lines_all.attribute1,
po_requisition_lines_all.org_id,
po_requisition_headers_all.authorization_status
FROM apps.PO_REQUISITION_LINES_ALL
INNER JOIN apps.po_requisition_headers_all
ON PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
po_requisition_headers_all.requisition_header_id
AND po_requisition_headers_all.authorization_status IN
('APPROVED', 'SYSTEM_SAVED')
WHERE TO_CHAR (SYSDATE, 'yy') =
TO_CHAR (po_requisition_headers_all.LAST_UPDATE_DATE, 'yy')
AND TO_CHAR (SYSDATE, 'mm') =
TO_CHAR (po_requisition_headers_all.LAST_UPDATE_DATE, 'mm')
ORDER BY po_requisition_headers_all.LAST_UPDATE_DATE DESC;
DROP VIEW JEDOX.JEDOX_VIEW_PR_APPROVED_ERRORS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PR_APPROVED_ERRORS
(
CREATION_DATE,
ITEM_DESCRIPTION,
QUANTITY,
UNIT_PRICE,
PREPARER_ID,
REQ_NUMBER_SEGMENT1,
HEADER_DESCRIPTION,
ITEM_ID,
CHARGE_ACCOUNT_SEGMENT2,
CHARGE_ACCOUNT_SEGMENT5,
CATEGORY_ID,
SUGGESTED_VENDOR_NAME,
NEED_BY_DATE,
PREPARER_NAME,
LINE_NUM
)
AS
SELECT creation_date,
item_description,
quantity,
unit_price,
preparer_id,
req_number_segment1,
header_description,
item_id,
charge_account_segment2,
charge_account_segment5,
category_id,
suggested_vendor_name,
need_by_date,
preparer_name,
line_num
FROM apps.PO_REQUISITIONS_INTERFACE_ALL
WHERE process_flag = 'ERROR';
DROP VIEW JEDOX.JEDOX_VIEW_PUR_REQ_DETAILS;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PUR_REQ_DETAILS
(
"REQ NUMBER",
PR_LINE_NUM,
ITEM_DESCRIPTION,
"Item Code",
AMOUNT,
ORGANIZATION
)
AS
SELECT DISTINCT
PRH2.SEGMENT1 "REQ NUMBER",
PRL2.LINE_NUM PR_LINE_NUM,
PRL2.ITEM_DESCRIPTION,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7 "Item Code",
(PRL2.QUANTITY * prl2.unit_price) Amount,
hout.NAME Organization
FROM APPS.PO_REQUISITION_HEADERS_ALL PRH2,
APPS.PO_REQUISITION_LINES_ALL PRL2,
apps.hr_all_organization_units_tl hout,
apps.hr_operating_units hou,
apps.po_req_distributions_all prd,
apps.gl_code_combinations gcc
WHERE 1 = 1
AND hou.organization_id = prh2.org_id
AND hout.organization_id(+) = prl2.destination_organization_id
AND hout.LANGUAGE(+) = USERENV ('LANG')
AND NVL (prh2.cancel_flag, 'N') <> 'Y'
AND NVL (prL2.cancel_flag, 'N') <> 'Y'
AND PRL2.REQUISITION_HEADER_ID = PRH2.REQUISITION_HEADER_ID
AND prl2.requisition_line_id = prd.requisition_line_id
AND prd.code_combination_id = gcc.code_combination_id;
DROP VIEW JEDOX.JEDOX_VIEW_PUR_REQ_HEADER;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_PUR_REQ_HEADER
(
"REQ NUMBER",
"DATE",
"COST CENTER",
NAME
)
AS
SELECT prh.segment1 "REQ NUMBER",
prh.creation_date "DATE",
gcc.segment5 "COST CENTER",
org.name
FROM apps.po_requisition_headers_all prh,
apps.hr_all_organization_units org,
apps.po_requisition_lines_all prl,
apps.po_req_distributions_all prd,
apps.gl_code_combinations gcc
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prd.requisition_line_id = prl.requisition_line_id
AND prd.code_combination_id = gcc.code_combination_id
AND prh.org_id = org.organization_id;
DROP VIEW JEDOX.JEDOX_VIEW_USER_LOCATION;
/* Formatted on 12/04/2021 2:35:46 PM (QP5 v5.362) */
CREATE OR REPLACE FORCE VIEW JEDOX.JEDOX_VIEW_USER_LOCATION
(
USER_NAME,
LOCATION_ID,
LOCATION_CODE
)
AS
SELECT usr.user_name, loc.LOCATION_ID, loc.LOCATION_CODE
FROM APPLSYS.fnd_profile_option_values v,
APPLSYS.fnd_user usr,
HR.HR_LOCATIONS_ALL loc
WHERE v.profile_option_id = 4108
AND usr.user_id = v.level_value
AND loc.INVENTORY_ORGANIZATION_ID = 103
AND v.profile_option_value = TO_CHAR (loc.LOCATION_ID)
AND usr.end_date IS NULL
ORDER BY usr.user_name;
DROP TABLE JEDOX.XXJEDOX_PR_ATTACHMENTS CASCADE CONSTRAINTS;
CREATE TABLE JEDOX.XXJEDOX_PR_ATTACHMENTS
(
HEADER_ID NUMBER,
LINE_ID NUMBER,
JEDOX_PR_NUMBER VARCHAR2(150 BYTE) NOT NULL,
ORA_PR_HDR_ID NUMBER,
JEDOX_PR_FILE_BLOB BLOB NOT NULL,
JEDOX_PR_FILE_NAME VARCHAR2(1000 BYTE) NOT NULL,
JEDOX_PR_FILE_TYPE VARCHAR2(1000 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2(150 BYTE),
ATTRIBUTE1 VARCHAR2(400 BYTE),
ATTRIBUTE2 VARCHAR2(400 BYTE),
ATTRIBUTE3 VARCHAR2(400 BYTE),
ATTRIBUTE4 VARCHAR2(400 BYTE),
ATTRIBUTE5 VARCHAR2(400 BYTE),
ATTRIBUTE6 VARCHAR2(400 BYTE),
ATTRIBUTE7 VARCHAR2(400 BYTE),
ATTRIBUTE8 VARCHAR2(400 BYTE),
ATTRIBUTE9 VARCHAR2(400 BYTE),
ATTRIBUTE10 VARCHAR2(400 BYTE),
ATTRIBUTE11 VARCHAR2(400 BYTE),
ATTRIBUTE12 VARCHAR2(400 BYTE),
ATTRIBUTE13 VARCHAR2(400 BYTE),
ATTRIBUTE14 VARCHAR2(400 BYTE),
ATTRIBUTE15 VARCHAR2(400 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
)
LOB (JEDOX_PR_FILE_BLOB) STORE AS BASICFILE (
TABLESPACE ADPF
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING)
TABLESPACE ADPF
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE;
export
DOMAIN_HOME=/u01/app/EBS/fs2/FMW_Home/user_projects/domains/EBS_domain
cd $DOMAIN_HOME/bin
. ./setDomainEnv.sh
java weblogic.WLST
from weblogic.security.internal import BootProperties
BootProperties.load("/u01/app/EBS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/security/boot.properties",
false)
prop = BootProperties.getBootProperties()
print "username: " + prop.getOneClient()
print "password: " + prop.getTwoClient()
