[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Updates Work Type Id on
pa_projects_all,
pa_tasks,
pa_expenditure_items_all,
pa_cost_distribution_lines_all
Simultaneously, it also updates Tp Amt Type Code on
pa_expenditure_items_all,
pa_cc_dist_lines_all
pa_draft_invoice_details_all
In FP.M, it has been modified to support update
inventory_item_id, wip_resource_id, unit_of_measure
on pa_expenditure_items_all
Parameters: IN
P_Num_Of_Processes : User given number, that many processes will be spawned
P_Worker_Id : Holds the worker id
P_Org_Id : Holds the operating unit
P_Txn_Date : Holds the transaction start date
P_Txn_Type : Can be 'PJM' or 'WORK TYPE'.
if PJM, will update Project Manufacturing Attributes on EI table
P_Txn_Src : If given will update EI for the specified Transaction Source only.
--Added for R12 AP Lines uptake
P_Min_Project_Id : Holds the minimum of the project id range, internally used
P_Max_Project_Id : Holds the maximum of the project id range, internally used
OUT
X_Return_Status : Currently not used
X_Error_Message_Code : Currently not used
*/
Procedure Upgrade_WT_Main(
X_RETURN_STATUS OUT NOCOPY VARCHAR2
,X_ERROR_MESSAGE_CODE OUT NOCOPY VARCHAR2
,P_TXN_TYPE IN VARCHAR2
,P_TXN_SRC IN VARCHAR2
,P_NUM_OF_PROCESSES IN NUMBER
,P_WORKER_ID IN NUMBER
,P_ORG_ID IN NUMBER DEFAULT NULL
,P_TXN_DATE IN VARCHAR2
,P_Min_Project_Id IN NUMBER DEFAULT NULL
,P_Max_Project_Id IN NUMBER DEFAULT NULL
)
Is
l_child_req_id number;
Select P.Project_Id,
Pt.Work_Type_Id
From Pa_Projects_All P,
Pa_Project_Types_All Pt
Where P.Project_Type = Pt.Project_Type
And nvl(P.Org_Id, -99) = nvl(Pt.Org_Id, -99)
And Pt.Work_Type_Id is not NULL;
l_WorkTypeTab.delete;
l_PrjIdTab.delete;
update pa_projects_all
set work_type_id = l_WorkTypeTab(i)
where project_id = l_PrjIdTab(i)
and work_type_id is null;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Project Records updated = '||SQL%ROWCOUNT);
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Project Records updated = '||l_rowcount);
--dbms_output.PUT_LINE('No of Project Records updated = '||l_rowcount);
update pa_tasks
set work_type_id = l_WorkTypeTab(i)
where project_id = l_PrjIdTab(i)
and work_type_id is null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Task Records updated = '||l_rowcount);
SELECT Min(P.Project_Id) --, Max(P.Project_Id)
INTO Min_Project_Id --,Max_Project_Id
FROM Pa_Projects_All P
WHERE p.org_id = nvl(p_org_id,p.org_id);
SELECT Max(P.Project_Id)
INTO Max_Project_Id
FROM Pa_Projects_All P
WHERE p.org_id = nvl(p_org_id,p.org_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Insert into Temp Table '|| to_char(sysdate, 'HH:MI:SS'));
INSERT /*+ APPEND parallel(t) */
INTO pa_txn_upgrade_temp t
(row_id,
pk1_id,
worker_id,
pk2_id)
select /*+ parallel(ei) */ ei.rowid, ei.expenditure_item_id id, null , ei.task_id
from pa_expenditure_items_all ei
where trunc(ei.expenditure_item_date) >= trunc(l_Txn_Date)
and nvl(ei.org_id, -99) = nvl(p_org_id,nvl(ei.org_id,-99)) /* Changed for Bug #6129449 by anuragar */
and work_type_id is null;
FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount||' Records inserted for WORK TYPE '|| to_char(sysdate, 'HH:MI:SS'));
INSERT /*+ APPEND parallel(t) */
INTO pa_txn_upgrade_temp t
(row_id
,pk1_id
,worker_id
,pk2_id
,txn_src
,reference1
)
SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
, EI.orig_transaction_reference, EI.transaction_source, 'CCO'
FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
WHERE EI.transaction_source = NVL(P_Txn_Src, EI.transaction_source)
AND EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
,'PJM_NON_CSTBP_ST_ACCOUNTS')
AND EI.unit_of_measure IS NULL
AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
AND EI.expenditure_id = EXP.expenditure_id
AND EXP.orig_exp_txn_reference1 is null
UNION ALL
SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
, EXP.orig_exp_txn_reference1, EI.transaction_source, 'PODIST'
FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
,'PJM_NON_CSTBP_ST_ACCOUNTS')
AND EI.unit_of_measure IS NULL
AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
AND EI.expenditure_id = EXP.expenditure_id
AND EXP.orig_exp_txn_reference1 is not null
AND EXP.orig_exp_txn_reference2 is not null
UNION ALL
SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
, CDL.system_reference1, EI.transaction_source, 'PODIST'
FROM pa_expenditure_items_all EI, pa_expenditures_all EXP, pa_cost_distribution_lines_all CDL
WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
,'PJM_NON_CSTBP_ST_ACCOUNTS')
AND EI.unit_of_measure IS NULL
AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
AND EI.expenditure_id = EXP.expenditure_id
AND EXP.orig_exp_txn_reference1 is not null
AND EXP.orig_exp_txn_reference2 is null
AND EI.expenditure_item_id = CDL.expenditure_item_id
AND CDL.line_num = 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount || ' Records inserted for PJM '|| to_char(sysdate, 'HH:MI:SS'));
UPDATE /*+ parallel(t) */ pa_txn_upgrade_temp t
SET worker_id = (ceil(rownum / ceil(l_ins_rowcount / p_num_of_processes )));
l_ReqStsTab.delete; -- PJM Changes
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successfully Updated UOM Meanings in PA '|| to_char(sysdate,'HH:MI:SS'));
Select Temp.Pk1_Id,
Tsk.Work_Type_Id,
Wt.Tp_Amt_Type_Code
From pa_txn_upgrade_temp Temp,
Pa_Tasks Tsk,
Pa_Work_Types_B Wt
Where Worker_Id = p_worker_id
And Temp.Pk2_id = Tsk.Task_Id
And Tsk.Work_Type_Id = Wt.Work_Type_Id;
SELECT MMT.Inventory_Item_Id Inventory_Item_ID
,to_number(NULL) Wip_Resource_Id
,MSI.Primary_UOM_Code UOM
,Temp.Pk1_Id
FROM MTL_MATERIAL_TRANSACTIONS MMT
,MTL_SYSTEM_ITEMS MSI
,Pa_Txn_Upgrade_Temp Temp
WHERE Temp.Worker_Id = p_worker_id
And Temp.Pk2_id = MMT.Transaction_Id
AND MMT.Inventory_Item_Id = MSI.Inventory_Item_Id
AND MMT.Organization_Id =MSI.Organization_Id
AND Temp.Txn_Src IN ('Inventory Misc', 'Inventory'
,'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS')
AND Temp.Reference1 = 'CCO'
UNION ALL
SELECT to_number(NULL) Inventory_Item_ID
,WT.Resource_Id Wip_Resource_ID
,WT.Primary_UOM UOM
,Temp.Pk1_ID
FROM WIP_TRANSACTIONS WT
,Pa_Txn_Upgrade_Temp Temp
WHERE Temp.Worker_Id = p_worker_id
And Temp.Pk2_ID = WT.Transaction_ID
AND Temp.Txn_Src IN ('Work In Process'
,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
,'PJM_NON_CSTBP_ST_ACCOUNTS')
AND Temp.Reference1 = 'CCO'
UNION ALL
SELECT decode(PoDist.destination_type_code, 'INVENTORY' , PoLine.Item_Id, null) Inventory_Item_ID ,
decode(PoDist.destination_type_code, 'SHOP FLOOR', PoDist.Bom_Resource_Id, null) Wip_Resource_ID ,
'DOLLARS' UOM ,
Temp.Pk1_Id
FROM Pa_Txn_Upgrade_Temp Temp,
Po_Distributions_All PoDist,
Po_Lines_All PoLine
WHERE Temp.Worker_Id = p_worker_id
AND Temp.Pk2_Id = PoDist.po_distribution_id
AND PoDist.Po_Line_Id = PoLine.Po_Line_Id
AND Temp.Txn_Src in ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS',
'Work In Process','PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS',
'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS','PJM_NON_CSTBP_ST_ACCOUNTS')
AND Temp.Reference1 = 'PODIST';
SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
ei.rowid,
cdl.expenditure_item_id,
to_number(cdl.system_reference1) vendor_id,
inv.invoice_id doc_header_id,
dist.invoice_distribution_id doc_dist_id,
dist.invoice_line_number doc_line_num,
NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4) doc_payment_id,
inv.invoice_type_lookup_code doc_type,
dist.line_type_lookup_code dist_type
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
ap_invoice_distributions_all dist,
ap_invoices_all inv
WHERE cdl.expenditure_item_id = ei.expenditure_item_id
AND inv.invoice_id = to_number(cdl.system_reference2)
AND dist.invoice_id = to_number(cdl.system_reference2)
AND dist.invoice_id = inv.invoice_id
AND dist.project_id > 0
AND dist.old_dist_line_number = to_number(cdl.system_reference3)
AND ((dist.line_type_lookup_code = decode(ei.transaction_source,'AP VARIANCE',cdl.system_reference4,
'AP INVOICE','ITEM',
'AP NRTAX','NONREC_TAX',dist.line_type_lookup_code)
AND ei.transaction_source <> 'AP DISCOUNTS'
OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP VARIANCE','T'||cdl.system_reference4))
-- Commented for the bug 12566440 (start)
/* OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','PREPAY')
OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','FREIGHT') /* 8547295 : Added clause */
/*OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','MISCELLANEOUS') /* 8547295 : Added clause */
/*OR ei.transaction_source = 'AP DISCOUNTS' and dist.line_type_lookup_code NOT IN ('TERV','TRV','ERV','IPV','TIPV'))*/
-- Commented for the bug 12566440 (end)
-- Added below condition for the bug 12566440 (start)
OR (ei.transaction_source = 'AP INVOICE' AND dist.line_type_lookup_code in ('PREPAY','FREIGHT','MISCELLANEOUS','NONREC_TAX','ACCRUAL')) /*Added for bug#14097178 */
OR (ei.transaction_source = 'AP EXPENSE' AND dist.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS','NONREC_TAX'))
OR (ei.transaction_source = 'AP DISCOUNTS' AND dist.line_type_lookup_code NOT IN ('TERV','TRV','ERV','IPV','TIPV')))
-- Added above condition for the bug 12566440 (end)
AND cdl.system_reference2 IS NOT NULL
AND cdl.system_reference3 IS NOT NULL
AND cdl.line_type ='R'
AND cdl.reversed_flag IS NULL
AND cdl.line_num_reversed IS NULL
AND ei.document_header_id IS NULL
AND nvl(ei.historical_flag,'Y') = 'Y'
AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
AND ei.project_id = p_project_id
AND ei.transaction_source in ('AP EXPENSE', 'AP INVOICE', 'INTERCOMPANY_AP_INVOICES', 'INTERPROJECT_AP_INVOICES', 'AP NRTAX',
'AP VARIANCE', 'AP DISCOUNTS' ,'AP ERV') /* changed for bug 9320194 */
UNION ALL
SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
ei.rowid,
cdl.expenditure_item_id,
to_number(cdl.system_reference1) vendor_id,
to_number(cdl.system_reference2) doc_header_id,
to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4)) doc_dist_id,
to_number(cdl.system_reference3) doc_line_num,
null doc_payment_id,
rcv.destination_type_code doc_type,
rcv.transaction_type dist_type
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
rcv_transactions rcv
WHERE cdl.expenditure_item_id = ei.expenditure_item_id
AND rcv.transaction_id = to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4))
AND rcv.po_distribution_id = to_number(cdl.system_reference3)
AND cdl.system_reference2 IS NOT NULL
AND cdl.system_reference3 IS NOT NULL
AND cdl.line_type ='R'
AND cdl.reversed_flag IS NULL
AND cdl.line_num_reversed IS NULL
AND ei.document_header_id IS NULL
AND nvl(ei.historical_flag,'Y') = 'Y'
AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
AND ei.project_id = p_project_id
AND ei.transaction_source like 'PO %';
SELECT ei.rowid,
ei.expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE ei.cost_distributed_flag = 'N'
ANd ei.document_header_id IS NULL
AND nvl(ei.historical_flag,'Y') = 'Y'
AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
AND ei.project_id = p_project_id
AND (ei.transaction_source in ('AP EXPENSE', 'AP INVOICE', 'INTERCOMPANY_AP_INVOICES', 'INTERPROJECT_AP_INVOICES', 'AP NRTAX',
'AP VARIANCE', 'AP DISCOUNTS' ,'AP ERV') OR ei.transaction_source like 'PO %') /* changed for bug 9320194 */
AND NOT EXISTS ( SELECT NULL
FROM pa_cost_distribution_lines_all cdl
WHERE cdl.expenditure_item_id = ei.expenditure_item_id);
SELECT project_id
FROM pa_projects_all
WHERE project_id between P_Min_Project_Id and P_Max_Project_Id;
SELECT ei.rowid,
ei.expenditure_item_id,
e.vendor_id
FROM pa_expenditures_all e,
pa_expenditure_items_all ei
WHERE e.expenditure_id =ei.expenditure_id
and ei.document_header_id is null
AND ei.cost_distributed_flag = 'Y'
AND NVL(EI.HISTORICAL_FLAG,'Y') = 'Y'
AND ei.project_id = p_project_id
AND ei.system_linkage_function = 'BTC'
AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
and ei.burden_sum_dest_run_id > 0
and (ei.vendor_id is null AND e.vendor_id IS NOT NULL);
l_EIIdTab.delete;
l_WtIdTab.delete;
l_WtTpAmtTab.delete;
l_InvItmIdTab.delete;
l_WIPIdTab.delete;
l_UOMTab.delete;
update pa_expenditure_items_all
set work_type_id = l_WtIdTab(i),
tp_amt_type_code = l_WtTpAmtTab(i)
where Expenditure_Item_Id = l_EIIdTab(i);
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of EI Records updated = '||l_rowcount);
update pa_cost_distribution_lines_all
set work_type_id = l_WtIdTab(j)
where Expenditure_Item_Id = l_EIIdTab(j)
and line_type = 'R';
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CDL Records updated = '||l_rowcount);
update pa_cc_dist_lines_all
set tp_amt_type_code = l_WtTpAmtTab(k)
where Expenditure_Item_Id = l_EIIdTab(k)
and tp_amt_type_code is null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CCDL Records updated = '||l_rowcount);
update pa_draft_invoice_details_all
set tp_amt_type_code = l_WtTpAmtTab(l)
where Expenditure_Item_Id = l_EIIdTab(l)
and tp_amt_type_code is null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'No of DID Records updated = '||l_rowcount);
l_EIIdTab.delete;
l_WtIdTab.delete;
l_WtTpAmtTab.delete;
l_InvItmIdTab.delete;
l_WIPIdTab.delete;
l_UOMTab.delete;
update pa_expenditure_items_all
set inventory_item_id = l_InvItmIdTab(i)
,wip_resource_id = l_WIPIdTab(i)
,unit_of_measure = l_UOMTab(i)
where Expenditure_Item_Id = l_EIIdTab(i);
FND_FILE.PUT_LINE(FND_FILE.LOG, l_rowcount || ' EI Records updated '||to_char(sysdate,'HH:MI:SS'));
l_ei_rowid_tbl.delete;
l_exp_item_id_tbl.delete;
l_vendor_id_tbl.delete;
l_doc_header_id_tbl.delete;
l_doc_dist_id_tbl.delete;
l_doc_line_num_tbl.delete;
l_doc_payment_id_tbl.delete;
l_document_tbl.delete;
l_document_dist_tbl.delete;
UPDATE pa_expenditure_items_all ei
SET ei.vendor_id = l_vendor_id_tbl(i),
ei.last_update_date = sysdate,
ei.document_header_id = l_doc_header_id_tbl(i),
ei.document_distribution_id = l_doc_dist_id_tbl(i),
ei.document_line_number = l_doc_line_num_tbl(i),
ei.document_payment_id = l_doc_payment_id_tbl(i),
ei.document_type = l_document_tbl(i),
ei.document_distribution_type = l_document_dist_tbl(i),
ei.historical_flag = decode(l_doc_header_id_tbl(i),NULL,NULL,nvl(ei.historical_flag,'Y'))
WHERE ei.rowid = l_ei_rowid_tbl(i);
UPDATE pa_expenditure_items_all ei
SET (ei.vendor_id,
ei.document_header_id,
ei.document_distribution_id,
ei.document_line_number,
ei.document_payment_id,
ei.document_type,
ei.document_distribution_type) = (
SELECT uei.vendor_id,
uei.document_header_id,
uei.document_distribution_id,
uei.document_line_number,
uei.document_payment_id,
uei.document_type,
uei.document_distribution_type
FROM pa_expenditure_items_all uei
WHERE uei.document_header_id >0
START WITH uei.expenditure_item_id = uncosted_ei.expenditure_item_id
CONNECT BY PRIOR NVL(uei.adjusted_expenditure_item_id,uei.transferred_from_exp_item_id)
= uei.expenditure_item_id
AND rownum = 1
),
ei.historical_flag = NVL(ei.historical_flag,'Y')
WHERE ei.rowid = uncosted_ei.rowid;
update pa_expenditure_items_all ei
set ei.vendor_id=costed_btc.vendor_id,
ei.last_update_date = sysdate
where ei.rowid = costed_btc.rowid
and ei.expenditure_item_id = costed_btc.expenditure_item_id;
fnd_file.put_line(fnd_file.log,'Updating costed BTC columns ends | lines updated ='||l_rowcount);