[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER(15);
l_last_update_date DATE;
SELECT ctb.transaction_source ,
ctb.expenditure_type ,
ctb.system_linkage_function ,
NVL(ctb.use_project_rate_flag,'N') ,
csd.cost_id ,
csd.transaction_date ,
csd.quantity ,
csd.extended_cost ,
csd.currency_code ,
csd.inventory_item_id ,
csd.unit_of_measure_code ,
ced.project_id ,
ced.project_task_id,
ced.estimate_detail_id,
ced.source_code,
ced.source_id,
ced.expenditure_org_id,
(SELECT segment1 from pa_projects_all pp where pp.project_id =ced.project_id ) ProjectNumber,
(SELECT task_number from pa_tasks pt where pt.task_id =ced.project_task_id ) ProjectTaskNumber,
csd.org_id,
cia.incident_number
FROM cs_cost_details csd,
cs_estimate_details ced,
cs_txn_billing_types ctb,
cs_incidents_all_b cia
WHERE cia.incident_id = ced.incident_id
AND ced.estimate_detail_id = csd.estimate_detail_id
AND csd.txn_billing_type_id = ctb.txn_billing_type_id
AND nvl(ctb.interface_to_pa_flag,'N')='Y'
-- and cia.incident_number ='68584' --remove this later
AND csd.transaction_date BETWEEN NVL(p_creation_from_date,csd.transaction_date-1) AND NVL(p_creation_to_date,csd.transaction_date+1)
AND ced.project_id = NVL(p_project_id,ced.project_id)
AND ced.project_task_id = NVL(p_project_task_id,ced.project_task_id)
AND csd.transaction_type_id = NVL(p_transaction_type_id, csd.transaction_type_id)
AND cia.incident_status_id = NVL(p_incident_status_id, cia.incident_status_id)
AND cia.incident_id = NVL(p_incident_id, cia.incident_id)
AND csd.source_code = NVL(p_source_code,csd.source_code)
AND csd.source_id = NVL(p_source_number,csd.source_id)
AND csd.org_id = NVL(p_org_id , csd.org_id)
AND csd.inventory_org_id = NVL(p_inv_org_id , csd.inventory_org_id)
AND ced.expenditure_org_id = NVL(p_expenditure_org_id,ced.expenditure_org_id)
AND ctb.billing_type = NVL(p_billing_type , ctb.billing_type) --bug 16806860
--and csd.cost_id= 39302;
SELECT pap.employee_number
INTO l_employee_num
FROM cs_estimate_details ced,
fnd_user fu ,
per_all_people_f pap
WHERE ced.created_by = fu.user_id
AND pap.person_id = fu.employee_id
AND ced.estimate_detail_id = l_estimate_detail_id
AND to_date(ced.creation_date) between nvl(pap.effective_start_date,sysdate) and nvl(pap.effective_end_date,sysdate);
SELECT pap.employee_number
INTO l_employee_num
FROM csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_rs_resource_extns jrr,
fnd_user fu ,
PER_ALL_PEOPLE_F pap
WHERE cdh.task_assignment_id = jta.task_assignment_id
AND cdl.debrief_header_id = cdh.debrief_header_id
AND cdl.debrief_line_id = l_source_id
AND jrr.resource_id = jta.resource_id
AND pap.person_id = fu.employee_id
AND fu.user_id = jrr.user_id ;
SELECT name
INTO l_organization_name
FROM hr_all_organization_units --need to clarify whether to use hr_organization_units
WHERE organization_id = (select expenditure_org_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
SELECT segment1
INTO l_project_num
FROM pa_projects_all
WHERE project_id = (select project_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
SELECT task_number
INTO l_task_num
FROM pa_tasks where task_id = (select project_task_id from cs_estimate_details where estimate_detail_id =l_estimate_detail_id );
select projfunc_currency_code
INTO l_project_currency
FROM PA_PROJECTS_ALL
WHERE project_id = l_project_id;
LAST UPDATED BY
*/
l_last_updated_by := FND_GLOBAL.USER_ID;
LAST UPDATE DATE
*/
l_last_update_date := SYSDATE;
select nvl(costed_flag ,'N')
into l_costed_flag
from pa_transaction_sources
where SYSTEM_LINKAGE_FUNCTION = l_system_linkage
and transaction_source = l_transaction_source;
SELECT fu.person_party_id
-- jrr.user_id,
-- fu.user_name
INTO l_person_id
FROM csf_debrief_lines cdl,
csf_debrief_headers cdh,
jtf_task_assignments jta,
jtf_rs_resource_extns jrr,
fnd_user fu
WHERE cdh.task_assignment_id = jta.task_assignment_id
AND cdl.debrief_header_id = cdh.debrief_header_id
AND cdl.debrief_line_id = l_estimate_Detail_id -- this needs to be changed
AND jrr.resource_id = jta.resource_id
AND fu.user_id = jrr.user_id ;
/*SELECT fu.person_party_id
INTO l_person_id
FROM cs_estimate_details ced,
fnd_user fu
WHERE ced.created_by = fu.user_id
AND ced.estimate_detail_id = l_estimate_Detail_id;-- this needs to be changed*/
SELECT pap.person_id
INTO l_person_id
FROM cs_estimate_details ced,
fnd_user fu ,
per_all_people_f pap
WHERE ced.created_by = fu.user_id
AND pap.party_id = fu.person_party_id
AND ced.estimate_detail_id = l_estimate_Detail_id-- this needs to be changed
AND ced.creation_date between nvl(pap.effective_start_date,sysdate) and nvl(pap.effective_end_date,sysdate);
SELECT pap. business_group_id
into l_business_group_id
FROM cs_estimate_details ced,
fnd_user fu ,
PER_ALL_PEOPLE_F pap
WHERE ced.created_by = fu.user_id
AND pap.person_id = fu.employee_id
AND ced.estimate_detail_id = l_estimate_detail_id and rownum=1;
Select name into
l_business_group_name
from hr_all_organization_units
where organization_id =l_business_group_id and rownum=1;
, L_LOG_MODULE || 'Before inserting into PA_TRANSACTION_INTERFACE_ALL table'
, 'Begins'
);
Insert into PA_TRANSACTION_INTERFACE_ALL
(
transaction_source, --1
batch_name , --2
expenditure_ending_date, --3
employee_number, --4
organization_name, --5
expenditure_item_date, --6
project_number , --7
task_number, --8
expenditure_type, --9
non_labor_resource, --10
non_labor_resource_org_name,--11
quantity, --12
raw_cost, --13
expenditure_comment, --14
transaction_status_code , --15
transaction_rejection_code, --16
expenditure_id, --17
orig_transaction_reference, --18
attribute_category , --19
attribute1, --20
attribute2, --20
attribute3, --20
attribute4, --20
attribute5, --20
attribute6, --20
attribute7, --20
attribute8, --20
attribute9, --20
attribute10, --20
raw_cost_rate, --21
interface_id, --22
unmatched_negative_txn_flag, --23
expenditure_item_id, --24
org_id, --25
dr_code_combination_id , --26
cr_code_combination_id , --27
cdl_system_reference1, --28
cdl_system_reference2, --28
cdl_system_reference3, --28
cdl_system_reference4, --28
cdl_system_reference5, --28
gl_date, --29
burdened_cost, --30
burdened_cost_rate, --31
system_linkage, --32
txn_interface_id, --33
user_transaction_source, --34
created_by, --35
creation_date, --36
last_updated_by, --37
last_update_date, --38
receipt_currency_amount, --39
receipt_currency_code, --40
receipt_exchange_rate, --41
denom_currency_code, --42
denom_raw_cost, --43
denom_burdened_cost, --44
acct_rate_date, --45
acct_rate_type, --46
acct_exchange_rate, --47
acct_raw_cost, --48
acct_burdened_cost, --49
acct_exchange_rounding_limit,--50
project_currency_code, --51
project_rate_date, --52
project_rate_type, --53
project_exchange_rate, --54
orig_exp_txn_reference1, --55
orig_exp_txn_reference2 , --56
orig_exp_txn_reference3, --57
orig_user_exp_txn_reference,--58
vendor_number , --59
override_to_organization_name,--60
reversed_orig_txn_reference, --61
billable_flag, --62
person_business_group_name, --63
projfunc_currency_code, --64
projfunc_cost_rate_type, --65
projfunc_cost_rate_date, --66
projfunc_cost_exchange_rate, --67
project_raw_cost, --68
project_burdened_cost, --69
assignment_name, --70
work_type_name, --71
accrual_flag, --72
project_id, --73
task_id, --74
person_id, --75
organization_id, --76
non_labor_resource_org_id, --77
vendor_id, --78
override_to_organization_id, --79
assignment_id, --80
work_type_id, --81
person_business_group_id, --82
inventory_item_id, --83
wip_resource_id, --84
unit_of_measure, --85
po_number, --86
po_header_id , --87
po_line_num, --88
po_line_id, --89
person_type, --90
po_price_type, --91
adjusted_expenditure_item_id, --92
fc_document_type, --93
document_type, --94
document_distribution_type, --95
si_assets_addition_flag, --96
sc_xfer_code, --97
adjusted_txn_interface_id, --98
net_zero_adjustment_flag --99
)
Values
(
l_transaction_source, --1 comes from cs_projects.transaction_source
l_batch_name, --2 harcoded
l_exp_ending_date , --3 Function newgetweekending(p_date in date) return date
l_employee_num, --4 N/A - populate the person_id instead of this
l_organization_name, --5 N/A - This is the name of the expenditure_org_id
l_expenditure_item_date, --6 cs_cost_details.transaction_date
l_project_number, --7 N/A - populate the project_id instead of this
l_project_task_number, --8 N/A - populate the task_id instead of this
l_expenditure_type , --9 comes from cs_projects.expenditure_type
null, --10 N/A for service
null, --11 N/A for service
l_quantity, --12 cs_cost_details.quantity
l_raw_cost , --13 cs_cost_details.extended_cost (cost should be in the OUs functional currency)
null, --14 N/A for service
l_transaction_status_code, --15 harcoded
null, --16 Projects will assign this
null, --17 Projects will assign this
l_orig_ref, --18 cs_cost_details.cost_id
null, --19 N/A for service Attribute 1--10
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --20 N/A for service
null, --21 N/A - we have provided total cost , so this is not applciable
null, --22 Projects will assign this
l_UNMAT_NEG_TXN_FLAG, --23
null, --24 Projects will assign this
l_cost_org_id, --25 cs_cost_details.Operating Unit on the cost record
null, --26 N/A for service , costs will go as unaccounted , this column is the ID of the GL debit account.
null, --27 N/A for service , costs will go as unaccounted , this column is the ID of the GL credit account.
null, --28 N/A for service ,the reference to the record in the external system if it has already been accounted for and interfaced to Oracle General Ledger
null, --28
null, --28
null, --28
null, --28
null, --29 N/A for service
null, --30 N/A for service
null, --31 N/A for service
l_system_linkage, --32 cs_projects.function(expenditure type class_
null, --33 Projects will assign this (UNIQUE indentifier for this table)
null, --34 N/A
FND_GLOBAL.USER_ID, --35
sysdate, --36
FND_GLOBAL.USER_ID, --37
sysdate, --38
null, --39 N/A for service , as service does not handle receipts
null, --40 N/A for service , as service does not handle receipts
null, --41 N/A for service , as service does not handle receipts
l_denom_currency_code, --42 cs_cost_details.currency_code
l_raw_cost , --43 Service will calculate the qty x unit cost in the denom_currency_code and put the value in this column.
null, --44 NA for service
null, --45 NA for service
null, --46 NA for service
null, --47 NA for service
null, --48 NA for service
null, --49 NA for service
null, --50 NA for service
null, --51 Project will assign this
null, --52 NA for service
null, --53 NA for service
null, --54 NA for service
null, --55 NA for service
null, --56 NA for service
null, --57 NA for service
null, --58 NA for service
null, --59 NA for service
null, --60 NA for service
null, --61 NA for service
'N' , --62 hard coded service wil not bill customers thru projects
l_business_group_name, --63 NA for service
null, --64 NA for service
null, --65 NA for service
null, --66 NA for service
null, --67 NA for service
null, --68 NA for service
null, --69 NA for service
null, --70 NA for service
null, --71 NA for service
null, --72 NA for service
null, --73 cs_incidents_all_b.project_id
null, --74 cs_incidents_all_b.project_id
null, --75 person_id mapped to the USER differs for SR and SD - should nto populate for SUPPLIER invoices
null, --76 expenditure_org_id
null, --77 NA for service
null, --78 Incase of SUPPLIER Invoices , person_id mapped to the USER differs for SR and SD (same as 73)
null, --79 NA for service
null, --80 NA for service
null, --81 NA for service
null, --82 NA for service
l_item_id, --83 cs_cost_details.inventory_item_id
null, --84 NA for service
l_uom, --85 cs_cost_details.unit_of_measure_code
null, --86 NA for service
null, --87 NA for service
null, --88 NA for service
null, --89 NA for service
null, --90 NA for service
null, --91 NA for service
null, --92 NA for service
null, --93 NA for service
null, --94 NA for service
null, --95 NA for service
null, --96 NA for service
null, --97 NA for service
null, --98 NA for service
null --99 NA for service
);
If sql%rowcount=1 then -- if record is successfully inserted
l_success_count := l_success_count+1;
Update cs_cost_details set interfaced_to_pa_flag = 'T'
where cost_id = l_cost_id;
, L_LOG_MODULE || 'After inserting into PA_TRANSACTION_INTERFACE_ALL table'
, 'ends'
);
FND_MSG_PUB.Delete_Msg;
Update cs_cost_details set interfaced_to_pa_flag = 'N'
where cost_id = l_cost_id;
Update cs_cost_details set interfaced_to_pa_flag = 'N'
where cost_id = l_cost_id;
, '' || 'Number of Cost Records successfully inserted into the Projects Interface Table' || ' '
|| l_success_count|| '
, '' || 'Number of Cost Records failed to be inserted into the Projects Interface Table' || ' '
|| l_failure_count|| '
||''||'Details of the Cost records that failed Insertion'||' '
);
/* select cost_id into l_err_cost_id
from cs_cost_details
where cost_id = l_err_cst_rec(i) ;*/
||''||'Details of the cost records that succeeded Insertion '||' '
);
/*select cost_id
into l_succ_cost_id
from cs_cost_details
where cost_id = l_succ_cst_rec(i) ;*/
Update cs_cost_details set interfaced_to_pa_flag = 'N'
where cost_id = l_cost_id;
PROCEDURE update_interface_status(P_transaction_source IN VARCHAR2,
P_batch IN VARCHAR2,
P_user_id IN NUMBER,
P_xface_id IN NUMBER) IS
l_cost_id NUMBER;
SELECT transaction_status_code ,orig_transaction_reference
FROM pa_transaction_interface_all
WHERE transaction_source = P_transaction_source
AND batch_name = P_batch
AND created_by = P_user_id
AND txn_interface_id = P_xface_id;
UPDATE cs_cost_details
set interfaced_to_pa_flag = decode(l_status_code,'I','Y',
'R','E')
where cost_id = l_cost_id;
UPDATE pa_transaction_interface_all
set transaction_status_code ='A'
where orig_transaction_reference = to_char(l_cost_id);
END update_interface_status;