[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select task_id
from pa_project_asset_assignments
where project_id = x_project_id
and task_id = x_task_id ;
select task_id
from pa_project_asset_assignments
where project_id = x_project_id
and task_id = x_top_task_id ;
select task_id
from pa_project_asset_assignments
where project_id = x_project_id ;
SELECT
mtlsi.asset_category_id
INTO
asset_category_id
FROM
ap_invoice_distributions_all apid,
po_distributions pod,
po_lines pol,
financials_system_parameters fsp,
mtl_system_items mtlsi
WHERE
apid.invoice_id = x_doc_header_id
AND apid.invoice_distribution_id = x_doc_dist_id
AND apid.po_distribution_id = pod.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND pol.item_id = mtlsi.inventory_item_id
AND mtlsi.organization_id = fsp.inventory_organization_id;
SELECT
mtlsi.asset_category_id
INTO
asset_category_id
FROM
po_distributions pod,
po_lines pol,
financials_system_parameters fsp,
mtl_system_items mtlsi
WHERE
pod.po_header_id = x_doc_header_id
AND pod.po_distribution_id = x_doc_line_num
AND pod.po_line_id = pol.po_line_id
AND pol.item_id = mtlsi.inventory_item_id
AND mtlsi.organization_id = fsp.inventory_organization_id;
SELECT
paa.project_asset_id,
ppa.asset_category_id
FROM
pa_project_asset_assignments paa,
pa_project_assets ppa
WHERE
paa.project_id = x_project_id
AND ppa.project_asset_type(+) = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND ppa.capital_event_id(+) IS NOT NULL
AND ppa.capital_event_id(+) = x_capital_event_id
AND ppa.capital_hold_flag(+) = 'N'
AND ppa.project_asset_id(+) = paa.project_asset_id
AND paa.task_id = x_grp_level_task_id;
SELECT
depreciation_expense_ccid
INTO
x_depreciation_expense_ccid
FROM
pa_project_assets
WHERE
project_asset_id = x_project_asset_id;
UPDATE
pa_project_assets ppa
SET
ppa.reverse_flag = 'S'
WHERE
ppa.project_id = x_project_id
AND ppa.capital_event_id = NVL(x_capital_event_id, ppa.capital_event_id)
AND ppa.reverse_flag = 'Y';
SELECT
'Exist'
INTO
dummy
FROM sys.dual
WHERE exists
(SELECT
'Yes'
FROM
pa_project_asset_lines pal
WHERE
pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
AND NOT EXISTS
( SELECT
'This Line was adjusted before'
FROM
pa_project_asset_lines ppal
WHERE
ppal.rev_proj_asset_line_id = pal.project_asset_line_id
)
AND pal.project_asset_id NOT IN
( SELECT
project_asset_id
FROM
pa_project_assets pas
WHERE
pas.reverse_flag = 'S'
AND pas.project_id = pal.project_id
)
UNION
SELECT
'Yes'
FROM
pa_project_asset_lines pal
WHERE
pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
AND pal.transfer_status_code <> 'T'
AND pal.rev_proj_asset_line_id IS NULL
AND pal.project_asset_id IN
( SELECT
project_asset_id
FROM
pa_project_assets pas
WHERE
pas.reverse_flag = 'S'
AND pas.project_id = pal.project_id
)
);
SELECT
'Exist'
INTO
dummy
FROM sys.dual
WHERE exists
(SELECT
'Yes'
FROM
pa_project_asset_lines pal
WHERE
pal.project_asset_line_detail_id = x_proj_asset_line_detail_id
AND pal.transfer_status_code <> 'P'
);
PROCEDURE update_line_details
(x_proj_asset_line_detail_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
UPDATE
pa_project_asset_line_details
SET
reversed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
project_asset_line_detail_id =
x_proj_asset_line_detail_id;
END update_line_details;
PROCEDURE update_expenditure_items
(x_proj_asset_line_detail_id IN NUMBER,
x_revenue_distributed_flag IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
UPDATE
pa_expenditure_items_all pei
SET
revenue_distributed_flag = x_revenue_distributed_flag,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
pei.expenditure_item_id IN
( SELECT
expenditure_item_id
FROM
pa_project_asset_line_details pald
WHERE
project_asset_line_detail_id =
x_proj_asset_line_detail_id
GROUP BY expenditure_item_id
);
END update_expenditure_items;
PROCEDURE update_asset_cost
(x_project_asset_id IN NUMBER,
x_grouped_cip_cost IN NUMBER,
x_capitalized_cost IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
UPDATE
pa_project_assets ppa
SET
ppa.grouped_cip_cost = NVL(ppa.grouped_cip_cost,0) +
x_grouped_cip_cost,
ppa.capitalized_cost = NVL(ppa.capitalized_cost,0) +
x_capitalized_cost
WHERE
ppa.project_asset_id = x_project_asset_id;
END update_asset_cost;
x_invoice_updated_by IN NUMBER,
x_invoice_id IN NUMBER,
x_payables_batch_name IN VARCHAR2,
x_ap_dist_line_number IN Number,
x_invoice_distribution_id IN Number, -- R12 new
x_orig_asset_id IN Number,
x_line_type IN VARCHAR2,
x_capital_event_id IN NUMBER,
x_retirement_cost_type IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
project_asset_line_id NUMBER;
SELECT pa_project_asset_lines_s.nextval
INTO project_asset_line_id
FROM sys.dual;
INSERT INTO pa_project_asset_lines(
project_asset_line_id,
description,
project_asset_id,
project_id,
task_id,
cip_ccid,
asset_cost_ccid,
original_asset_cost,
current_asset_cost,
project_asset_line_detail_id,
gl_date,
transfer_status_code,
transfer_rejection_reason,
amortize_flag,
asset_category_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
rev_proj_asset_line_id,
rev_from_proj_asset_line_id,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
invoice_date,
invoice_created_by,
invoice_updated_by,
invoice_id,
payables_batch_name,
ap_distribution_line_number,
invoice_distribution_id, -- R12 new
original_asset_id
,line_type
,capital_event_id
,retirement_cost_type
,org_id
)
SELECT
project_asset_line_id,
UPPER(x_description),
x_project_asset_id,
x_project_id,
x_task_id,
x_cip_ccid,
x_asset_cost_ccid,
x_original_asset_cost,
x_current_asset_cost,
x_project_asset_line_detail_id,
x_gl_date,
x_transfer_status_code,
x_transfer_rejection_reason,
x_amortize_flag,
x_asset_category_id,
sysdate,
x_last_updated_by,
x_created_by,
sysdate,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_rev_proj_asset_line_id,
x_rev_from_proj_asset_line_id,
x_invoice_number,
x_vendor_number,
x_po_vendor_id,
x_po_number,
x_invoice_date,
x_invoice_created_by,
x_invoice_updated_by,
x_invoice_id,
x_payables_batch_name,
x_ap_dist_line_number,
x_invoice_distribution_id, -- R12 new
x_orig_asset_id
,x_line_type
,x_capital_event_id
,x_retirement_cost_type
,L_org_id
FROM
sys.dual;
SELECT
pal.project_asset_line_detail_id
FROM
pa_project_asset_lines pal
WHERE
pal.project_id+0 = x_project_id
AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
AND pal.project_asset_id IN
( SELECT
project_asset_id
FROM
pa_project_assets pas
WHERE
pas.reverse_flag = 'S'
AND pas.project_id = pal.project_id
)
AND pal.transfer_status_code||'' = 'T'
AND pal.rev_proj_asset_line_id is NULL
AND NOT EXISTS
( SELECT
'This Line was adjusted before'
FROM
pa_project_asset_lines ppal
WHERE
ppal.rev_proj_asset_line_id = pal.project_asset_line_id
)
GROUP by project_asset_line_detail_id;
-- Cursor for selecting all the project_asset_lines
-- which are candidates for reversal for the given
-- project_asset_line_detail_id for all the assets
CURSOR selprojassetlines(proj_asset_line_detail_id NUMBER) IS
SELECT
project_asset_line_id,
description,
project_asset_id,
project_id,
task_id,
cip_ccid,
asset_cost_ccid,
original_asset_cost,
current_asset_cost,
project_asset_line_detail_id,
gl_date,
transfer_status_code,
amortize_flag,
asset_category_id,
request_id,
rev_from_proj_asset_line_id,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
invoice_date,
invoice_created_by,
invoice_updated_by,
invoice_id,
payables_batch_name,
ap_distribution_line_number,
invoice_distribution_id, -- R12 new
original_asset_id
,line_type
,capital_event_id
,retirement_cost_type
FROM
pa_project_asset_lines pal
WHERE
pal.project_id+0 = x_project_id
AND pal.rev_proj_asset_line_id is NULL /* Added this for the bug 3989536 */
AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
AND pal.project_asset_line_detail_id = proj_asset_line_detail_id
AND pal.transfer_status_code||'' = 'T'
AND pal.project_asset_id IN
( SELECT
project_asset_id
FROM
pa_project_assets pas
WHERE
pas.reverse_flag = 'S'
AND pas.project_id = pal.project_id
)
AND NOT EXISTS
( SELECT
'This Line was adjusted before'
FROM
pa_project_asset_lines ppal
WHERE
ppal.rev_proj_asset_line_id = pal.project_asset_line_id
);
SELECT meaning
INTO x_translated_reversal
FROM pa_lookups
WHERE lookup_type = 'TRANSLATION' and
lookup_code = 'REVERSAL';
assetlinerec.invoice_updated_by,
assetlinerec.invoice_id,
assetlinerec.payables_batch_name,
assetlinerec.ap_distribution_line_number,
assetlinerec.invoice_distribution_id, --R12 new
assetlinerec.original_asset_id,
assetlinerec.line_type,
assetlinerec.capital_event_id,
assetlinerec.retirement_cost_type,
x_err_stage,
x_err_code);
update_asset_cost
(assetlinerec.project_asset_id,
-assetlinerec.current_asset_cost,
0, --- capitalized_cost
x_err_stage,
x_err_code);
assetlinerec.invoice_updated_by,
assetlinerec.invoice_id,
assetlinerec.payables_batch_name,
assetlinerec.ap_distribution_line_number,
assetlinerec.invoice_distribution_id, --R12 new
assetlinerec.original_asset_id,
assetlinerec.line_type,
assetlinerec.capital_event_id,
assetlinerec.retirement_cost_type,
x_err_stage,
x_err_code);
-- and update the cip_cost to Zero
IF ( cdl_fully_reversible ) THEN
update_line_details
(detailidrec.project_asset_line_detail_id,
x_err_stage,
x_err_code);
update_expenditure_items
(detailidrec.project_asset_line_detail_id,
'N',
x_err_stage,
x_err_code);
UPDATE
pa_project_assets
SET
reverse_flag = 'N',
/* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
--Commenting out this line since it is counter to business flow and erases key data (DPIS)
--The CAPITAL_HOLD_FLAG will now be used to prevent new asset line generation
--date_placed_in_service = NULL,
/* End of Automatic asset capitalization changes */
reversal_date = sysdate,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
reverse_flag = 'S';
SELECT
paa.project_asset_id
INTO
x_project_asset_id
FROM
pa_project_asset_assignments paa
WHERE
paa.project_id = x_project_id
AND paa.task_id = 0;
SELECT
paa.project_asset_id
INTO
x_project_asset_id
FROM
pa_project_asset_assignments paa
WHERE
paa.project_id = x_project_id
AND paa.task_id =
(SELECT
task_id
FROM
pa_tasks
WHERE
parent_task_id is null --- top task
CONNECT BY task_id = PRIOR parent_task_id
START WITH task_id = x_task_id
);
PROCEDURE delete_proj_asset_line
(x_project_asset_line_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
DELETE
pa_project_asset_lines
WHERE
project_asset_line_id = x_project_asset_line_id;
DELETE PA_MC_PRJ_AST_LINES_ALL
WHERE project_asset_line_id = x_project_asset_line_id;
END delete_proj_asset_line;
PROCEDURE delete_proj_asset_line_details
(x_project_asset_line_detail_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
DELETE
pa_project_asset_line_details
WHERE
project_asset_line_detail_id = x_project_asset_line_detail_id;
DELETE PA_MC_PRJ_AST_LINE_DTLS
WHERE PROJ_ASSET_LINE_DTL_UNIQ_ID = x_project_asset_line_detail_id;
END delete_proj_asset_line_details;
PROCEDURE delete_asset_lines
(x_project_id IN NUMBER,
x_capital_event_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
-- This cursor will return all the project asset line which are eligible
-- for deletion
CURSOR selassetlines IS
SELECT
ppal.project_asset_line_detail_id,
ppal.project_asset_line_id,
ppal.project_asset_id,
ppal.current_asset_cost
FROM
pa_project_asset_lines ppal
WHERE
ppal.rev_proj_asset_line_id IS NULL
AND ppal.transfer_status_code <> 'T'
AND ppal.capital_event_id = NVL(x_capital_event_id, ppal.capital_event_id)
AND ppal.project_asset_line_detail_id IN
(SELECT
/*+ INDEX (pal PA_PROJECT_ASSET_LINES_N2) */pal.project_asset_line_detail_id
FROM
pa_project_asset_lines pal
WHERE
pal.project_id = x_project_id
AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
AND pal.transfer_status_code <> 'T'
GROUP BY pal.project_asset_line_detail_id
HAVING SUM(current_asset_cost) =
( SELECT
SUM(cip_cost)
FROM pa_project_asset_line_details pald
WHERE pald.project_asset_line_detail_id = pal.project_asset_line_detail_id
)
)
ORDER BY ppal.project_asset_line_detail_id;
update_detail_lines BOOLEAN;
update_detail_lines,
x_err_stage,
x_err_code);
update_expenditure_items
(assetlinerec.project_asset_line_detail_id,
'N',
x_err_stage,
x_err_code);
IF (update_detail_lines) THEN
-- update the reversed_flag = 'Y'
update_line_details
(assetlinerec.project_asset_line_detail_id,
x_err_stage,
x_err_code);
pa_debug.debug('delete_asset_lines: ' || '. Deleting detail for detail line id = ' ||
to_char(assetlinerec.project_asset_line_detail_id));
-- delete all the details
delete_proj_asset_line_details
(assetlinerec.project_asset_line_detail_id,
x_err_stage,
x_err_code);
-- now delete the projec asset line
delete_proj_asset_line
(assetlinerec.project_asset_line_id,
x_err_stage,
x_err_code);
update_asset_cost
(assetlinerec.project_asset_id,
-assetlinerec.current_asset_cost,
0, --- capitalized_cost
x_err_stage,
x_err_code);
END delete_asset_lines;
SELECT pa_proj_asset_line_dtls_uniq_s.nextval
INTO l_proj_asset_line_dtl_uniq_id
FROM sys.dual;
INSERT INTO pa_project_asset_line_details(
proj_asset_line_dtl_uniq_id,
expenditure_item_id,
line_num,
project_asset_line_detail_id,
cip_cost,
reversed_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id
)
SELECT
l_proj_asset_line_dtl_uniq_id,
x_expenditure_item_id,
x_line_num,
x_project_asset_line_detail_id,
x_cip_cost,
x_reversed_flag,
sysdate,
x_last_updated_by,
x_created_by,
sysdate,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id
FROM
sys.dual;
x_invoice_updated_by OUT NOCOPY NUMBER,
x_payables_batch_name OUT NOCOPY VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER) IS
------bring VI info
begin
x_err_code := 0;
select rtrim(API.invoice_num),rtrim(POV.segment1),
POV.employee_id,
API.vendor_id,rtrim(upper(POH.segment1)),
API.invoice_date,API.created_by,
API.last_updated_by,
APB.batch_name
into x_invoice_num, x_vendor_number,
x_employee_id,
x_po_vendor_id, x_po_number,
x_invoice_date, x_invoice_created_by,
x_invoice_updated_by,
x_payables_batch_name
from ap_invoices API, ap_invoice_distributions APID, ap_batches APB,
po_vendors POV, po_headers POH ,po_distributions POD
where API.invoice_id = x_invoice_id
and APID.invoice_distribution_id = x_ap_inv_dist_id
and APID.invoice_id = API.invoice_id
and APID.po_distribution_id = POD.po_distribution_id(+)
and POD.po_header_id = POH.po_header_id(+)
and POV.vendor_id = API.vendor_id
and API.batch_id = APB.batch_id(+);
SELECT /*+ INDEX(pcdl PA_COST_DISTRIBUTION_LINES_U1) */ /* bug 5194567 added hint */
pa_faxface.get_group_level_task_id(pei.task_id,pt.top_task_id,
p_project_id) group_level_task_id,
pa_faxface.get_asset_category_id(pei.document_header_id, -- R12 new
pei.document_line_number, -- R12 new
pei.document_distribution_id, -- R12 new
pei.transaction_source) asset_category_id,
pei.system_linkage_function,
pei.document_header_id, -- R12 new
pei.document_line_number, -- R12 new
pei.document_distribution_id, -- R12 new
pet.expenditure_category,
pei.expenditure_type,
pei.non_labor_resource,
PA_CLIENT_EXTN_CIP_ACCT_OVR.CIP_ACCT_OVERRIDE
(decode(pcdl.acct_source_code,
'UPG', pcdl.dr_code_combination_id,
'EXT', pcdl.dr_code_combination_id,
'NA', pcdl.dr_code_combination_id,
NULL, decode(nvl(pei.historical_flag, 'Y'),
'Y', decode(pcdl.line_type,
'R', pcdl.dr_code_combination_id,
'D', pcdl.dr_code_combination_id,
'I', pa_xla_interface_pkg.get_post_acc_sla_ccid
(
pcdl.acct_event_id,
pcdl.transfer_status_code,
pei.transaction_source,
nvl(pei.historical_flag, 'Y'),
pcdl.expenditure_item_id,
pcdl.parent_line_num,
pcdl.line_type,
pcdl.dr_code_combination_id,
'DEBIT',
p_ledger_id
)
),
'N', decode(pcdl.line_type,
'R', decode(pa_xla_interface_pkg.get_source(pei.transaction_source,
pei.document_payment_id),
'EXT', pcdl.dr_code_combination_id,
pa_xla_interface_pkg.get_post_acc_sla_ccid
(
pcdl.acct_event_id,
pcdl.transfer_status_code,
pei.transaction_source,
nvl(pei.historical_flag, 'Y'),
decode(pa_xla_interface_pkg.get_source(pei.Transaction_Source,
pei.document_payment_id),
'PA', pcdl.expenditure_item_id,
'AP_PAY', pcdl.system_reference5,
'AP_INV', pei.document_distribution_id,
'RCV', pcdl.system_reference5,
'INV', pcdl.system_reference5,
'WIP', pcdl.system_reference5), -- distribution_id1
decode(pa_xla_interface_pkg.get_source(pei.Transaction_Source,
pei.document_payment_id),
'PA', pcdl.line_num,
'AP_PAY', pei.document_distribution_id,
'AP_INV', NULL,
'RCV', NULL,
'INV', NULL,
'WIP', NULL), -- distribution_id2
pcdl.line_type,
pcdl.dr_code_combination_id,
'DEBIT',
p_ledger_id
)
),
pa_xla_interface_pkg.get_post_acc_sla_ccid
(
pcdl.acct_event_id,
pcdl.transfer_status_code,
pei.transaction_source,
nvl(pei.historical_flag, 'Y'),
pcdl.expenditure_item_id,
decode(pcdl.line_type,
'D', pcdl.line_num,
'I', pcdl.parent_line_num),
pcdl.line_type,
pcdl.dr_code_combination_id,
'DEBIT',
p_ledger_id
) -- for I and D.
)
), -- acct_source_code NULL
pa_xla_interface_pkg.get_post_acc_sla_ccid /* for all other acct source codes */
(
pcdl.acct_event_id,
pcdl.transfer_status_code,
pei.transaction_source,
nvl(pei.historical_flag, 'Y'),
pcdl.expenditure_item_id,
pcdl.line_num,
pcdl.line_type,
pcdl.dr_code_combination_id,
'DEBIT',
p_ledger_id
)
),
pcdl.expenditure_item_id,
pcdl.line_num
) cip_ccid,
decode(p_amount_type,
'R', pcdl.amount,
decode(pcdl.line_type,
'D', pcdl.amount,
pcdl.burdened_cost)) cip_cost,
pcdl.expenditure_item_id,
pcdl.line_num,
NVL(p_cip_grouping_method_code,'ALL') cip_grouping_method_code,
DECODE(x_line_type,
'R', PA_CLIENT_EXTN_RET_COST_TYPE.RETIREMENT_COST_TYPE
(pcdl.expenditure_item_id,
pcdl.line_num,
pei.expenditure_type),
DECODE(DECODE(p_vendor_invoice_grouping_code,'G','G','E')||pei.system_linkage_function,
'EVI', DECODE(p_cip_grouping_method_code,
'EC', pet.expenditure_category,
'ECNLR',pet.expenditure_category||'+'||pei.non_labor_resource,
'ET',pei.expenditure_type,
'ETNLR',pei.expenditure_type||'+'||pei.non_labor_resource,
'CIPGCE',PA_CLIENT_EXTEN_CIP_GROUPING.CLIENT_GROUPING_METHOD(
p_project_id,
pei.task_id ,
pei.expenditure_item_id ,
pei.expenditure_id ,
pei.expenditure_type ,
pet.expenditure_category ,
pei.attribute1 ,
pei.attribute2 ,
pei.attribute3 ,
pei.attribute4 ,
pei.attribute5 ,
pei.attribute6 ,
pei.attribute7 ,
pei.attribute8 ,
pei.attribute9 ,
pei.attribute10 ,
pei.attribute_category ,
pei.transaction_source,
pcdl.system_reference2, /*bug 5523708- passing ref2,3,4*/
pcdl.system_reference3,
pcdl.system_reference4),
'ALL')||'+'||pei.document_header_id||'+'||pei.document_distribution_id,
DECODE(p_cip_grouping_method_code,
'EC',pet.expenditure_category,
'ECNLR',pet.expenditure_category||'+'||pei.non_labor_resource,
'ET',pei.expenditure_type,
'ETNLR',pei.expenditure_type||'+'||pei.non_labor_resource,
'CIPGCE',PA_CLIENT_EXTEN_CIP_GROUPING.CLIENT_GROUPING_METHOD(
p_project_id,
pei.task_id ,
pei.expenditure_item_id ,
pei.expenditure_id ,
pei.expenditure_type ,
pet.expenditure_category ,
pei.attribute1 ,
pei.attribute2 ,
pei.attribute3 ,
pei.attribute4 ,
pei.attribute5 ,
pei.attribute6 ,
pei.attribute7 ,
pei.attribute8 ,
pei.attribute9 ,
pei.attribute10 ,
pei.attribute_category ,
pei.transaction_source,
pcdl.system_reference2, /*bug 5454123- passing ref2,3,4*/
pcdl.system_reference3,
pcdl.system_reference4),
'ALL' ))) GROUPING_METHOD,
p_override_asset_assignment override_asset_assignment_flag,
p_vendor_invoice_grouping_code vendor_invoice_grouping_code,
p_project_id project_id,
pei.task_id,
pei.expenditure_id,
pei.organization_id,
pei.attribute1,
pei.attribute2,
pei.attribute3,
pei.attribute4,
pei.attribute5,
pei.attribute6,
pei.attribute7,
pei.attribute8,
pei.attribute9,
pei.attribute10,
pei.attribute_category,
pei.capital_event_id,
-- Added for bug : 4768332
pcdl.acct_event_id,
NVL(pei.transaction_source,'Projects') transaction_source
--pa_xla_interface_pkg.get_source(pei.transaction_source, pei.document_payment_id) transaction_source
FROM
pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_expenditure_types pet,
pa_tasks pt
WHERE
pcdl.expenditure_item_id = pei.expenditure_item_id
AND pei.revenue_distributed_flag||'' = 'N'
AND pei.cost_distributed_flag ='Y'
AND pcdl.transfer_status_code in ('P','A','V','T','R','G','B') /*bug5672624*/ /*Modified for bug 7142829 */
AND (pcdl.line_type = p_capital_cost_type_code
OR pcdl.line_type = DECODE(p_capital_cost_type_code,
'R', 'I',
p_capital_cost_type_code))
AND ((pcdl.line_type = 'I' and pcdl.transfer_status_code = 'G') OR
pcdl.line_type in ('R', 'D') and pcdl.transfer_status_code in ('V', 'A'))
AND ((pcdl.billable_flag = 'Y' and x_line_type = 'C') OR x_line_type = 'R') -- Bug 4860800
--AND pei.billable_flag = DECODE(x_line_type, 'C', 'Y', 'N') -- Bug 4860800
AND pei.capital_event_id IS NOT NULL
AND pei.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
AND NVL(pt.retirement_cost_flag,'N') = DECODE(x_line_type,'R','Y','N')
/* Added the below code as part of bug 7274814
Fix is to prevent net-zeroing CDLs from being processed */
AND NOT EXISTS
/* Reversed CDL*/
(SELECT NULL
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL1,
PA_COST_DISTRIBUTION_LINES_ALL CDL2
WHERE CDL1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND CDL1.LINE_NUM = PCDL.LINE_NUM
AND CDL1.REVERSED_FLAG = 'Y'
AND CDL1.BILLABLE_FLAG = 'Y'
AND CDL2.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND CDL2.LINE_NUM_REVERSED = CDL1.LINE_NUM
AND CDL2.BILLABLE_FLAG = 'Y'
AND TRUNC(CDL1.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE) /* Added for Bug 7445771 */
UNION ALL
/* Reversal CDL*/
SELECT NULL
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL1,
PA_COST_DISTRIBUTION_LINES_ALL CDL2
WHERE CDL1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND CDL1.REVERSED_FLAG = 'Y'
AND CDL1.BILLABLE_FLAG = 'Y'
AND CDL2.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND CDL2.LINE_NUM_REVERSED = CDL1.LINE_NUM
AND CDL2.LINE_NUM = PCDL.LINE_NUM
AND CDL2.BILLABLE_FLAG = 'Y'
AND TRUNC(CDL1.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE) /* Added for Bug 7445771 */
/* To check if the reversed CDL has been capitalized*/
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_ASSET_LINE_DETAILS PALD1
WHERE PALD1.EXPENDITURE_ITEM_ID = CDL1.EXPENDITURE_ITEM_ID
AND PALD1.LINE_NUM = CDL1.LINE_NUM
AND PALD1.REVERSED_FLAG ||'' = 'N')
UNION ALL
/* For ei adjustment e.g. transfer from cap to expense task
SELECT NULL
FROM PA_EXPENDITURE_ITEMS_ALL EI1
WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
/* checking if the adjusted ei has already been capitalized
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2
WHERE PALD2.EXPENDITURE_ITEM_ID =
DECODE(EI1.ADJUSTED_EXPENDITURE_ITEM_ID,NULL,- 99,
EI1.ADJUSTED_EXPENDITURE_ITEM_ID)
AND PALD2.REVERSED_FLAG ||'' = 'N')
Commented the above code and added below two selects for Bug 7445771 */
SELECT NULL
FROM PA_EXPENDITURE_ITEMS_ALL EI1
WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
AND EI1.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2,
PA_EXPENDITURE_ITEMS_ALL EI2
WHERE EI2.ADJUSTED_EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
AND PALD2.EXPENDITURE_ITEM_ID = EI2.ADJUSTED_EXPENDITURE_ITEM_ID
AND PALD2.REVERSED_FLAG||'' = 'N')
AND EXISTS (SELECT 1
FROM PA_EXPENDITURE_ITEMS_ALL EI3,
PA_COST_DISTRIBUTION_LINES_ALL PCDL2
WHERE EI3.ADJUSTED_EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
AND EI3.EXPENDITURE_ITEM_ID = PCDL2.EXPENDITURE_ITEM_ID
AND TRUNC(PCDL2.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE))
UNION ALL
SELECT NULL
FROM PA_EXPENDITURE_ITEMS_ALL EI1
WHERE EI1.EXPENDITURE_ITEM_ID = PEI.EXPENDITURE_ITEM_ID
AND EI1.NET_ZERO_ADJUSTMENT_FLAG = 'Y'
AND EI1.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_ASSET_LINE_DETAILS PALD2
WHERE PALD2.EXPENDITURE_ITEM_ID = EI1.ADJUSTED_EXPENDITURE_ITEM_ID
AND PALD2.REVERSED_FLAG||'' = 'N')
AND EXISTS (SELECT 1
FROM PA_COST_DISTRIBUTION_LINES_ALL PCDL2
WHERE PCDL2.EXPENDITURE_ITEM_ID = EI1.ADJUSTED_EXPENDITURE_ITEM_ID
AND TRUNC(PCDL2.PA_DATE) <= TRUNC(P_PA_THROUGH_DATE))
/* End of code changes for Bug 7445771 */
)
/* End of code changes done as part of bug 7274814 */
AND TRUNC(pcdl.pa_date) <= TRUNC(p_pa_through_date)
AND decode(p_amount_type,
'R', pcdl.amount,
decode(pcdl.line_type,
'D', pcdl.amount,
pcdl.burdened_cost)) <> 0
AND pei.task_id = pt.task_id
AND pei.expenditure_type = pet.expenditure_type
AND pt.project_id =p_project_id
/*start of change for bug 6037734*/
AND (pa_nl_installed.is_nl_installed='N'
OR (pa_nl_installed.is_nl_installed='Y'
AND EXISTS(select NULL from pa_expenditure_items_all ei1
where ei1.expenditure_item_id = pei.expenditure_item_id
and (ei1.attribute6 is not null OR ei1.attribute7 is not null OR ei1.attribute8 is not null
OR ei1.attribute9 is not null OR ei1.attribute10 is not null)
)
AND NOT EXISTS (select null from pa_expenditure_items_all ei2
where ei2.expenditure_item_id = pei.expenditure_item_id
and ei2.attribute8 is NULL
and ei2.attribute9 is NULL
)
)
OR (pa_nl_installed.is_nl_installed='Y'
AND EXISTS(select null from pa_expenditure_items_all ei3
where ei3.expenditure_item_id = pei.expenditure_item_id
and ei3.attribute6 is NULL
and ei3.attribute7 is NULL
and ei3.attribute8 is NULL
and ei3.attribute9 is NULL
and ei3.attribute10 is NULL
)
)
)
/* end of change for bug 6037734*/
AND EXISTS ( SELECT '1' -- Check for task_id to have asset assignment
FROM pa_project_assets ppa,
pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id = pt.task_id
AND ppa.capital_event_id IS NOT NULL
AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND ppa.capital_hold_flag = 'N'
AND ppa.project_asset_id = paa.project_asset_id
AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
UNION ALL
SELECT '1' -- Check for top_task_id to have asset assignment
FROM pa_project_assets ppa,
pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id = pt.top_task_id
AND ppa.capital_event_id IS NOT NULL
AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND ppa.capital_hold_flag = 'N'
AND ppa.project_asset_id = paa.project_asset_id
AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
UNION ALL
SELECT '1' -- Check for project level Asset Assignments
FROM pa_project_assets ppa,
pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND nvl(paa.task_id,0) = 0
AND ppa.capital_event_id IS NOT NULL
AND ppa.capital_event_id = NVL(x_capital_event_id, pei.capital_event_id)
AND ppa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND ppa.capital_hold_flag = 'N'
AND ppa.project_asset_id = paa.project_asset_id
AND TRUNC(ppa.date_placed_in_service) <= TRUNC(p_in_service_date_through)
UNION ALL
SELECT '1' -- Check task_id is a common cost task
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND task_id = pt.task_id
AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
UNION ALL
SELECT '1' -- Check top_task_id is a common cost task
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND task_id = pt.top_task_id
AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
UNION ALL
SELECT '1' -- Check project is a common cost project
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND nvl(task_id,0) = 0
AND DECODE(paa.project_asset_id, 0,p_common_tasks_flag, 'N') = 'Y'
)
AND NOT EXISTS
(
SELECT
'This CDL was summarized before'
FROM
pa_project_asset_line_details pald
WHERE
pald.expenditure_item_id = pcdl.expenditure_item_id
AND pald.line_num = pcdl.line_num
AND pald.reversed_flag||'' = 'N'
)
ORDER BY
1, -- group level task Id
2, -- asset category id
cip_ccid,
pei.capital_event_id,
GROUPING_METHOD;
select project_id, date_placed_in_service, capital_event_id,
capital_hold_flag, project_asset_type
from pa_project_assets
where project_asset_id = nvl(p_asset_id,0);
curr_invoice_updated_by ap_invoices.last_updated_by%TYPE;
prev_invoice_updated_by ap_invoices.last_updated_by%TYPE;
SELECT pt.capital_cost_type_code
INTO l_amount_type
FROM pa_projects ppr,
pa_project_types pt
WHERE ppr.project_type=pt.project_type
AND project_id=x_project_id;
x_invoice_updated_by => curr_invoice_updated_by,
x_payables_batch_name => curr_payables_batch_name,
x_err_stage => x_err_stage,
x_err_code => x_err_code);
curr_invoice_updated_by := null;
insert into pa_capital_exceptions(request_id,
module,
record_type,
project_id,
project_asset_id,
error_code,
created_by,
creation_date)
values (x_request_id,
'CAPITAL',
'E',
x_project_id,
nvl(client_asset_id,l_asset_id),
extn_error_code,
x_created_by,
fnd_date.date_to_canonical(sysdate));
SELECT pa_project_asset_line_det_s.nextval
INTO proj_asset_line_detail_id
FROM sys.dual;
SELECT meaning
INTO description
FROM pa_lookups
WHERE lookup_type = 'RETIREMENT_COST_TYPE'
AND lookup_code = cdlrec.grouping_method;
update_expenditure_items
(proj_asset_line_detail_id,
'Y',
x_err_stage,
x_err_code);
prev_invoice_updated_by,
prev_invoice_id,
prev_payables_batch_name,
prev_ap_dist_line_number,
prev_ap_inv_dist_id, -- R12 new
orig_asset_id,
x_line_type, --line_type,
prev_capital_event_id,
v_retirement_cost_type,
x_err_stage,
x_err_code);
update_asset_cost
(prev_asset_id,
prev_asset_cost,
0, --- capitalized_cost
x_err_stage,
x_err_code);
SELECT meaning
INTO description
FROM pa_lookups
WHERE lookup_type = 'RETIREMENT_COST_TYPE'
AND lookup_code = cdlrec.grouping_method;
SELECT pa_project_asset_line_det_s.nextval
INTO proj_asset_line_detail_id
FROM sys.dual;
prev_invoice_updated_by := curr_invoice_updated_by;
INSERT INTO pa_reporting_exceptions
(request_id,
context,
sub_context,
attribute1, --project_id
attribute2, --task_id
attribute3, --expenditure_item_id
attribute4, --expenditure_type
attribute5, --line_num
attribute6, --event_id
attribute7, --amount
attribute8, --rejection_code
attribute20 -- transaction_source
)
VALUES
(x_request_id,
'GENERATE_PROJ_ASSET_LINES',
'NO_CC_ID',
cdlrec.project_id,
cdlrec.task_id,
cdlrec.expenditure_item_id,
cdlrec.expenditure_type,
cdlrec.line_num,
cdlrec.ACCT_EVENT_ID,
cdlrec.cip_cost,
'PROJECT EVENT ERRORS',
cdlrec.transaction_source
);
update_expenditure_items
(proj_asset_line_detail_id,
'Y',
x_err_stage,
x_err_code);
prev_invoice_updated_by,
prev_invoice_id,
prev_payables_batch_name,
prev_ap_dist_line_number,
prev_ap_inv_dist_id, -- R12 new
orig_asset_id,
x_line_type, --line_type,
prev_capital_event_id,
v_retirement_cost_type,
x_err_stage,
x_err_code);
update_asset_cost
(prev_asset_id,
prev_asset_cost,
0, --- capitalized_cost
x_err_stage,
x_err_code);
UPDATE
pa_project_asset_lines pal
SET
pal.transfer_status_code = 'X'
WHERE
pal.transfer_status_code||'' IN ('P','R')
AND pal.project_id = x_project_id
AND pal.line_type = x_line_type
AND exists
(SELECT null -- Update project asset lines belonging to new assets
FROM
pa_project_assets ppa
WHERE
ppa.project_id = pal.project_id
AND ppa.project_asset_id = pal.project_asset_id
AND
(
(ppa.capitalized_flag = 'N'
AND ppa.reverse_flag||'' = 'N'
AND TRUNC(ppa.date_placed_in_service) <=
TRUNC(NVL(x_in_service_date_through,
ppa.date_placed_in_service))
)
OR
(-- Update project asset lines for the assets which were
-- transferred to FA previously
ppa.capitalized_flag = 'Y'
AND ppa.reverse_flag||'' = 'N'
AND TRUNC(ppa.date_placed_in_service) <=
TRUNC(NVL(x_in_service_date_through,
ppa.date_placed_in_service))
AND pal.rev_proj_asset_line_id is null
)
OR
( ppa.capitalized_flag = 'Y'
AND pal.rev_proj_asset_line_id is not null
)
)
);
UPDATE
pa_project_asset_lines pal
SET
pal.transfer_status_code = 'X'
WHERE pal.transfer_status_code||'' IN ('P','R')
AND rev_proj_asset_line_id is not null
AND pal.project_id = x_project_id
AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
AND pal.line_type = x_line_type
AND pal.project_asset_id IN
(SELECT
ppa.project_asset_id
FROM
pa_project_assets ppa
WHERE
ppa.project_id = pal.project_id
AND ppa.capitalized_flag = 'Y'
);
PROCEDURE update_asset_capitalized_flag
(x_project_asset_id IN NUMBER,
x_capitalized_flag IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
UPDATE
pa_project_assets
SET
capitalized_flag = x_capitalized_flag,
capitalized_date = sysdate,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
project_asset_id = x_project_asset_id;
END update_asset_capitalized_flag;
PROCEDURE update_asset_adjustment_flag
(x_project_asset_id IN NUMBER,
x_adjustment_flag IN VARCHAR2,
x_adjustment_type IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
--this is a reversing adjustment so do NOT update capitalized_date
UPDATE
pa_project_assets
SET
cost_adjustment_flag = x_adjustment_flag,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
project_asset_id = x_project_asset_id;
--this is a non-reversing adjustment. Update capitalized_date
UPDATE
pa_project_assets
SET
cost_adjustment_flag = x_adjustment_flag,
capitalized_date = sysdate,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
project_asset_id = x_project_asset_id;
END update_asset_adjustment_flag;
SELECT
fai.asset_id
FROM
fa_asset_invoices fai,
pa_project_asset_lines pal
WHERE
fai.project_asset_line_id = pal.project_asset_line_id
AND pal.project_asset_id = x_project_asset_id
AND pal.transfer_status_code||'' = 'T'
GROUP BY
fai.asset_id;
SELECT fa_asset_id
INTO v_fa_asset_id
FROM pa_project_assets_all
WHERE project_asset_id = x_project_asset_id;
select GREATEST(fdp.calendar_period_open_date,
LEAST(sysdate,fdp.calendar_period_close_date))
into x_date_placed_in_service
from fa_deprn_periods fdp
where fdp.book_type_code = x_book_type_code
and fdp.period_close_date is null;
UPDATE
pa_project_asset_lines pal
SET
pal.transfer_status_code = 'R',
pal.transfer_rejection_reason = 'FUTURE_DPIS',
pal.last_updated_by = x_last_updated_by,
pal.last_update_date= sysdate,
pal.created_by = x_created_by,
pal.last_update_login = x_last_update_login,
pal.request_id = x_request_id,
pal.program_application_id = x_program_application_id,
pal.program_id = x_program_id
WHERE
pal.transfer_status_code = 'X'
AND exists
(SELECT
'Yes'
FROM
pa_project_assets ppa
WHERE
ppa.project_asset_id = pal.project_asset_id
AND ppa.capitalized_flag = 'N'
AND ppa.reverse_flag||'' = 'N'
AND fa_mass_add_validate.valid_date_in_service(ppa.date_placed_in_service,ppa.book_type_code) = 0
);
PROCEDURE update_asset_lines
(x_proj_asset_line_id IN NUMBER,
x_transfer_rejection_reason IN VARCHAR2,
x_transfer_status_code IN VARCHAR2,
x_amortize_flag IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
BEGIN
x_err_code := 0;
UPDATE
pa_project_asset_lines
SET
transfer_rejection_reason = x_transfer_rejection_reason,
transfer_status_code = x_transfer_status_code,
amortize_flag = x_amortize_flag,
last_update_date = sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate
WHERE
project_asset_line_id = x_proj_asset_line_id;
END update_asset_lines;
x_invoice_updated_by IN NUMBER,
x_invoice_id IN NUMBER,
x_payables_batch_name IN VARCHAR2,
x_ap_dist_line_number IN Number,
x_invoice_distribution_id IN Number, -- R12 new
x_parent_asset_id IN NUMBER,
x_manufacturer_name IN VARCHAR2,
x_model_number IN VARCHAR2,
x_serial_number IN VARCHAR2,
x_tag_number IN VARCHAR2,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER
)
IS
x_inventorial_flag VARCHAR2(3);
SELECT inventorial
INTO x_inventorial_flag
FROM fa_categories
WHERE category_id = x_asset_category_id;
SELECT
fa_mass_additions_s.nextval
INTO
x_mass_addition_id
FROM
SYS.DUAL;
SELECT
GREATEST(fdp.calendar_period_open_date,LEAST(sysdate,fdp.calendar_period_close_date))
INTO
l_amortization_start_date
FROM
fa_deprn_periods fdp
WHERE
fdp.book_type_code = x_book_type_code
and fdp.period_close_date is null;
The strategy is to insert the records into fa_mass_additions irrespective
of whether the profile option PA: Licensed to Use CRL Projects is set or not.
And if it is set, update the CRL specific columns in IPAFAXB.pls
Refer 3224283 and 3224294 */
/* Commented the following code for the above reason and added it after the
insert into fa_mass_additions statement
if (PA_INSTALL.is_product_installed('IPA')) then --CRL Installed
PA_CRL_FAXFACE.create_crl_fa_mass_additions
(x_accounting_date ,
x_add_to_asset_id ,
x_amortize_flag ,
x_asset_category_id ,
x_asset_key_ccid ,
x_asset_number ,
x_asset_type ,
x_assigned_to ,
x_book_type_code ,
x_create_batch_date ,
x_create_batch_id ,
x_date_placed_in_service ,
x_depreciate_flag ,
x_description ,
x_expense_code_combination_id ,
x_feeder_system_name ,
x_fixed_assets_cost ,
x_fixed_assets_units ,
x_location_id ,
x_mass_addition_id ,
x_merged_code ,
x_merge_prnt_mass_additions_id ,
x_new_master_flag ,
x_parent_mass_addition_id ,
x_payables_code_combination_id ,
x_payables_cost ,
x_payables_units ,
x_posting_status ,
x_project_asset_line_id ,
x_project_id ,
x_queue_name ,
x_split_code ,
x_split_merged_code ,
x_split_prnt_mass_additions_id ,
x_task_id ,
x_inventorial_flag ,
x_invoice_number ,
x_vendor_number ,
x_po_vendor_id ,
x_po_number ,
x_invoice_date ,
x_invoice_created_by ,
x_invoice_updated_by ,
x_invoice_id ,
x_payables_batch_name ,
x_ap_dist_line_number ,
x_invoice_distribution_id , -- R12 new
x_err_stage ,
x_err_code );
INSERT INTO FA_MASS_ADDITIONS(
ACCOUNTING_DATE,
ADD_TO_ASSET_ID,
AMORTIZE_FLAG,
AMORTIZATION_START_DATE, /* added for bug # 2540723 */
ASSET_CATEGORY_ID,
ASSET_KEY_CCID,
ASSET_NUMBER,
ASSET_TYPE,
ASSIGNED_TO,
BOOK_TYPE_CODE,
CREATED_BY,
CREATE_BATCH_DATE,
CREATE_BATCH_ID,
CREATION_DATE,
DATE_PLACED_IN_SERVICE,
DEPRECIATE_FLAG,
DESCRIPTION,
EXPENSE_CODE_COMBINATION_ID,
FEEDER_SYSTEM_NAME,
FIXED_ASSETS_COST,
FIXED_ASSETS_UNITS,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LOCATION_ID,
MASS_ADDITION_ID,
MERGED_CODE,
MERGE_PARENT_MASS_ADDITIONS_ID,
NEW_MASTER_FLAG,
PARENT_MASS_ADDITION_ID,
PAYABLES_CODE_COMBINATION_ID,
PAYABLES_COST,
PAYABLES_UNITS,
POSTING_STATUS,
PROJECT_ASSET_LINE_ID,
PROJECT_ID,
QUEUE_NAME,
SPLIT_CODE,
SPLIT_MERGED_CODE,
SPLIT_PARENT_MASS_ADDITIONS_ID,
TASK_ID,
INVENTORIAL,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
invoice_date,
invoice_created_by,
invoice_updated_by,
invoice_id,
payables_batch_name,
ap_distribution_line_number,
invoice_line_number,
invoice_distribution_id, -- R12 new
parent_asset_id,
manufacturer_name,
model_number,
serial_number,
tag_number
)
SELECT
x_accounting_date,
x_add_to_asset_id,
/* x_amortize_flag, */
x_amort_flag, /*bug 4349574*/
l_amortization_start_date, /* bug#2540723 */
x_asset_category_id,
x_asset_key_ccid,
x_asset_number,
x_asset_type,
x_assigned_to,
x_book_type_code,
x_created_by,
x_create_batch_date,
x_create_batch_id,
SYSDATE,
x_date_placed_in_service,
x_depreciate_flag,
-- Commented for bug 5408702 substr(x_description,1,80), Substr included for bug# 1607527
rtrim(substrb(x_description,1,80)), -- rtrim and Substrb included for bug# 5408702
x_expense_code_combination_id,
x_feeder_system_name,
x_fixed_assets_cost,
x_fixed_assets_units,
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_location_id,
x_mass_addition_id,
x_merged_code,
x_merge_prnt_mass_additions_id,
x_new_master_flag,
x_parent_mass_addition_id,
x_payables_code_combination_id,
x_payables_cost,
x_payables_units,
x_posting_status,
x_project_asset_line_id,
x_project_id,
x_queue_name,
x_split_code,
x_split_merged_code,
x_split_prnt_mass_additions_id,
x_task_id,
x_inventorial_flag,
x_invoice_number,
x_vendor_number,
x_po_vendor_id,
x_po_number,
x_invoice_date,
x_invoice_created_by,
x_invoice_updated_by,
x_invoice_id,
x_payables_batch_name,
x_ap_dist_line_number,
NULL, -- invoice_line_number,
x_invoice_distribution_id, -- R12 new
x_parent_asset_id,
x_manufacturer_name,
x_model_number,
x_serial_number,
x_tag_number
FROM SYS.DUAL;
/* Bug#3224283. Added Call to pa_crl_faxface after inserting the
record into fa_mass_additions */
if (PA_INSTALL.is_product_installed('IPA')) then --CRL Installed
PA_CRL_FAXFACE.create_crl_fa_mass_additions
(x_accounting_date ,
x_add_to_asset_id ,
/* x_amortize_flag , */
x_amort_flag , /*bug 4349574 */
x_asset_category_id ,
x_asset_key_ccid ,
x_asset_number ,
x_asset_type ,
x_assigned_to ,
x_book_type_code ,
x_create_batch_date ,
x_create_batch_id ,
x_date_placed_in_service ,
x_depreciate_flag ,
rtrim(substrb(x_description,1,80)) , /* Added for bug 5408702 */
x_expense_code_combination_id ,
x_feeder_system_name ,
x_fixed_assets_cost ,
x_fixed_assets_units ,
x_location_id ,
x_mass_addition_id ,
x_merged_code ,
x_merge_prnt_mass_additions_id ,
x_new_master_flag ,
x_parent_mass_addition_id ,
x_payables_code_combination_id ,
x_payables_cost ,
x_payables_units ,
x_posting_status ,
x_project_asset_line_id ,
x_project_id ,
x_queue_name ,
x_split_code ,
x_split_merged_code ,
x_split_prnt_mass_additions_id ,
x_task_id ,
x_inventorial_flag ,
x_invoice_number ,
x_vendor_number ,
x_po_vendor_id ,
x_po_number ,
x_invoice_date ,
x_invoice_created_by ,
x_invoice_updated_by ,
x_invoice_id ,
x_payables_batch_name ,
--x_ap_dist_line_number , Send invoice_distribution_id instead.
x_invoice_distribution_id , -- R12 new.
x_err_stage ,
x_err_code );
SELECT
ppa.project_id,
ppa.project_asset_id,
pal.cip_ccid,
pal.asset_cost_ccid,
ppa.asset_number,
ppa.asset_name,
ppa.asset_description,
ppa.location_id,
ppa.assigned_to_person_id,
ppa.date_placed_in_service,
ppa.asset_category_id,
ppa.asset_key_ccid,
ppa.book_type_code,
ppa.asset_units,
decode(ppa.depreciate_flag,'Y','YES','N','NO') depreciate_flag,
ppa.depreciation_expense_ccid,
decode(ppa.amortize_flag, 'Y','YES','N','NO') amortize_flag,
ppa.amortize_flag single_char_amortize_flag,
ppa.cost_adjustment_flag,
ppa.capitalized_flag,
ppa.reverse_flag,
decode(nvl(ppa.new_master_flag,'N'),'Y','YES','N','NO') new_master_flag, -- Bug 5435551
pal.project_asset_line_id,
pal.project_asset_line_detail_id detail_id,
pal.rev_proj_asset_line_id,
pal.description,
pal.task_id,
pal.current_asset_cost,
pal.gl_date,
ppt.interface_complete_asset_flag,
ppt.vendor_invoice_grouping_code,
pal.invoice_number,
pal.vendor_number,
pal.po_vendor_id,
pal.po_number,
pal.invoice_date,
pal.invoice_created_by,
pal.invoice_updated_by,
pal.invoice_id,
pal.payables_batch_name,
pal.ap_distribution_line_number,
pal.invoice_distribution_id, -- R12 new
ppa.parent_asset_id,
ppa.manufacturer_name,
ppa.model_number,
ppa.serial_number,
ppa.tag_number
FROM
pa_project_asset_lines pal,
pa_project_assets ppa,
pa_projects pp,
pa_project_types ppt
WHERE
pal.project_asset_id = ppa.project_asset_id
AND ppa.project_id = pp.project_id
AND ppa.project_id = x_project_id
AND pp.project_type = ppt.project_type
AND pal.transfer_status_code = 'X'
AND pal.line_type = 'C'
ORDER BY --- These order by is very important
DECODE(ppt.vendor_invoice_grouping_code,'N',ppa.project_asset_id||pal.ap_distribution_line_number,ppa.project_asset_id),
pal.cip_ccid,
pal.asset_cost_ccid;
select 'X'
from pa_project_asset_line_details d,
pa_cost_distribution_lines_all cdl
where d.PROJECT_ASSET_LINE_DETAIL_ID = x_detail_id and
d.expenditure_item_id = cdl.expenditure_item_id and
d.line_num = cdl.line_num and
(
cdl.transfer_status_code in ('P','R','X','T')
/* -- R12 change..pa_gl_interface is obsolete.
OR
Exists ( Select 'X' From Pa_Gl_Interface GL
Where GL.Reference26 = cdl.batch_name
And Cdl.transfer_status_code = 'A'
)
*/
);
Select ASSET_KEY_FLEX_STRUCTURE into keynumber
from FA_SYSTEM_CONTROLS;
update_asset_lines
(assetlinerec.project_asset_line_id,
'CIP_NOT_XFERD_TO_GL',
'R',
NULL,
x_err_stage,
x_err_code);
-- also update the assets status as capitalized
update_asset_capitalized_flag
(curr_project_asset_id,
'Y',
x_err_stage,
x_err_code);
assetlinerec.invoice_updated_by,
assetlinerec.invoice_id,
assetlinerec.payables_batch_name,
assetlinerec.ap_distribution_line_number,
assetlinerec.invoice_distribution_id, -- R12 new
assetlinerec.parent_asset_id,
assetlinerec.manufacturer_name,
assetlinerec.model_number,
assetlinerec.serial_number,
assetlinerec.tag_number,
x_err_stage,
x_err_code);
assetlinerec.invoice_updated_by,
assetlinerec.invoice_id,
assetlinerec.payables_batch_name,
assetlinerec.ap_distribution_line_number,
assetlinerec.invoice_distribution_id, -- R12 new
assetlinerec.parent_asset_id,
assetlinerec.manufacturer_name,
assetlinerec.model_number,
assetlinerec.serial_number,
assetlinerec.tag_number,
/* End of Automatic asset capitalization changes */
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'ASSET_NOT_COMPLETE',
'R',
NULL,
x_err_stage,
x_err_code);
SELECT '1'
INTO fa_posted_count
FROM DUAL
WHERE EXISTS (SELECT mass_addition_id
FROM fa_mass_additions
WHERE asset_number = assetlinerec.asset_number AND
posting_status = 'POSTED');
update_asset_adjustment_flag
(curr_project_asset_id,
'Y',
x_asset_type,
x_err_stage,
x_err_code);
assetlinerec.invoice_updated_by,
assetlinerec.invoice_id,
assetlinerec.payables_batch_name,
assetlinerec.ap_distribution_line_number,
assetlinerec.invoice_distribution_id, -- R12 new
assetlinerec.parent_asset_id,
assetlinerec.manufacturer_name,
assetlinerec.model_number,
assetlinerec.serial_number,
assetlinerec.tag_number,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'ASSET_NOT_POSTED',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'DEPRN_NOT_FOUND',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'ASSET_NOT_ADJUSTABLE',
'R',
NULL,
x_err_stage,
x_err_code);
--Update the line as transferred
update_asset_lines
(assetlinerec.project_asset_line_id,
NULL,
'T',
assetlinerec.single_char_amortize_flag,
x_err_stage,
x_err_code);
-- Update the asset capitalized_cost
pa_faxface.update_asset_cost
(assetlinerec.project_asset_id,
0, --- grouped_cip_cost
assetlinerec.current_asset_cost, --- capitalized_cost
x_err_stage,
x_err_code);
SELECT
ppr.project_id,
ppr.segment1,
pt.capital_cost_type_code,
pt.cip_grouping_method_code,
pt.OVERRIDE_ASSET_ASSIGNMENT_FLAG,
pt.VENDOR_INVOICE_GROUPING_CODE,
NVL(pt.total_burden_flag, DECODE(pt.burden_amt_display_method,
'S','Y',
'D','N',
'Y')) total_burden_flag,
pt.Burden_amt_display_method
FROM
pa_projects ppr,
pa_project_types pt
WHERE
ppr.segment1 between x_project_num_from and x_project_num_to and
ppr.template_flag <> 'Y' and
PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
'CAPITALIZE') = 'Y' and
ppr.project_type = pt.project_type and
pt.project_type_class_code = 'CAPITAL'
AND NOT EXISTS (SELECT 'X' /* Modified the selprjs cursor for Bug 7128863 start */
FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA1,
PA_PROJECT_ASSET_ASSIGNMENTS PPAA2
WHERE PPAA1.PROJECT_ID = PPR.PROJECT_ID
AND PPAA1.PROJECT_ID = PPAA2.PROJECT_ID
AND ((PPAA1.PROJECT_ID <> 0
AND PPAA1.TASK_ID = 0)
AND (PPAA2.PROJECT_ID <> 0
AND PPAA2.TASK_ID <> 0))); /* Modified the selprjs cursor for Bug 7128863 end */
and ( exists (select 'x' -- project has costed,uncapitalized expenditure items
from pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_tasks pat
where pcdl.expenditure_item_id = pei.expenditure_item_id
and pei.revenue_distributed_flag||'' = 'N'
and pei.cost_distributed_flag ='Y'
and pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R','B','D','R')
and pcdl.billable_flag = 'Y'
and pei.task_id = pat.task_id
and pat.project_id = ppr.project_id)
or exists (select 'x' -- Untransferred asset lines exist
from pa_project_asset_lines pal
where pal.project_id = ppr.project_id
and pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
and pal.transfer_status_code <> 'T')
or exists ( select 'x' -- project has assets to be reverse capitalized
from pa_project_assets ppa
where ppa.project_id+0 = ppr.project_id
and ppa.reverse_flag = 'Y'));
SELECT pal.project_asset_line_id,
pal.capital_event_id,
pal.project_id,
pal.task_id,
NVL(pal.line_type,'C') line_type,
p.segment1 project_number,
pce.capital_event_number,
pce.event_name,
NVL(pce.asset_allocation_method, NVL(p.asset_allocation_method,'N')) asset_allocation_method,
pal.asset_category_id /* Added for bug#3211946 */
FROM pa_project_asset_lines_all pal,
pa_projects p,
pa_capital_events pce
WHERE pal.project_id = p.project_id
AND p.project_id = x_project_id
AND pal.capital_event_id = NVL(x_capital_event_id, pal.capital_event_id)
AND pal.capital_event_id = pce.capital_event_id (+)
AND pal.project_asset_id = 0
AND NVL(pce.asset_allocation_method, NVL(p.asset_allocation_method,'N')) <> 'N'
ORDER BY pal.project_id, pal.capital_event_id, pal.task_id; --This order by is critical for cache purposes
is used to create project assets procedure no_event_projects will fail to update
pa_project_assets table since the client code has not been fired at this point.
IF x_capital_event_id IS NULL THEN
PA_FAXFACE.NO_EVENT_PROJECTS
( x_project_num_from => x_project_num_from,
x_project_num_to => x_project_num_to,
x_in_service_date_through => x_in_service_date_through,
x_err_stage => x_err_stage,
x_err_code => x_err_code);
select set_of_books_id
into l_ledger_id
from pa_implementations;
INSERT INTO pa_capital_exceptions
(request_id,
module,
record_type,
project_id,
error_code,
created_by,
creation_date)
VALUES
(x_request_id,
'CAPITAL',
'E',
projrec.project_id,
'C', --Asset Creation Extension Error
x_created_by,
fnd_date.date_to_canonical(sysdate));
-- now delete all the asset lines which could be deleted
delete_asset_lines
(projrec.project_id,
x_capital_event_id,
x_err_stage,
x_err_code);
SELECT 'Y'
INTO v_ret_cost_tasks_exist
FROM sys.dual
WHERE EXISTS
(SELECT task_id
FROM pa_tasks
WHERE project_id = projrec.project_id
AND retirement_cost_flag = 'Y');
INSERT INTO pa_reporting_exceptions
(request_id,
context,
sub_context,
module,
record_type,
org_id,
attribute1, --project_id
attribute2, --project_number
attribute3, --task_id
attribute4, --project_asset_line_id
attribute5, --capital_event_id
attribute6, --capital_event_number
attribute7, --event_name
attribute8, --asset_allocation_method
attribute9, --asset_id
attribute10, --error_code
attribute20, --error message
user_id,
attribute_date1)
VALUES
(x_request_id,
'PA_ASSET_ALLOCATION_PVT',
v_asset_or_project_err,
'ALLOCATE_UNASSIGNED',
v_return_status,
--NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1), ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99),
L_current_org_id,
unassigned_lines_rec.project_id,
unassigned_lines_rec.project_number,
unassigned_lines_rec.task_id,
unassigned_lines_rec.project_asset_line_id,
unassigned_lines_rec.capital_event_id,
unassigned_lines_rec.capital_event_number,
unassigned_lines_rec.event_name,
unassigned_lines_rec.asset_allocation_method,
v_err_asset_id,
v_error_code,
v_msg_data,
x_created_by,
-- fnd_date.date_to_canonical(sysdate));
SELECT distinct
ppr.project_id,
ppr.segment1
FROM
pa_projects ppr,
pa_project_types pt,
pa_project_asset_lines pal
WHERE
ppr.segment1 between x_project_num_from and x_project_num_to and
ppr.template_flag <> 'Y' and
PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
'CAPITALIZE') = 'Y' and
ppr.project_type = pt.project_type and
pt.project_type_class_code = 'CAPITAL' and
pt.interface_asset_cost_code = 'F' and
ppr.project_id = pal.project_id and
pal.transfer_status_code||'' IN ('P','R')
and (exists
(SELECT 'x'
FROM
pa_project_assets ppa
WHERE ppa.project_id = ppr.project_id
AND ppa.reverse_flag||'' = 'N'
AND TRUNC(ppa.date_placed_in_service) <=
TRUNC(NVL(x_in_service_date_through,ppa.date_placed_in_service))
AND ppa.capitalized_flag = 'N'
AND pal.project_asset_id = ppa.project_asset_id
)
or exists
(SELECT 'x'
FROM
pa_project_assets ppa
WHERE ppa.project_id = ppr.project_id
AND ppa.reverse_flag||'' = 'N'
AND ppa.capitalized_flag ='Y'
AND TRUNC(ppa.date_placed_in_service) <=
TRUNC(NVL(x_in_service_date_through,ppa.date_placed_in_service))
AND pal.rev_proj_asset_line_id is null
AND pal.project_asset_id = ppa.project_asset_id
)
or exists
(SELECT 'x'
FROM
pa_project_assets ppa
WHERE ppa.project_id = ppr.project_id
AND ppa.capitalized_flag = 'Y'
AND pal.rev_proj_asset_line_id is not null
AND pal.project_asset_id = ppa.project_asset_id
)
);
GOTO next_proj; -- No asset lines are selected for entire project
GOTO skip_ret_lines; --No retirement asset lines are selected
SELECT
ppr.project_id
FROM
pa_projects ppr,
pa_project_types pt
WHERE
ppr.segment1 between x_project_num_from and x_project_num_to and
ppr.template_flag <> 'Y' and
PA_PROJECT_UTILS.Check_prj_stus_action_allowed(ppr.project_status_code,
'CAPITALIZE') = 'Y' and
ppr.project_type = pt.project_type and
pt.project_type_class_code = 'CAPITAL' and
pt.interface_asset_cost_code = 'F';
select set_of_books_id
into l_ledger_id
from pa_implementations;
-- now delete all the asset lines which could be deleted
delete_asset_lines
(projrec.project_id,
/* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
NULL, --x_capital_event_id, *This line added just to allow SUMMARIZE_XFACE to compile
/* End of Automatic asset capitalization changes */
x_err_stage,
x_err_code);
Commented the Select statement for Bug#2540426 and
split this statement into three separate selects
SELECT 1 INTO dummy
FROM dual
WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized expenditure items
FROM pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_tasks pat,
pa_projects pp,
pa_project_types pt
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND pp.project_id = p_project_id
AND pp.project_type = pt.project_type
AND pei.revenue_distributed_flag||'' = 'N'
AND pei.cost_distributed_flag ='Y'
AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
'B',decode(pt.burden_amt_display_method,'S','D','R'),
'R') -- * Added decode for bug 1309745*
AND pcdl.billable_flag = 'Y'
AND pei.task_id = pat.task_id
AND pat.project_id = p_project_id)
OR EXISTS (SELECT 'x' -- Untransferred assetlines exist
FROM pa_project_asset_lines pal
WHERE pal.project_id = p_project_id
AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
AND pal.transfer_status_code <> 'T')
OR EXISTS (SELECT 'x' -- project has assets to be reverse capitalized
FROM pa_project_assets ppa
WHERE ppa.project_id+0 = p_project_id
AND ppa.reverse_flag = 'Y');
SELECT 1 INTO dummy
FROM DUAL
WHERE EXISTS (SELECT 'x' -- project has assets to be reverse capitalized
FROM pa_project_assets ppa
WHERE ppa.project_id+0 = p_project_id
AND ppa.reverse_flag = 'Y'
/* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
AND capital_event_id = NVL(p_capital_event_id, capital_event_id)
AND capital_event_id IS NOT NULL
/* End of Automatic asset capitalization changes */
);
SELECT 1 INTO dummy
FROM DUAL
WHERE EXISTS (SELECT 'x' -- Untransferred assetlines exist
FROM pa_project_asset_lines pal
WHERE pal.project_id = p_project_id
AND pal.rev_proj_asset_line_id IS NULL -- This line is not an adjustment
AND pal.transfer_status_code <> 'T'
/* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
AND pal.capital_event_id = NVL(p_capital_event_id, pal.capital_event_id)
/* End of Automatic asset capitalization changes */
);
SELECT 1 INTO dummy
FROM dual
WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized Retirement Cost expenditure items
FROM pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_tasks pat,
pa_projects pp,
pa_project_types pt
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND pp.project_id = p_project_id
AND pp.project_type = pt.project_type
AND pei.revenue_distributed_flag||'' = 'N'
AND pei.cost_distributed_flag ='Y'
/* PA.L Code change to allow 'R' or 'I' line types in cases when previously only 'R' was used. JPULTORAK 20-MAY-2003 */
--In addition, PA.L allows 'D' lines to exist in all cases when Total Burden Flag is 'Y'
--The following section was changed into an OR condition to allow 'R' or 'I' lines as introduced by PA.L
--AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
AND (pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
/* Fix for Enable TBC Accounting Option JPULTORAK 14-FEB-2003 */
-- We can only select 'D' CDLs when TBC Accounting is ENABLED and Burden Amt Display Method is 'S'
-- 'B',decode(pt.burden_amt_display_method,'S','D','R'),
-- 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y',decode(pt.burden_amt_display_method,'S','D','R'),'R'),
-- The line above was commented out since PA.L allows 'D' lines in all cases where Total Burden Flag is 'Y'
'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','R'),
/* End of Fix for Enable TBC Accounting Option */
'R') --Added decode for bug 1309745
OR
pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','I',
'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','I'),
'I'))
/* End of PA.L code change section */
AND pcdl.billable_flag = 'N'
AND pei.task_id = pat.task_id
AND pat.project_id = pp.project_id --Bug 3057423 added to avoid merge join cartesian
AND pat.project_id = p_project_id
AND pei.capital_event_id = NVL(p_capital_event_id, pei.capital_event_id)
AND pei.capital_event_id IS NOT NULL
AND pat.retirement_cost_flag = 'Y');
SELECT 1 INTO dummy
FROM dual
WHERE EXISTS (SELECT 'x' -- project has costed,uncapitalized expenditure items
FROM pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
--pa_tasks pat, /* bug fix :2830211 task_id is not reqd */
pa_projects_all pp,
pa_project_types pt
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND pp.project_id = p_project_id
AND pei.project_id = pp.project_id /* added for bug fix :2830211 */
AND pp.project_type = pt.project_type
AND pei.revenue_distributed_flag||'' = 'N'
AND pei.cost_distributed_flag ='Y'
/* PA.L Code change to allow 'R' or 'I' line types in cases when previously only 'R' was used. JPULTORAK 20-MAY-2003 */
--In addition, PA.L allows 'D' lines to exist in all cases when Total Burden Flag is 'Y'
--The following section was changed into an OR condition to allow 'R' or 'I' lines as introduced by PA.L
--AND pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
AND (pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','R',
/* Fix for Enable TBC Accounting Option JPULTORAK 14-FEB-2003 */
-- We can only select 'D' CDLs when TBC Accounting is ENABLED and Burden Amt Display Method is 'S'
-- 'B',decode(pt.burden_amt_display_method,'S','D','R'),
-- 'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y',decode(pt.burden_amt_display_method,'S','D','R'),'R'),
-- The line above was commented out since PA.L allows 'D' lines in all cases where Total Burden Flag is 'Y'
'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','R'),
/* End of Fix for Enable TBC Accounting Option */
'R')/* Added decode for bug 1309745*/
OR
pcdl.line_type = DECODE(pt.capital_cost_type_code,'R','I',
'B',DECODE(NVL(pt.total_burden_flag,'N'),'Y','D','I'),
'I'))
/* End of PA.L code change section */
AND pcdl.billable_flag = 'Y'
/* bug fix :2830211 */
--AND pei.task_id = pat.task_id
--AND pat.project_id = p_project_id
--AND pat.project_id = pp.project_id missing join causing cartesion
/* End of bug fix :2830211 */
/* Automatic asset capitalization changes JPULTORAK 04-FEB-03 */
AND pei.capital_event_id = NVL(p_capital_event_id, pei.capital_event_id)
AND pei.capital_event_id IS NOT NULL
/* End of Automatic asset capitalization changes */
);
RETURN FALSE; /* Return False only when there is no data for all the three selects */
SELECT 'Deprn Expense Acct code combination is valid'
FROM gl_code_combinations gcc,
gl_sets_of_books gsob,
pa_implementations pi
WHERE gcc.code_combination_id = l_new_deprn_expense_ccid
AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
AND gsob.set_of_books_id = pi.set_of_books_id
AND gcc.account_type = 'E';
UPDATE pa_project_assets
SET depreciation_expense_ccid = x_out_deprn_expense_ccid,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE project_asset_id = x_project_asset_id;
END IF; --Value has changed, perform update
SELECT
ppa.project_id,
ppa.project_asset_id,
pal.cip_ccid,
ppa.asset_number,
ppa.asset_name,
ppa.asset_description,
ppa.date_placed_in_service,
ppa.book_type_code,
ppa.ret_target_asset_id,
decode(ppa.amortize_flag, 'Y','YES','N','NO') amortize_flag,
ppa.amortize_flag single_char_amortize_flag,
ppa.cost_adjustment_flag,
ppa.capitalized_flag,
ppa.reverse_flag,
decode(nvl(ppa.new_master_flag,'N'),'Y','YES','N','NO') new_master_flag, -- Bug 5435551
pal.project_asset_line_id,
pal.project_asset_line_detail_id detail_id,
pal.rev_proj_asset_line_id,
pal.description,
pal.task_id,
pal.current_asset_cost,
pal.gl_date
FROM
pa_project_asset_lines pal,
pa_project_assets ppa,
pa_projects pp,
pa_project_types ppt
WHERE
pal.project_asset_id = ppa.project_asset_id
AND ppa.project_id = pp.project_id
AND ppa.project_id = x_project_id
AND pp.project_type = ppt.project_type
AND pal.transfer_status_code = 'X'
AND pal.line_type = 'R'
ORDER BY ppa.project_asset_id;
select 'X'
from pa_project_asset_line_details d,
pa_cost_distribution_lines_all cdl
where d.PROJECT_ASSET_LINE_DETAIL_ID = x_detail_id and
d.expenditure_item_id = cdl.expenditure_item_id and
d.line_num = cdl.line_num and
(
cdl.transfer_status_code in ('P','R','X','T')
/* -- R12 change..pa_gl_interface is obsolete.
OR
Exists ( Select 'X' From Pa_Gl_Interface GL
Where GL.Reference26 = cdl.batch_name
And Cdl.transfer_status_code = 'A'
)
*/
)
;
SELECT fb.set_of_books_id
FROM fa_book_controls fb,
pa_implementations pi
WHERE fb.set_of_books_id = pi.set_of_books_id
AND fb.book_type_code = x_book_type_code;
SELECT fa.asset_category_id
FROM fa_books fb,
fa_additions fa
WHERE fa.asset_id = x_ret_target_asset_id
AND fa.asset_type = 'GROUP'
AND fa.asset_id = fb.asset_id
AND fb.book_type_code = x_book_type_code
AND fb.date_ineffective IS NULL;
update_asset_lines
(assetlinerec.project_asset_line_id,
'CIP_NOT_XFERD_TO_GL',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'TARGET_NOT_COMPLETE',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'TARGET_NOT_COMPLETE',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'TARGET_NOT_ADJUSTABLE',
'R',
NULL,
x_err_stage,
x_err_code);
update_asset_capitalized_flag
(assetlinerec.project_asset_id,
'Y',
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
NULL,
'T',
assetlinerec.single_char_amortize_flag,
x_err_stage,
x_err_code);
-- Update the asset capitalized_cost
pa_faxface.update_asset_cost
(assetlinerec.project_asset_id,
0, --- grouped_cip_cost
assetlinerec.current_asset_cost, --- capitalized_cost
x_err_stage,
x_err_code);
update_asset_lines
(assetlinerec.project_asset_line_id,
'ERROR_IN_GROUP_RET_ADJ', --v_msg_data,
'R',
NULL,
x_err_stage,
x_err_code);
SELECT p.project_id
FROM pa_projects p,
pa_project_types pt
WHERE p.segment1
BETWEEN x_project_num_from AND x_project_num_to
AND p.template_flag <> 'Y'
AND PA_PROJECT_UTILS.Check_prj_stus_action_allowed(p.project_status_code,'CAPITALIZE') = 'Y'
AND p.project_type = pt.project_type
AND pt.project_type_class_code = 'CAPITAL'
AND NVL(p.capital_event_processing,'N') = 'N';
select 'Y' into is_no_event_proj
from pa_projects_all p
where p.project_id = x_project_id
and NVL(p.capital_event_processing,'N') = 'N';
UPDATE pa_project_assets_all
SET capital_event_id = -1,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE project_id = x_project_id /*Bug 5758490*/
AND project_asset_type IN ('AS-BUILT','RETIREMENT_ADJUSTMENT')
AND date_placed_in_service IS NOT NULL
AND date_placed_in_service <= x_in_service_date_through
AND capital_event_id IS NULL
AND capital_hold_flag = 'N';
UPDATE pa_expenditure_items_all
SET capital_event_id = -1,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE project_id = x_project_id /*Bug 5758490*/
AND billable_flag||'' = 'Y'
AND capital_event_id IS NULL;
UPDATE pa_expenditure_items_all peia
SET capital_event_id = -1,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE project_id = x_project_id /*Bug 5758490*/
AND billable_flag||'' = 'N'
AND capital_event_id IS NULL
AND EXISTS
(SELECT t.task_id
FROM pa_tasks t
WHERE t.task_id = peia.task_id
AND t.retirement_cost_flag = 'Y');
SELECT gl.ledger_id, gl.currency_code, imp.set_of_books_id, imp.org_id, gld.sla_ledger_cash_basis_flag
FROM gl_alc_ledger_rships_v gl,
pa_implementations imp,
gl_ledgers gld
WHERE gl.source_ledger_id = imp.set_of_books_id
AND gl.application_id = 275
AND gl.org_id = imp.org_id
AND gld.ledger_id = imp.set_of_books_id
AND gl.relationship_enabled_flag = 'Y';
SELECT cdl.line_type, cdl.transfer_status_code, cdl.acct_event_id, cdl.denom_raw_cost, cdl.acct_rate_type, cdl.acct_exchange_rate,
pts.acct_source_code, pts.transaction_source, cdl.gl_date, cdl.denom_currency_code, nvl(ei.historical_flag, 'Y'),
nvl(pts.predefined_flag,'Y'), cdl.system_reference5, ei.document_distribution_id, ei.document_payment_id
INTO l_line_type, l_transfer_status_code, l_acct_event_id, l_denom_raw_cost, l_exchange_rate_type, l_exchange_rate,
l_acct_source_code, l_transaction_source, l_gl_date, l_denom_currency_code, l_historical_flag,
l_predefined_flag , l_system_reference5, l_document_distribution_id, l_document_payment_id
FROM pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl,
pa_transaction_sources pts
WHERE cdl.expenditure_item_id = x_expenditure_item_id
AND cdl.line_num = x_line_num
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND ei.transaction_source = pts.transaction_source(+) ;
select nvl(xdl.unrounded_accounted_dr, -1 * xdl.unrounded_accounted_cr)
into l_cip_cost
from xla_distribution_links xdl,
xla_ae_lines xal,
xla_ae_headers xah,
xla_acct_class_assgns xaca,
xla_assignment_defns_b xad,
xla_post_acct_progs_b xpap
where xpap.program_code = 'PA_POSTACCOUNTING_DEBIT'
and xpap.program_owner_code = 'S'
and xpap.application_id = 275
and xpap.program_owner_code = xad.program_owner_code
and xpap.program_code = xad.program_code
and xad.enabled_flag = 'Y'
and (xad.ledger_id IS NULL OR xad.ledger_id = v_alc_ledger.ledger_id)
and xaca.assignment_owner_code = xad.assignment_owner_code
and xaca.assignment_code = xad.assignment_code
and xaca.program_owner_code = xad.program_owner_code
and xaca.program_code = xad.program_code
and xal.accounting_class_code = xaca.accounting_class_code
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = v_alc_ledger.ledger_id
and xah.balance_type_code = 'A'
and xah.accounting_entry_status_code = 'F'
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and decode ( xaca.accounting_class_code ,'DISCOUNT', decode (v_alc_ledger.sla_ledger_cash_basis_flag , 'Y', 2 , 1) , 1 ) = 1
/*
Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
Restricted 'Discount' acc class code to be fetched from debit side of Post Acc Program
'PA_POSTACCOUNITNG_DEBIT' for cash basis txns, as for cash basis txn, 'Discount' should
be on credit side of Post Acc Programs.
For R12+, this need be reverted out and create seperate post acc program
for cash basis include 'Discount' in credit side and remove from Debit.
*/
and xdl.application_id = DECODE(l_transfer_status_code, 'V',
decode( l_Acct_Source_Code, 'AP_INV', 200,
'AP_PAY', 200,
'AP_APP', 200,
'INV', 707,
'WIP', 707,
'RCV', 707),
275)
and NVL(xdl.source_distribution_id_num_2, -99) = DECODE(l_transfer_status_code,
'V',nvl(xdl.source_distribution_id_num_2,-99),
x_line_num
)
and xdl.source_distribution_id_num_1 = DECODE(l_transfer_status_code, 'V',
DECODE(l_Acct_Source_Code, 'AP_INV',
DECODE ( l_document_payment_id ,
NULL , l_document_distribution_id,
l_system_reference5
) ,
'AP_PAY', l_System_Reference5,
'AP_APP', l_System_Reference5,
'INV', l_System_Reference5,
'WIP', l_System_Reference5,
'RCV', l_System_Reference5),
x_expenditure_item_id)
and xdl.source_distribution_type = DECODE(l_transfer_status_code,
'V', DECODE (l_Acct_Source_Code,
'AP_PAY','AP_PMT_DIST',
'AP_INV', DECODE ( l_document_payment_id ,
NULL , 'AP_INV_DIST',
'AP_PMT_DIST'
),
'AP_APP','AP_PREPAY',
'RCV','RCV_RECEIVING_SUB_LEDGER',
'INV','MTL_TRANSACTION_ACCOUNTS',
'WIP','WIP_TRANSACTION_ACCOUNTS')
, l_line_type );
SELECT 'N'
into l_alc_exists
from dual
WHERE NOT EXISTS (
SELECT gl.ledger_id
FROM gl_alc_ledger_rships_v gl
WHERE gl.source_ledger_id = v_alc_ledger.set_of_books_id
AND gl.application_id = decode( l_Acct_Source_Code, 'AP_INV', 200,
'AP_PAY', 200,
'AP_APP', 200,
'INV', 707,
'WIP', 707,
'RCV', 707,
0)
AND gl.currency_code = v_alc_ledger.currency_code
AND gl.relationship_enabled_flag = 'Y') ;
SELECT p_type.capital_cost_type_code
INTO l_capital_cost_type
FROM pa_project_types_all p_type,
pa_projects_all proj,
pa_cost_distribution_lines_all pa_cdl
WHERE p_type.project_type = proj.project_type
AND proj.project_id = pa_cdl.project_id
AND pa_cdl.expenditure_item_id = x_expenditure_item_id
AND pa_cdl.line_num = x_line_num
AND p_type.org_id = v_alc_ledger.org_id;
SELECT nvl(burdened_cost,amount),
exchange_rate,
conversion_date
INTO l_cip_cost,
l_exchange_rate,
l_gl_date
FROM PA_MC_COST_DIST_LINES_ALL
WHERE set_of_books_id = v_alc_ledger.ledger_id
AND expenditure_item_id = x_expenditure_item_id
AND line_num = x_line_num;
SELECT amount,
exchange_rate,
conversion_date
INTO l_cip_cost,
l_exchange_rate,
l_gl_date
FROM PA_MC_COST_DIST_LINES_ALL
WHERE set_of_books_id = v_alc_ledger.ledger_id
AND expenditure_item_id = x_expenditure_item_id
AND line_num = x_line_num;
INSERT into PA_MC_PRJ_AST_LINE_DTLS
(
SET_OF_BOOKS_ID,
PROJ_ASSET_LINE_DTL_UNIQ_ID,
PROJECT_ASSET_LINE_DETAIL_ID,
CIP_COST,
CURRENCY_CODE,
EXCHANGE_RATE,
CONVERSION_DATE
)
VALUES
(
v_alc_ledger.ledger_id,
x_proj_asset_line_dtl_uniq_id,
x_project_asset_line_detail_id,
l_cip_cost,
v_alc_ledger.currency_code,
l_exchange_rate,
l_gl_date
);
SELECT gl.ledger_id, gl.currency_code
FROM gl_alc_ledger_rships_v gl,
pa_implementations imp
WHERE gl.source_ledger_id = imp.set_of_books_id
AND gl.application_id = 275
AND gl.org_id = imp.org_id
AND gl.relationship_enabled_flag = 'Y';
SELECT original_asset_cost,
current_asset_cost
INTO l_orig_asset_cost,
l_curr_asset_cost
FROM PA_MC_PRJ_AST_LINES_ALL
WHERE set_of_books_id = v_alc_ledger.ledger_id
AND project_asset_line_id = x_rev_proj_asset_line_id;
SELECT original_asset_cost,
sum(current_asset_cost)
INTO l_orig_asset_cost,
l_curr_asset_cost
FROM PA_MC_PRJ_AST_LINES_ALL
WHERE set_of_books_id = v_alc_ledger.ledger_id
AND project_asset_line_detail_id = x_project_asset_line_detail_id
GROUP BY original_asset_cost;
SELECT nvl(sum(cip_cost),0)
INTO l_orig_asset_cost
FROM PA_MC_PRJ_AST_LINE_DTLS
WHERE set_of_books_id = v_alc_ledger.ledger_id
AND project_asset_line_detail_id = x_project_asset_line_detail_id;
INSERT into PA_MC_PRJ_AST_LINES_ALL
(
SET_OF_BOOKS_ID,
PROJECT_ASSET_LINE_ID,
ORIGINAL_ASSET_COST,
CURRENT_ASSET_COST,
PROJECT_ASSET_LINE_DETAIL_ID
)
VALUES
(
v_alc_ledger.ledger_id,
x_project_asset_line_id,
l_orig_asset_cost,
l_curr_asset_cost,
x_project_asset_line_detail_id
);
PROCEDURE update_alc_proj_asset_lines (x_project_asset_line_id IN NUMBER,
x_original_asset_cost IN NUMBER,
x_current_asset_cost IN NUMBER)
IS
CURSOR c_alc_ledger IS
SELECT gl.ledger_id, gl.currency_code
FROM gl_alc_ledger_rships_v gl,
pa_implementations imp
WHERE gl.source_ledger_id = imp.set_of_books_id
AND gl.application_id = 275
AND gl.org_id = imp.org_id
AND gl.relationship_enabled_flag = 'Y';
UPDATE PA_MC_PRJ_AST_LINES_ALL
SET current_asset_cost = pa_mc_currency_pkg.CurrRound((l_cost_ratio*original_asset_cost),
v_alc_ledger.currency_code)
WHERE project_asset_line_id = x_project_asset_line_id
AND set_of_books_id = v_alc_ledger.ledger_id;
end update_alc_proj_asset_lines;
SELECT gl.ledger_id, gl.currency_code
FROM gl_alc_ledger_rships_v gl,
pa_implementations imp
WHERE gl.source_ledger_id = imp.set_of_books_id
AND gl.application_id = 275
AND gl.org_id = imp.org_id
AND gl.relationship_enabled_flag = 'Y';
SELECT current_asset_cost
INTO l_fixed_assets_alc_cost
FROM PA_MC_PRJ_AST_LINES_ALL
WHERE project_asset_line_id = x_project_asset_line_id
AND set_of_books_id = v_alc_ledger.ledger_id;
INSERT into FA_MC_MASS_RATES
(
SET_OF_BOOKS_ID,
MASS_ADDITION_ID,
PARENT_MASS_ADDITION_ID,
FIXED_ASSETS_COST,
EXCHANGE_RATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
v_alc_ledger.ledger_id,
x_mass_addition_id,
x_parent_mass_addition_id,
l_fixed_assets_alc_cost,
l_exchange_rate,
x_created_by,
sysdate,
x_created_by,
sysdate,
x_last_update_login
);