[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Return : 'Y', if project information on the purchase order distribution can be updated.
'N', if project information on the purchase order distribution cannot be updated.
*/
FUNCTION Allow_Project_Info_Change ( p_po_distribution_id IN po_distributions_all.po_distribution_id%type)
RETURN varchar2 IS
l_sum_amount_interfaced number := 0;
select sum(nvl(entered_cr,0) - nvl(entered_dr,0))
into l_sum_amount_interfaced
from rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
where rcv_txn.po_distribution_id = l_po_distribution_id
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and rcv_sub.pa_addition_flag in ('Y','I')
and ((rcv_txn.destination_type_code ='EXPENSE') OR
/*and ((rcv_txn.destination_type_code ='EXPENSE' AND rcv_txn.transaction_type <> 'RETURN TO RECEIVING') OR */--Bug4630478
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) );
select 1
into l_uninterfaced_to_pa
FROM dual
WHERE EXISTS
(SELECT 1 FROM rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
,po_distributions_all podist/*Bug 3905697*/
where rcv_txn.po_distribution_id = l_po_distribution_id
and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and ((rcv_txn.destination_type_code ='EXPENSE') OR
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
and rcv_sub.pa_addition_flag ='N');
select 1
into l_uninterfaced_to_pa
FROM dual
WHERE EXISTS
(SELECT 1 FROM rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
,po_distributions_all podist/*Bug 3905697*/
where rcv_txn.po_distribution_id = l_po_distribution_id
and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and ((rcv_txn.destination_type_code ='EXPENSE') OR
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
and rcv_sub.pa_addition_flag ='N');
select nvl(clm_flag,'N'), nvl(LINE_NUM_DISPLAY,'XXXX'), to_char(line_num)
into v_clm_flag,
v_clm_line_num,
v_line_num
from po_doc_style_headers st, po_headers_all poh, po_lines_all pol
where poh.style_id=st.style_id
and poh.po_header_id= pol.po_header_id
and pol.po_line_id=p_po_line_id;
/*This is a public API, which will update PA_ADDITION_FLAG in
rcv_receiving_sub_ledger table. This API will be called from
purchasing module at the time of receipt creation.*/
PROCEDURE Update_PA_Addition_Flg (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
p_commit IN VARCHAR2 default FND_API.G_FALSE,
p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_transaction_id IN NUMBER,
p_po_distribution_id IN NUMBER,
p_accounting_event_id IN NUMBER)
IS
/*l_project_id po_distributions_all.project_id%type; Bug 5585218 */
SELECT /*+ leading(rcv_txn) index(rcvsub RCV_RECEIVING_SUB_LEDGER_N1) */ rcv_txn.transaction_id /*4338075*/
,rcv_txn.po_distribution_id
,rcvsub.accounting_event_id -- pricing changes
FROM rcv_transactions rcv_txn
,po_distributions podist
,rcv_receiving_sub_ledger rcvsub
WHERE rcv_txn.transaction_id = rcvsub.rcv_transaction_id
AND rcv_txn.parent_transaction_id = (SELECT parent_transaction_id
FROM rcv_transactions rcv_txn3
WHERE rcv_txn3.transaction_id = p_transaction_id)
and rcv_txn.po_distribution_id = podist.po_distribution_id
and podist.code_combination_id = rcvsub.code_combination_id
and rcvsub.actual_flag = 'A'
and podist.accrue_on_receipt_flag = 'Y'
/*and podist.project_id = p_project_id Bug 5585218 */
and rcvsub.pa_addition_flag = 'N' -- pricing changes
and ((rcv_txn.destination_type_code = 'EXPENSE' ) OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
rcv_txn.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
))
and 0 = (SELECT /*+ INDEX(RCV_TXN2 RCV_TRANSACTIONS_N1) */sum(nvl(rcvsub2.entered_dr,0)-nvl(rcvsub2.entered_cr,0))/*4338075*/
FROM rcv_transactions rcv_txn2
,rcv_receiving_sub_ledger rcvsub2
,po_distributions podist2
WHERE rcv_txn2.transaction_id = rcvsub2.rcv_transaction_id
and podist2.po_distribution_id = rcv_txn2.po_distribution_id
and podist2.code_combination_id = rcvsub2.code_combination_id
and rcvsub2.actual_flag = 'A'
and rcv_txn2.parent_transaction_id = rcv_txn.parent_transaction_id
and rcvsub2.code_combination_id = rcvsub.code_combination_id
and trunc(rcv_txn2.transaction_date) = trunc(rcv_txn.transaction_date)
and rcvsub2.pa_addition_flag = 'N' -- pricing changes
and rcv_txn2.po_distribution_id = rcv_txn.po_distribution_id
and ((rcv_txn2.destination_type_code = 'EXPENSE' ) OR
(rcv_txn2.destination_type_code = 'RECEIVING' AND
rcv_txn2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
))
);
l_rcv_txn_id_tbl.delete;
l_po_dist_id_tbl.delete;
l_rcv_acct_evt_tbl.delete; -- pricing changes
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'Z'
WHERE rcv_sub.rcv_transaction_id = l_rcv_txn_id_tbl(i) --pricing changes
AND rcv_sub.pa_addition_flag = 'N'
AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_tbl(i) --pricing changes
OR rcv_sub.accounting_event_id IS NULL); --pricing changes
-- write_log (LOG,'Total number of transctions updated to Z:'||l_num_rows);
l_rcv_txn_id_tbl.delete;
l_po_dist_id_tbl.delete;
l_rcv_acct_evt_tbl.delete; -- pricing changes
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = NULL
WHERE rcv_sub.pa_addition_flag ='N'
AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
AND EXISTS
(
SELECT 'X'
FROM rcv_transactions rcv_txn
WHERE rcv_txn.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
AND ((rcv_txn.destination_type_code IN ('INVENTORY','MULTIPLE','SHOP FLOOR')
OR
(rcv_txn.destination_type_code = 'RECEIVING'
AND
(rcv_txn.transaction_type NOT IN ('RETURN TO VENDOR','RETURN TO RECEIVING')
)
)
)
OR
(EXISTS
(SELECT po_distribution_id
FROM po_distributions po_dist
WHERE po_dist.po_distribution_id = rcv_txn.po_distribution_id
AND ((rcv_txn.destination_type_code = 'EXPENSE' AND
po_dist.project_id IS NULL)
OR
(rcv_txn.destination_type_code = 'EXPENSE' AND
nvl(po_dist.project_id,0) > 0 AND
po_dist.accrue_on_receipt_flag = 'N')
OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
po_dist.project_id IS NULL)
OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
po_dist.project_id IS NOT NULL AND
po_dist.accrue_on_receipt_flag = 'N')
)
)
) OR
( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
AND EXISTS (SELECT 'X'
FROM mtl_system_items si,
po_lines_all pol,
po_distributions_all po_dist1
WHERE po_dist1.po_line_id = pol.po_line_id
AND po_dist1.po_distribution_id = rcv_txn.po_distribution_id
AND si.inventory_item_id = pol.item_id
AND po_dist1.project_id IS NOT NULL
AND si.comms_nl_trackable_flag = 'Y')
) OR
(
rcv_sub.actual_flag <> 'A'
)
)
);
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_Addition_Flag = 'X'
WHERE rcv_sub.pa_addition_flag IN ('N','I')
AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
AND EXISTS
( SELECT po_dist.code_combination_id
FROM Rcv_Transactions rcv_txn, PO_Distributions po_dist
WHERE
(
(rcv_txn.destination_type_code ='EXPENSE' )
OR (rcv_txn.destination_type_code = 'RECEIVING'
AND (rcv_txn.transaction_type
IN ('RETURN TO VENDOR','RETURN TO RECEIVING')))
)
-- AND rcv_txn.transaction_date <= nvl(G_GL_DATE,rcv_txn.transaction_date)
AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
AND rcv_sub.code_combination_id <> po_dist.code_combination_id
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
AND rcv_sub.actual_flag = 'A'
-- AND po_dist.expenditure_item_date <= nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)
AND po_dist.project_ID > 0
AND po_dist.accrue_on_receipt_flag= 'Y') ;
END Update_PA_Addition_Flg;
select count(*)
into l_user_c
from per_all_people_f papf
where papf.person_id=(select max(employee_id) from fnd_user where user_id = x_proj_user_id)
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and papf.person_id in
(
select f.person_id
from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
,per_all_people_f f, pa_resource_txn_attributes ptn
where pp.resource_id = pr.resource_id
and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
and pp.project_role_id = ppr.project_role_id
and ptn.resource_id=pp.resource_id
and f.person_id=ptn.person_id
and pp.project_id=x_proj_id);
/* Function to impose project based security in Procurement.It has two modes :UPDATE and VIEW
* UPDATE : This mode should check if a person has access to 'ALL' the projects
* in a PO.
* VIEW :This mode should check if person has access to atleast 'ONE' project on
* a PO.
* This is used to restrict the access on PO for which the user is not key member on included projects.
*This will be called from PO context and the profile PO_ENFORCE_PROJ_SECURITY will be used to enforce the same.
*/
FUNCTION PA_USER_PO_ACCESS_CHECK(x_po_header_id IN NUMBER,
x_proj_user_id IN NUMBER,
x_mode IN VARCHAR2 DEFAULT 'VIEW' /* Mode can have 2 values 'VIEW' or 'UPDATE'*/
)
RETURN VARCHAR2 IS
l_profile_value VARCHAR2(1) := NULL;
select distinct project_id from po_distributions_all
where po_header_id = x_po_header_id
and project_id is not NULL; /* added condition for 8920005 */
if x_mode = 'UPDATE' then
open c_proj;
select count(*)
into l_user_c
from per_all_people_f papf
where papf.person_id=x_proj_user_id
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and papf.person_id in
(
select f.person_id
from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
,per_all_people_f f, pa_resource_txn_attributes ptn
where pp.resource_id = pr.resource_id
and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
and pp.project_role_id = ppr.project_role_id
and ptn.resource_id=pp.resource_id
and f.person_id=ptn.person_id
and pp.project_id=l_proj_id);
end if; --end for mode=update
select count(*)
into l_user_c
from per_all_people_f papf
where papf.person_id=x_proj_user_id
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and papf.person_id in
(
select f.person_id
from pa_project_parties pp, pa_resources pr,PA_PROJ_ROLES_V ppr
,per_all_people_f f, pa_resource_txn_attributes ptn
where pp.resource_id = pr.resource_id
and trunc(sysdate) between pp.start_date_active and Nvl(pp.end_date_active, Sysdate + 1) -- Bug 8943693
and pp.project_role_id = ppr.project_role_id
and ptn.resource_id=pp.resource_id
and f.person_id=ptn.person_id
and pp.project_id=l_proj_id);
SELECT DISTINCT ppa.segment1 -- Added for the bug 13617899
FROM po_distributions_all pod ,
pa_budget_versions pbv ,
pa_budgetary_control_options pbco,
pa_budget_entry_methods pbe ,
pa_budget_lines pbl ,
pa_resource_assignments pra ,
pa_resource_list_members prlm,
pa_projects_all ppa -- Added for the bug 13617899
WHERE pod.po_header_id =p_po_header_id
AND ppa.project_id = pod.project_id -- Added for the bug 13617899
AND pod.project_id = pbv.project_id
AND pod.project_id =pbco.project_id
AND pbco.bdgt_cntrl_flag ='Y'
AND pbv.budget_type_code ='AC'
AND ((pbv.budget_status_code ='B'
AND pbv.current_flag ='Y')
OR (pbv.budget_status_code ='W')) -- Modified for the bug 13566375
AND pbe.budget_entry_method_code =pbv.budget_entry_method_code
AND pbe.categorization_code ='R'
AND pbl.budget_version_id =pbv.budget_version_id
AND pbl.resource_assignment_id =pra.resource_assignment_id
AND prlm.resource_list_member_id =pra.resource_list_member_id
AND prlm.vendor_id =p_vendor_id;
SELECT DISTINCT rsh.receipt_num, rsh.creation_date -- Added for the bug 13617899
FROM Rcv_Transactions rcv_txn ,
rcv_shipment_headers rsh ,
PO_Distributions_all po_dist ,
rcv_receiving_sub_ledger rcv_sub1
WHERE po_dist.po_header_id =p_po_header_id
AND rcv_txn.destination_type_code ='EXPENSE'
AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
AND rcv_sub1.code_combination_id = po_dist.code_combination_id
AND rcv_sub1.rcv_transaction_id = rcv_txn.transaction_id
AND rsh.shipment_header_id = rcv_txn.shipment_header_id
AND po_dist.accrue_on_receipt_flag = 'Y'
AND rcv_sub1.pa_addition_flag = 'N'
AND rcv_sub1.actual_flag = 'A';
SELECT DISTINCT ppd.po_number, ppd.creation_date -- Added for the bug 13617899
FROM pa_proj_po_distributions ppd
WHERE ppd.po_header_id=p_po_header_id
AND ppd.cmt_qty <> 0; -- Modified for the bug 13566375
SELECT DISTINCT ppa.segment1, pbt.budget_type -- Added for the bug 13617899
FROM pa_budgetary_controls pbc,
pa_resource_list_members prlm,
pa_projects_all ppa,
pa_budget_types pbt,
pa_budget_versions pbv
WHERE prlm.vendor_id = p_vendor_id
AND ppa.project_id=pbc.project_id
AND pbv.project_id=pbc.project_id
AND ((pbv.budget_status_code ='B'
AND pbv.current_flag ='Y')
OR (pbv.budget_status_code ='W'))
AND pbt.budget_type_code=pbc.budget_type_code
AND pbc.resource_list_member_id=prlm.resource_list_member_id;
SELECT DISTINCT OD.business_document_number, OD.creation_date -- Added for the bug 13617899
FROM OKC_DELIVERABLES OD ,
hz_parties HP ,
HR_ALL_ORGANIZATION_UNITS org ,
okc_resp_parties_tl resp_party_tl
WHERE OD.BUSINESS_DOCUMENT_ID = p_po_header_id
AND OD.BUSINESS_DOCUMENT_TYPE = 'PO_STANDARD'
AND OD.external_party_contact_id = HP.party_id (+)
AND OD.internal_party_id = org.organization_id (+)
AND OD.DELIVERABLE_STATUS IN ('OPEN','SUBMITTED')
AND OD.MANAGE_YN = 'Y'
AND OD.responsible_party = resp_party_tl.resp_party_code
AND resp_party_tl.language = USERENV('LANG')
AND resp_party_tl.document_type_class = 'PO';
CURSOR c_update_doc(p_po_header_id IN NUMBER) IS
SELECT DISTINCT pcsd.ci_id, pcsd.creation_date -- Added for the bug 13617899
FROM PA_CI_SUPPLIER_DETAILS pcsd,
PA_CONTROL_ITEMS pci
WHERE pcsd.PO_HEADER_ID = p_po_header_id
AND pcsd.CI_ID = pci.CI_ID
AND pci.STATUS_CODE NOT IN ('CI_APPROVED', 'CI_CANCELED', 'CI_CLOSED' );
SELECT segment1
INTO l_po_number
FROM po_headers_all
WHERE po_header_id=l_po_header_id;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
SELECT NVL(pp.pji_source_flag,'N')
INTO l_pji_source_flag
FROM po_headers_all poh,
po_distributions_all pod,
pa_projects_all pp
WHERE poh.po_header_id=l_po_header_id
AND poh.po_header_id =pod.po_header_id
AND pod.project_id =pp.project_id;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
FOR erec IN c_update_doc(l_po_header_id) LOOP
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;