[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
* If a process is not passed then the selector function defined in
* item type will be determine which process to run
*/
IF ( ItemType is NOT NULL ) AND
( ItemKey is NOT NULL) AND
( req_header_id is NOT NULL ) THEN
--Bug 5490243. Removed the commit introduced in Bug 3293852
wf_engine.CreateProcess(itemtype => itemtype,
itemkey => itemkey,
process => workflow_process );
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
select org_id
into x_org_id
from po_requisition_headers
where requisition_header_id = req_header_id;
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select pol.requisition_line_id
from po_requisition_headers poh, po_requisition_lines pol
where line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
(poh.requisition_header_id = x_req_header_id
OR
x_req_header_id is null) AND
poh.requisition_header_id = pol.requisition_header_id
order by poh.requisition_header_id, line_num;
select pol.requisition_line_id
from po_requisition_headers_all poh, --
po_requisition_lines pol
where x_req_header_id is null AND
line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
poh.requisition_header_id = pol.requisition_header_id
union all
select pol.requisition_line_id
from po_requisition_headers_all poh, --
po_requisition_lines pol
where x_req_header_id is not null AND
poh.requisition_header_id = x_req_header_id AND
line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
poh.requisition_header_id = pol.requisition_header_id;
select pol.requisition_line_id
from po_requisition_headers_all poh, --
po_requisition_lines pol
where poh.requisition_header_id = x_req_header_id AND
line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
poh.requisition_header_id = pol.requisition_header_id
order by pol.requisition_line_id asc;
select pol.requisition_line_id
from po_requisition_headers_all poh, --
po_requisition_lines pol
where line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
poh.requisition_header_id = pol.requisition_header_id;
SELECT prl.requisition_line_id
FROM po_lines pol,
po_requisition_lines prl,
po_requisition_headers_all prh --
WHERE pol.po_header_id=l_consume_req_demand_doc_id
AND prl.auction_header_id = pol.auction_header_id
AND prl.bid_line_number = pol.bid_line_number
AND prl.bid_number = pol.bid_number
AND prl.line_location_id is null
AND nvl(prl.cancel_flag,'N') ='N'
AND nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(prl.modified_by_agent_flag,'N') ='N'
AND prl.source_type_code = 'VENDOR'
AND prh.authorization_status = 'APPROVED'
AND prh.requisition_header_id = prl.requisition_header_id;
select pol.requisition_line_id
from po_requisition_headers_all poh, --
po_requisition_lines pol
where poh.requisition_header_id = x_req_header_id AND
line_location_id is null AND
nvl(pol.cancel_flag,'N') ='N' AND
nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N') ='N' AND
source_type_code = 'VENDOR' AND
authorization_status = 'APPROVED' AND
poh.requisition_header_id = pol.requisition_header_id AND
pol.GROUP_LINE_ID IS NULL AND
pol.CLM_BASE_LINE_NUM is NULL AND
Nvl(pol.CLM_INFO_FLAG,'N') = 'N' AND
NOT EXISTS
(SELECT 'Y' FROM po_requisition_lines_all prl1
WHERE prl1.requisition_header_id = poh.requisition_header_id
AND prl1.GROUP_LINE_ID = pol.REQUISITION_LINE_ID
OR prl1.CLM_BASE_LINE_NUM = pol.REQUISITION_LINE_ID)
order by pol.requisition_line_id asc;
select to_char(PO_WF_GROUP_S.NEXTVAL)
into x_group_id
from sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into x_seq_for_item_key
from sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into x_seq_for_item_key
from sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into x_seq_for_item_key
from sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
SELECT to_char(PO_WF_GROUP_S.NEXTVAL)
INTO x_group_id
FROM sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
INTO x_seq_for_item_key
FROM sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* If a process is not passed then the selector function defined in
* item type will be determine which process to run
*/
IF (ItemType is NOT NULL ) AND
(ItemKey is NOT NULL) AND
(req_line_id is NOT NULL ) then
wf_engine.CreateProcess(itemtype => itemtype,
itemkey => itemkey,
process => workflow_process );
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select pls.org_id,
pls.suggested_buyer_id,
pls.suggested_vendor_name,
pls.suggested_vendor_location,
pls.document_type_code,
pls.blanket_po_header_id,
pls.blanket_po_line_num,
pls.rfq_required_flag,
pls.on_rfq_flag,
pls.item_id,
pls.category_id,
pls.currency_code,
pls.rate_type,
pls.rate_date,
pls.rate,
pls.pcard_flag,
/* Supplier PCard FPH */
--decode(pls.pcard_flag, 'Y', phs.pcard_id,'S',-99999,'N', null),
--16027770
decode(pls.pcard_flag, 'Y', phs.pcard_id,'S',nvl((po_pcard_pkg.get_valid_pcard_id(-99999,pls.vendor_id,pls.vendor_site_id)),-99999),'N', null),
pls.destination_organization_id,
pls.catalog_type,
pls.vendor_id, /* Bug 2577940 */
pls.vendor_site_id,
pls.job_id, --
pls.labor_req_line_id , --
phs.federal_flag -- Bug 14314684
into x_org_id,
x_suggested_buyer_id,
x_suggested_vendor_name,
x_suggested_vendor_location,
x_source_doc_type_code,
x_source_doc_po_header_id,
x_source_doc_line_num,
x_rfq_required_flag,
x_on_rfq_flag,
x_item_id,
x_category_id,
x_currency_code,
x_rate_type,
x_rate_date,
x_rate,
x_pcard_flag,
x_pcard_id,
x_organization_id,
x_catalog_type,
x_suggested_vendor_id,
x_suggested_vendor_site_id,
l_job_id, --
l_labor_req_line_id, --
x_federal_flag -- Bug 14314684
from po_requisition_headers_all phs, --
po_requisition_lines pls
where pls.requisition_line_id = x_req_line_id
and phs.requisition_header_id = pls.requisition_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select global_agreement_flag
into x_ga_flag
from po_headers_all
where po_header_id = x_source_doc_po_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select 'Y',
vendor_name
into x_valid_vendor,
x_vendor
from po_suppliers_val_v
where vendor_id = x_suggested_vendor_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select 'Y',
vendor_id
into x_valid_vendor,
x_vendor_id
from po_suppliers_val_v
where vendor_name = x_suggested_vendor_name;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select 'Y',
vendor_site_code
into x_valid_vendor_site,
x_vendor_site
from po_vendor_sites_all
where vendor_id = x_vendor_id
and vendor_site_id = x_suggested_vendor_site_id
--
and purchasing_site_flag = 'Y'
and NVL(rfq_only_site_flag, 'N') = 'N'
and sysdate < NVL(inactive_date, sysdate + 1);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select 'Y',
vendor_site_id
into x_valid_vendor_site,
x_vendor_site_id
from po_supplier_sites_val_v
where vendor_id = x_vendor_id
and vendor_site_code = x_suggested_vendor_site;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
SELECT POH.po_header_id,
POH.global_agreement_flag,
POH.currency_code
FROM po_headers_all POH
WHERE POH.vendor_id = x_supplier_id
AND POH.currency_code = nvl(x_item_currency, l_base_currency)
AND POH.type_lookup_code = 'CONTRACT'
/* R12 GCPA
+ If Profile ALLOW_REFERENCING_CPA_UNDER_AMENDMENT is Y, then we can refer any Contract Which is approved Once
+ Else Contract should be in APPROVED state */
AND ( (NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
AND POH.Approved_Date Is Not Null
)
or ( POH.authorization_status = 'APPROVED' )
)
AND NVL(POH.cancel_flag, 'N') = 'N'
AND NVL(POH.frozen_flag, 'N') = 'N'
AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.start_date), SYSDATE - 1)
AND NVL(TRUNC(POH.end_date), SYSDATE + 1)
AND POH.global_agreement_flag = 'Y'
/* R12 GCPA
+ Vendor Site validation needs to be skipped if Enable All Sites on Contracts is Set to Y. */
AND EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = POH.po_header_id
AND PGOA.vendor_site_id = decode(Nvl(poh.Enable_All_Sites,'N'),'Y',PGOA.vendor_site_id, x_supplier_site_id)
AND PGOA.organization_id = l_current_org_id
AND PGOA.enabled_flag = 'Y')
ORDER BY POH.creation_date desc;
SELECT POH.po_header_id,
POH.global_agreement_flag,
POH.currency_code
FROM po_headers_all POH
WHERE POH.vendor_id = x_supplier_id
AND POH.type_lookup_code = 'CONTRACT'
/* R12 GCPA
+ If Profile ALLOW_REFERENCING_CPA_UNDER_AMENDMENT is Y, then we can refer any Contract Which is approved Once
+ Else Contract should be in APPROVED state
*/
AND ( (NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
AND POH.Approved_Date Is Not Null
)
or ( POH.authorization_status = 'APPROVED' )
)
AND NVL(POH.cancel_flag, 'N') = 'N'
AND NVL(POH.frozen_flag, 'N') = 'N'
AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.start_date), SYSDATE - 1)
AND NVL(TRUNC(POH.end_date), SYSDATE + 1)
AND POH.global_agreement_flag = 'Y'
/* R12 GCPA
+ Vendor Site validation needs to be skipped if Enable All Sites on Contracts is Set to Y. */
AND EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = POH.po_header_id
AND PGOA.vendor_site_id = decode(Nvl(poh.Enable_All_Sites,'N'),'Y',PGOA.vendor_site_id, x_supplier_site_id)
AND PGOA.organization_id = l_current_org_id
AND PGOA.enabled_flag = 'Y')
ORDER BY POH.creation_date desc;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select inventory_organization_id
into x_inv_org_id
from financials_system_params_all
where org_id = l_purchasing_org_id;
select buyer_id
into x_buyer_id
from mtl_system_items
where inventory_item_id = x_item_id
and organization_id = x_inv_org_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select agent_id
into x_agent_id
from po_agents
where category_id = x_category_id
and trunc(sysdate) between start_date_active
and nvl(end_date_active, sysdate+1);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select agent_id
into x_agent_id
from po_agents poa,
per_all_people_f ppf, --Bug 16249921. Changed per_people_f to per_all_people_f
financials_system_parameters fsp
where poa.agent_id = ppf.person_id
and ppf.business_group_id = fsp.business_group_id
and trunc(sysdate) between ppf.effective_start_date
and nvl(ppf.effective_end_date, sysdate+1)
and poa.category_id = x_category_id
and trunc(sysdate) between poa.start_date_active
and nvl(poa.end_date_active, sysdate+1);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select agent_id
into x_agent_id
from po_headers_all
where po_header_id = x_source_doc_po_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select agent_id
into x_agent_id
from po_headers_all
where po_header_id = x_contract_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
This query has been modified in such a way that it could select a release generation method even for the asl at commodity level Earlier the query selects release generation method only for item.
*/
select release_generation_method
into x_rel_gen_method
from (
select paa.release_generation_method
from po_asl_attributes_val_v paa
WHERE Paa.Item_Id = x_item_id
AND Paa.Vendor_Id = x_suggested_vendor_id
AND (Paa.Vendor_Site_Id IS NULL
OR ( x_suggested_vendor_site_id = Paa.Vendor_Site_Id
AND NOT EXISTS (SELECT 'select supplier line with null supplier site'
FROM po_Asl_Attributes_val_v Paa3
WHERE Paa.Item_Id = Paa3.Item_Id
AND Nvl(Paa.Category_Id,- 1) = Nvl(Paa3.Category_Id,- 1)
AND Paa.Vendor_Id = Paa3.Vendor_Id
AND Paa3.Vendor_Site_Id IS NULL
AND Paa3.UsIng_Organization_Id IN (- 1,
x_organization_id))))
AND Paa.UsIng_Organization_Id = (SELECT MAX(Paa2.UsIng_Organization_Id)
FROM po_Asl_Attributes_val_v Paa2
WHERE Paa.Item_Id = Paa2.Item_Id
AND Nvl(Paa.Category_Id,- 1) = Nvl(Paa2.Category_Id,- 1)
AND Paa.Vendor_Id = Paa2.Vendor_Id
AND Nvl(Paa.Vendor_Site_Id,- 1) = Nvl(Paa2.Vendor_Site_Id,- 1)
AND Paa2.UsIng_Organization_Id IN (- 1,x_organization_id))
union all
select paa.release_generation_method
from po_asl_attributes_val_v paa
WHERE (Paa.Item_Id IS NULL
AND x_category_id = Paa.Category_Id
AND NOT EXISTS (SELECT 'commodity level ASL should be used only if there is no item level ASL'
FROM po_Asl_Attributes_val_v Paa4
WHERE Paa4.Item_Id = x_item_id
AND Paa4.Vendor_Id = Paa.Vendor_Id
AND Nvl(Paa4.Vendor_Site_Id,- 1) = Nvl(Paa.Vendor_Site_Id,- 1)
AND Paa4.UsIng_Organization_Id IN (- 1,x_organization_id)))
AND Paa.Vendor_Id = x_suggested_vendor_id
AND (Paa.Vendor_Site_Id IS NULL
OR ( x_suggested_vendor_site_id = Paa.Vendor_Site_Id
AND NOT EXISTS (SELECT 'select supplier line with null supplier site'
FROM po_Asl_Attributes_val_v Paa3
WHERE Paa3.Item_Id IS NULL
AND Paa.Category_Id = Paa3.Category_Id
AND Paa.Vendor_Id = Paa3.Vendor_Id
AND Paa3.Vendor_Site_Id IS NULL
AND Paa3.UsIng_Organization_Id IN (- 1,
x_organization_id))))
AND Paa.UsIng_Organization_Id = (SELECT MAX(Paa2.UsIng_Organization_Id)
FROM po_Asl_Attributes_val_v Paa2
WHERE Paa2.Item_Id IS NULL
AND Paa.Category_Id = Paa2.Category_Id
AND Paa.Vendor_Id = Paa2.Vendor_Id
AND Nvl(Paa.Vendor_Site_Id,- 1) = Nvl(Paa2.Vendor_Site_Id,- 1)
AND Paa2.UsIng_Organization_Id IN (- 1,x_organization_id))
) WHERE ROWNUM =1;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* Procedure: insert_cand_req_lines_into_tbl
*
* Description: Inserts a req line into the temp table.
* This means its possible to try and autocreate this
* line.
*
**************************************************************************/
procedure insert_cand_req_lines_into_tbl (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 ) is
x_group_id number;
/* Insert the req line into the the temp table.
* The req lines in this table will then be picked up
* later to be autocreated.
*/
x_progress := '10:insert_cand_req_lines_into_tbl: inserting into temp table for ' ||
'req line = ' || to_char(x_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
INSERT INTO po_wf_candidate_req_lines_temp
(group_id,
requisition_header_id,
requisition_line_id,
suggested_buyer_id,
source_doc_type_code,
source_doc_id,
source_doc_line,
suggested_vendor_id,
suggested_vendor_site_id,
contract_id,
currency_code,
rate_type,
rate_date,
rate,
pcard_id,
process_code,
release_generation_method,
item_id,
job_id) --
VALUES (x_group_id,
x_req_header_id,
x_req_line_id,
x_suggested_buyer_id,
x_source_doc_type_code,
x_source_doc_id,
x_source_doc_line,
x_suggested_vendor_id,
x_suggested_vendor_site_id,
x_contract_id,
x_currency_code,
x_rate_type,
x_rate_date,
x_rate,
x_pcard_id,
'PENDING',
x_rel_gen_method,
x_item_id,
l_job_id); --
x_progress := '15: insert_cand_req_lines_into_tbl: IN EXCEPTION when inserting' ||
'into po_wf_candidate_req_lines_temp';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
x_progress:= '20: insert_cand_req_lines_into_tbl: ACTIVITY_PERFORMED';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
wf_core.context('po_autocreate_doc','insert_cand_req_lines_into_tbl',x_progress);
end insert_cand_req_lines_into_tbl;
* header and line records which it then inserts into
* the interface tables.
*
**************************************************************************/
procedure group_req_lines (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 ) is
c1_group_id number;
* We need the 'for update' since we are going to update the
* process_code.
*/
/* Bug # 1721991.
The 'for update' clause was added to update the row which was processed
in the Cursor c2 but this led to another problem in Oracle 8.1.6.3 or above
where you can't have a commit inside a 'for update' Cursor loop.
This let to the Runtime Error 'fetch out of sequence'
The commit was actually issued in the procedure insert_into_header_interface.
To solve this we removed the for update in the cursor and instead used rowid
to update the row processed by the Cursor.
*/
--
-- Added labor_req_line_id to the select statement
cursor c1 is /* x_group_id is a parameter */
select prlt.group_id,
prlt.requisition_header_id,
prlt.requisition_line_id,
prlt.suggested_buyer_id,
prlt.source_doc_type_code,
prlt.source_doc_id,
prlt.source_doc_line,
prlt.suggested_vendor_id,
prlt.suggested_vendor_site_id,
prlt.currency_code,
prlt.rate_type,
prlt.rate_date,
prlt.rate,
prlt.process_code,
prlt.release_generation_method,
prlt.item_id,
prlt.pcard_id,
prlt.contract_id,
hrl.location_code,
prl.destination_organization_id,
prl.destination_type_code,
prl.labor_req_line_id
--
,prl.line_type_id,
prl.purchase_basis
--
from po_wf_candidate_req_lines_temp prlt,
po_requisition_lines prl,
hr_locations_all hrl
where prlt.process_code = 'PENDING'
and prlt.group_id = x_group_id
and prlt.requisition_line_id = prl.requisition_line_id
and prl.deliver_to_location_id = hrl.location_id(+) -- bug 2709046
and prl.line_location_id IS NULL-- --bug10064616
for update; --
select prlt.rowid, -- Bug# 1721991 , Added rowid to update row processed
prlt.group_id,
prlt.requisition_header_id,
prlt.requisition_line_id,
prlt.suggested_buyer_id,
prlt.source_doc_type_code,
prlt.source_doc_id,
prlt.source_doc_line,
prlt.suggested_vendor_id,
prlt.suggested_vendor_site_id,
prlt.currency_code,
prlt.rate_type,
prlt.rate_date,
prlt.rate,
prlt.process_code,
prlt.release_generation_method,
prlt.item_id,
prlt.pcard_id,
prlt.contract_id,
prl.suggested_vendor_contact,
prl.vendor_contact_id,
hrl.location_code,
prl.destination_organization_id,
prl.destination_type_code,
prl.labor_req_line_id
--
,prl.line_type_id,
prl.purchase_basis
--
from po_wf_candidate_req_lines_temp prlt,
po_requisition_lines prl,
hr_locations_all hrl
where prlt.process_code = 'PENDING'
and prlt.group_id = x_group_id
and prlt.requisition_line_id = prl.requisition_line_id
and prl.deliver_to_location_id = hrl.location_id(+) -- bug 2709046
and prl.line_location_id IS NULL-- --bug10064616
--Bug# 1721991, for update;
for update; --
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select global_agreement_flag
into c1_ga_flag
from po_headers_all
where po_header_id = c1_source_doc_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select global_agreement_flag
into c2_ga_flag
from po_headers_all
where po_header_id = c2_source_doc_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
if (c1_req_line_id = c2_req_line_id) /* Always insert if c1 and c2 is the same line */
OR
( /* (x_grouping_allowed = 'Y') AND Bug 2974129 */
(x_group_one_time_address = 'Y' OR
(x_group_one_time_address = 'N' AND
/* Bug 16518070
Added NVL for deliver to location code as theyre failing for drop ship so flow
Because deliver to location is not present in hr locations for drop ship SO instead in hz locations.
So above cursors will retrieve NULL values for these variables*/
nvl(c1_deliver_to_location_code,-99) <> nvl(fnd_profile.value('POR_ONE_TIME_LOCATION'),-99) AND --bug 4449781 : added nvl
nvl(c2_deliver_to_location_code,-99) <> nvl(fnd_profile.value('POR_ONE_TIME_LOCATION'),-99))) AND
(c1_suggested_buyer_id = c2_suggested_buyer_id) AND
(c1_style_id = c2_style_id) AND --
(c1_suggested_vendor_id = c2_suggested_vendor_id) AND
(c1_suggested_vendor_site_id = c2_suggested_vendor_site_id) AND
(nvl(c1_source_doc_type_code ,'QUOTATION') =
nvl(c2_source_doc_type_code,'QUOTATION')) AND
(nvl(c1_ga_flag,'N') = nvl(c2_ga_flag,'N')) AND -- FPI GA
(nvl(c1_contract_id,-1) = nvl(c2_contract_id,-1)) AND
(nvl(c1_currency_code,'ok') = nvl(c2_currency_code, 'ok')) AND
(nvl(c1_rate_type, 'ok') = nvl(c2_rate_type, 'ok')) AND
((c1_rate is NULL AND c2_rate is NULL) --
OR
(nvl(trunc(c1_rate_date), trunc(sysdate)) = nvl(trunc(c2_rate_date), trunc(sysdate)))) AND --bug9104813
(nvl(c1_rate,-1) = nvl(c2_rate, -1)) AND
(nvl(c1_pcard_id,-1) = nvl(c2_pcard_id,-1)) AND
((nvl(c1_source_doc_id,-1) = nvl(c2_source_doc_id,-1))
OR
(nvl(c1_source_doc_type_code ,'QUOTATION') = 'QUOTATION')
OR
((nvl(c1_source_doc_type_code,'QUOTATION') = 'BLANKET') AND (nvl(c1_ga_flag,'N') = 'Y'))) -- FPI GA AND
)
--
OR
(nvl(c1_req_line_id, -1) = nvl(c2_labor_req_line_id, -1))
OR
(nvl(c1_labor_req_line_id, -1) = nvl(c2_req_line_id, -1))
--
THEN
x_progress := '20: group_req_lines: c1 and c2 match ';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
/* Update the process code of the current line in the temp table so
* it doesn't get picked up again by the cursor for processing.
*/
update po_wf_candidate_req_lines_temp
set process_code = 'PROCESSED'
where rowid=c2_rowid;
x_progress:= '30:group_req_lines: Updated process_code ';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
if(po_autocreate_doc.insert_into_headers_interface
(itemtype,
itemkey,
c2_group_id,
c2_suggested_vendor_id,
c2_suggested_vendor_site_id,
c2_suggested_buyer_id,
c2_source_doc_type_code,
c2_source_doc_id,
c2_currency_code,
c2_rate_type,
c2_rate_date,
c2_rate,
l_pcard_id, --
c2_style_id, --
x_interface_header_id) = FALSE) then
exit; --bug 3401653: po creation failed, skip out of inner loop
po_autocreate_doc.insert_into_lines_interface (itemtype,
itemkey,
x_interface_header_id,
c2_req_line_id,
c2_source_doc_line,
c2_source_doc_type_code,
c2_contract_id,
c2_source_doc_id, -- GA FPI
c2_cons_from_supp_flag); -- Consigned FPI
SELECT vendor_contact_id
INTO x_source_contact_id
FROM po_headers_all
WHERE po_header_id=c2_contract_id;
SELECT vendor_contact_id
INTO x_source_contact_id
FROM po_headers_all -- To take care of GAs in Diff Operating unit
WHERE po_header_id=c2_source_doc_id;
x_progress := '40: group_req_lines: inserted header'||
' and line for req line = ' || to_char(c2_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
Added code to update vendor_contact_id when po_headers is inserted for first time. */
if (x_carry_contact_to_po_flag = 'TRUE' and
valid_contact(c2_suggested_vendor_site_id, x_suggested_vendor_contact_id)) then
begin
update po_headers_interface
set vendor_contact_id = x_suggested_vendor_contact_id
where interface_header_id = x_interface_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* as a previous one, so only insert a new line into the
* po_lines_interface table.
*/
po_autocreate_doc.insert_into_lines_interface (itemtype,
itemkey,
x_interface_header_id,
c2_req_line_id,
c2_source_doc_line,
c2_source_doc_type_code,
c2_contract_id,
c2_source_doc_id, -- GA FPI
c2_cons_from_supp_flag); -- Consigned FPI
x_progress := '50: group_req_lines: inserted just line for '||
'req line = ' || to_char(c2_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
/* Commented this code as we are updating vendor_contact_id when header is inserted first time.
if (x_carry_contact_to_po_flag = 'TRUE' and
valid_contact(c2_suggested_vendor_site_id, x_suggested_vendor_contact_id)) then
begin
x_progress := '55: group_req_lines: updating header with vendor contact :'||x_interface_header_id;
update po_headers_interface
set vendor_contact_id = x_suggested_vendor_contact_id
where interface_header_id = x_interface_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
/* Bug 3586181 Update the contact id if the either Contract or GA has
got a valid contact */
if (c2_found='Y') then
Begin
if ( x_source_contact_id is not null) then
update po_headers_interface
set vendor_contact_id = x_source_contact_id
where interface_header_id = x_interface_header_id;
update po_headers_interface
set vendor_contact_id = NULL
where interface_header_id = x_interface_header_id;
update po_headers_interface
set vendor_contact_id = x_prev_sug_vendor_contact_id
where interface_header_id = x_interface_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* Procedure: insert_into_headers_interface
*
* Description: Inserts a row into the po_headers_interface
* Returns false if creating PO header fails, and true otherwise (bug 3401653)
*
**************************************************************************/
function insert_into_headers_interface (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
x_group_id IN NUMBER,
x_suggested_vendor_id IN NUMBER,
x_suggested_vendor_site_id IN NUMBER,
x_suggested_buyer_id IN NUMBER,
x_source_doc_type_code IN VARCHAR2,
x_source_doc_id IN NUMBER,
x_currency_code IN VARCHAR2,
x_rate_type IN VARCHAR2,
x_rate_date IN DATE,
x_rate IN NUMBER,
x_pcard_id IN NUMBER,
p_style_id IN NUMBER, --
x_interface_header_id IN OUT NOCOPY NUMBER)
RETURN boolean is --bug 3401653
x_batch_id number;
x_last_update_date date := sysdate;
x_last_updated_by number;
x_progress := '10:insert_into_headers_interface:' ||
'just before set_purchasing_org_id';
x_progress:= '20: insert_into_headers_interface: org_id = ' ||
to_char(x_org_id) || ' purchasing_org_id = ' ||
to_char(l_purchasing_org_id);
x_last_updated_by := to_number(FND_PROFILE.VALUE('user_id'));
select po_headers_interface_s.nextval
into x_interface_header_id
from sys.dual;
select global_agreement_flag, currency_code
into x_ga_flag, l_source_doc_currency_code
from po_headers_all
where po_header_id = x_source_doc_id;
x_progress := '30: insert_into_headers_interface: Just about to get doc' ||
'num from po_unique_identifier_control';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
done as the Commit after the UPDATE of the PO_UNIQUE_IDENTIFIER_CONTROL
table was also affecting the Workflow transactions.
The function get_document_num is an autonomous transaction.
*/
--
--Get document num in purchasing org
x_document_num := get_document_num(l_purchasing_org_id);
x_progress := '40: insert_into_headers_interface: Got doc' ||
'num from po_unique_identifier_control';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select 'PO EXISTS'
into x_found
from po_headers_all
where segment1 = x_document_num
and org_id = l_purchasing_org_id --
and type_lookup_code IN ('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT');
x_progress := '45: insert_into_headers_interface: document_num is a ' ||
'duplicate - not going to insert into po_headers_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select sob.currency_code, fsp.set_of_books_id
into l_pou_currency_code, l_pou_sob_id
from financials_system_params_all fsp,
gl_sets_of_books sob
where fsp.set_of_books_id = sob.set_of_books_id
and fsp.org_id = l_purchasing_org_id; --
select default_rate_type
into l_pou_default_rate_type
from po_system_parameters_all psp --
where psp.org_id = l_purchasing_org_id; -- removed nvl --
select sob.currency_code
into l_rou_currency_code
from financials_system_params_all fsp,
gl_sets_of_books sob
where fsp.set_of_books_id = sob.set_of_books_id
and fsp.org_id = x_org_id;
x_progress := '47: insert_into_headers_interface: Purchasing Operating unit' ||
' has no default rate type, cannot create PO';
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
x_progress := '47: insert_into_headers_interface: Purchasing Operating unit' ||
' has no default rate type, cannot create PO';
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
x_progress := '48: insert_into_headers_interface: No rate defined to' ||
' convert from Requesting OU currency to PO currency, cannot create PO';
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
select segment1
into x_document_num
from po_headers
where po_header_id = x_source_doc_id;
select nvl(max(release_num),0)+1
into x_release_num
from po_releases_all por, --
po_headers poh
where poh.po_header_id = x_source_doc_id
and poh.po_header_id = por.po_header_id;
* this could conflict with a release_num that we have inserted into
* the po_headers_interface table previously that has yet to converted
* into a release eg. when we have two req lines that will be created
* onto two diff. releases.
*/
-- Bug 722352, lpo, 08/26/98
-- Commented out the release_num filters for the next 2 queries.
select count (*)
into x_no_releases
from po_headers_interface phi
where phi.document_num = x_document_num;
select max(release_num)+1
into x_release_num1
from po_headers_interface phi
where phi.document_num = x_document_num;
/* Insert into po_headers_inteface */
x_progress := '50: insert_into_headers_interface: Just about to insert into ' ||
'po_headers_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
insert into po_headers_interface
(wf_group_id,
interface_header_id,
interface_source_code,
batch_id,
process_code,
action,
document_type_code,
document_subtype,
document_num,
group_code,
vendor_id,
vendor_site_id,
release_num,
release_date,
agent_id,
currency_code,
rate_type_code,
rate_date,
rate,
creation_date,
created_by,
last_update_date,
last_updated_by,
pcard_id,
style_id --
)
values
(x_group_id,
x_interface_header_id,
'PO',
x_batch_id,
'NEW',
'NEW',
'PO', -- PO for both po's and releases
x_doc_type_to_create,
x_document_num,
x_group_code, /* Bug 2974129 */
x_suggested_vendor_id,
x_suggested_vendor_site_id,
x_release_num,
x_release_date,
x_suggested_buyer_id,
x_currency_code_doc,
l_interface_rate_type, --bug 3401653
l_interface_rate_date, --bug 3401653
l_interface_rate, --bug 3401653
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_pcard_id,
p_style_id --
);
x_progress := '55: insert_into_headers_interface: IN EXCEPTION when '||
'inserting into po_headers_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
x_progress := '60: insert_into_headers_interface: Inserted into ' ||
'po_headers_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
wf_core.context('po_autoinsert_into_headers_interface','create_doc',x_progress);
end insert_into_headers_interface;
* Procedure: insert_into_lines_interface
*
* Description: Inserts a row in the po_lines_interface table
*
*
**************************************************************************/
procedure insert_into_lines_interface (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
x_interface_header_id IN NUMBER,
x_req_line_id IN NUMBER,
x_source_doc_line IN NUMBER,
x_source_doc_type_code IN VARCHAR2,
x_contract_id IN NUMBER,
x_source_doc_id IN NUMBER, -- GA FPI
x_cons_from_supp_flag IN VARCHAR2) is -- Consigned FPI
-- : removed variable x_contract_num
x_interface_line_id number;
x_last_update_date date := sysdate;
x_last_updated_by number;
x_last_updated_by := to_number(FND_PROFILE.VALUE('user_id'));
select global_agreement_flag
into x_ga_flag
from po_headers_all
where po_header_id = x_source_doc_id;
select po_lines_interface_s.nextval
into x_interface_line_id
from sys.dual;
Select po_line_id
into x_source_line_id
From po_lines_all
Where po_header_id = x_source_doc_id
And line_num = x_source_doc_line;
/* Insert into po_lines */
x_progress := '10: insert_into_lines_interface: Just about to insert into ' ||
'po_lines_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
insert into po_lines_interface
(interface_header_id,
interface_line_id,
action,
line_num,
shipment_num,
requisition_line_id,
contract_id, --
from_header_id, -- GA FPI
from_line_id, -- GA FPI
consigned_flag, -- Bug 2798503
creation_date,
created_by,
last_update_date,
last_updated_by)
values
(x_interface_header_id,
x_interface_line_id,
x_action_type_code_line,
x_line_num,
null,
x_req_line_id,
x_contract_id, --
x_source_doc_id , -- Consigned FPI
x_source_line_id, -- Consigned FPI
x_cons_from_supp_flag, -- Bug 2798503
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by);
x_progress:= '15: insert_into_lines_interface: IN EXCEPTION when' ||
'inserting into po_lines_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
x_progress := '20: insert_into_lines_interface: Inserted into ' ||
'po_lines_interface';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
wf_core.context('po_autoinsert_into_lines_interface','insert_into_lines_interface',
x_progress);
end insert_into_lines_interface;
select interface_header_id,
document_subtype,
agent_id,
vendor_site_id --
from po_headers_interface
where wf_group_id = x_group_id
order by interface_header_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype => itemtype,
itemkey => itemkey,
x_progress => x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into x_seq_for_item_key
from sys.dual;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* If a process is not passed then the selector function defined in
* item type will be determine which process to run
*/
IF (ItemType is NOT NULL ) AND
(ItemKey is NOT NULL) AND
(interface_header_id is NOT NULL ) then
wf_engine.CreateProcess(itemtype => itemtype,
itemkey => itemkey,
process => workflow_process );
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* - insert into the main tables from the
* the interface tables.
*
* x_document_id is populated with po_header_id for pos
* and po_release_id for releases
*/
x_progress:= '10: create_doc: Kicking off backend with' ||
'interface_header_id = '|| to_char(x_interface_header_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select nvl(group_shipments_flag, 'Y')
into x_group_shipments
from po_system_parameters;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select displayed_field
into x_doc_type_created_disp
from po_lookup_codes
where lookup_type = 'NOTIFICATION TYPE'
and lookup_code = x_doc_type_to_create;
select segment1,
style_id
into x_segment1,
l_style_id
from po_headers_all
where po_header_id = x_autocreated_doc_id;
p_mode => 'update');
select poh.segment1,
por.release_num
into x_segment1,
x_release_num
from po_headers_all poh, --
po_releases por
where por.po_release_id = x_autocreated_doc_id
and por.po_header_id = poh.po_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,'Tax Errored ');
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* Deleted the code which gets teh preparer_id if it is null since
* x_preparer_id will be derived in get_transmission_defaults if
* this is null. Also deleted the code which gets the default supplier
* communication method from po_vendor_site and now call
* PO_VENDOR_SITES_SV.Get_Transmission_Defaults which does this now.
* Also deleted the code which defaults item_type, item_key from
* po_document_types as this is done now in start_wf_process.
*/
/* Bug 1845764 :
Check if the supplier site on the PO/Release has email as the default notification method.
if so set the email flag and address from the site */
-- included bug fix 2342323. Modified changes are to retrieve segment1
-- from po_headers into x_doc_num
-- for release, PO or PA
-- Bug 2567900 Included fax number in the select
/* bug 4638656 - start */
/* We donot consider transaction subtype POCO, since a
document cannot be in requires reapproval when
launching approval from create doc */
--Bug4956479 Included agent_id/preparer_id in select/into clause
--for doctype of both PO/RELEASE
l_transaction_subtype := 'POO';
select pvs.tp_header_id,
nvl(etd.edi_flag,'N'),
ph.agent_id
into l_tp_header_id,
l_edi_flag,
x_preparer_id
from ece_tp_details etd,
po_vendor_sites pvs,
po_vendors pv,
po_headers ph
where pv.vendor_id = pvs.vendor_id
and pvs.tp_header_id = etd.tp_header_id
and etd.document_id = l_transaction_subtype
and ph.vendor_id = pv.vendor_id
and ph.vendor_site_id = pvs.vendor_site_id
and ph.po_header_id = x_doc_id
and ph.type_lookup_code= x_doc_subtype
and etd.document_type = ph.type_lookup_code;
select pvs.tp_header_id,
nvl(etd.edi_flag,'N'),
pr.agent_id
into l_tp_header_id,
l_edi_flag,
x_preparer_id
from ece_tp_details etd,
po_vendor_sites pvs,
po_vendors pv,
po_headers ph ,
po_releases pr
where pv.vendor_id = pvs.vendor_id
and pvs.tp_header_id = etd.tp_header_id
and etd.document_id = l_transaction_subtype
and ph.vendor_id = pv.vendor_id
and ph.vendor_site_id = pvs.vendor_site_id
and pr.po_header_id = ph.po_header_id
and pr.po_release_id = x_doc_id
and etd.document_type = 'RELEASE';
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
/* Delete all rows belonging to this group_id */
delete from po_wf_candidate_req_lines_temp
where group_id = x_group_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
SELECT emergency_po_num
INTO x_emergency_po_num
FROM po_requisition_headers
WHERE requisition_header_id=x_req_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* insert into the interface tables
* Remark: This is for processing emergency requisitions
* where only one PO number is reserved for each
* requisition
*
**************************************************************************/
procedure put_on_one_po(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 ) is
c_rowid rowid;
* We need the 'for update' since we are going to update the
* process_code.
*/
/* Bug # 1721991.
The 'for update' clause was added to update the row which was processed
in the Cursor c1 but this led to another problem in Oracle 8.1.6.3 or above
where you can't have a commit inside a 'for update' Cursor loop.
This let to the Runtime Error 'fetch out of sequence'
The commit was actually issued in the procedure insert_into_header_interface.
To solve this we removed the for update in the cursor and instead used rowid
to update the row processed by the Cursor.
*/
cursor c1 is /* x_group_id is a parameter */
select prlt.rowid, -- Bug# 1721991, Added rowid to update row
prlt.group_id,
prlt.requisition_header_id,
prlt.requisition_line_id,
prlt.suggested_buyer_id,
prlt.source_doc_type_code,
prlt.source_doc_id,
prlt.source_doc_line,
prlt.suggested_vendor_id,
prlt.suggested_vendor_site_id,
prlt.currency_code,
prlt.rate_type,
prlt.rate_date,
prlt.rate,
prlt.process_code,
prlt.release_generation_method,
prlt.item_id,
prlt.pcard_id,
prlt.contract_id,
prl.suggested_vendor_contact,
prl.vendor_contact_id,
prl.destination_organization_id,
prl.destination_type_code
from po_wf_candidate_req_lines_temp prlt,
po_requisition_lines prl
where prlt.process_code = 'PENDING'
and prlt.group_id = x_group_id
and prlt.requisition_header_id = prl.requisition_header_id
and prlt.requisition_line_id = prl.requisition_line_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
update po_wf_candidate_req_lines_temp
set process_code = 'PROCESSED'
where rowid=c_rowid;
select NVL(global_agreement_flag,'N')
into x_ga_flag
from po_headers_all
where po_header_id = c_source_doc_id;
if(po_autocreate_doc.insert_into_headers_interface
(itemtype,
itemkey,
c_group_id,
c_suggested_vendor_id,
c_suggested_vendor_site_id,
c_suggested_buyer_id,
--Bug 13496442/14305183 , replace null values only if global BPA
c_source_doc_type_code,
c_source_doc_id,
--End bug 13496442
c_currency_code,
c_rate_type,
c_rate_date,
c_rate,
c_pcard_id,
l_style_id, --
x_interface_header_id) = FALSE) then
return; --bug 3401653: po creation failed
po_autocreate_doc.insert_into_lines_interface (itemtype,
itemkey,
x_interface_header_id,
c_req_line_id,
--Bug 13496442, replace null values
c_source_doc_line,
c_source_doc_type_code,
null,
c_source_doc_id,
c_cons_from_supp_flag); -- Consigned FPI
x_progress := '20: put_on_one_po: inserted header'||
' and line for req line = ' ||
to_char(c_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
* as a previous one, so only insert a new line into the
* po_lines_interface table.
*/
po_autocreate_doc.insert_into_lines_interface (itemtype,
itemkey,
x_interface_header_id,
c_req_line_id,
--Bug 13496442, replace null values
c_source_doc_line,
c_source_doc_type_code,
null,
c_source_doc_id,
--End bug 13496442
c_cons_from_supp_flag); -- Consigned FPI
x_progress := '30: put_on_one_po: inserted just line for '||
'req line = ' || to_char(c_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
update po_headers_interface
set vendor_contact_id = x_suggested_vendor_contact_id
where interface_header_id = x_interface_header_id;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
l_update_req_url varchar2(1000);
select st.DISPLAYED_FIELD,
ty.DISPLAYED_FIELD,
hd.SEGMENT1,
hd.ORG_ID,
hd.PREPARER_ID,
hd.TYPE_LOOKUP_CODE
into l_doc_subtype,
l_doc_type,
l_req_num,
l_org_id,
l_preparer_id,
doc_subtype
from po_requisition_headers hd,
po_lookup_codes ty,
po_lookup_codes st
where hd.requisition_header_id = p_req_header_id
and ty.lookup_type = 'DOCUMENT TYPE'
and ty.lookup_code = 'REQUISITION'
and st.lookup_type = 'REQUISITION TYPE'
and st.lookup_code = hd.TYPE_LOOKUP_CODE;
select ty.DOCUMENT_TYPE_CODE,
ty.DOCUMENT_SUBTYPE,
ty.type_name,
nvl2(hd.par_draft_id,
pod.modification_number,
hd.segment1) SEGMENT1,
hd.ORG_ID,
hd.PREPARER_ID,
hd.TYPE_LOOKUP_CODE,
ty.wf_approval_itemtype
into l_doc_type_code,
l_doc_subtype,
l_doc_type,
l_req_num,
l_org_id,
l_preparer_id,
doc_subtype,
ItemType
from po_requisition_headers hd,
po_document_types ty ,
po_drafts pod
where hd.requisition_header_id = p_req_header_id
and ty.document_type_code = 'REQUISITION'
and ty.document_subtype = hd.TYPE_LOOKUP_CODE
AND hd.par_draft_id = pod.draft_id(+);
select wf_approval_itemtype
into ItemType
from PO_DOCUMENT_TYPES
where DOCUMENT_TYPE_CODE = 'REQUISITION'
and DOCUMENT_SUBTYPE = doc_subtype;
l_update_req_url := por_util_pkg.jumpIntoFunction(
p_application_id=> 178,
p_function_code => 'POR_UPDATE_REQ',
p_parameter1 => to_char(p_req_header_id),
p_parameter11 => to_char(l_org_id) );
/*select pr.FULL_NAME
into l_agent_name
from per_people_f pr
where pr.person_id = p_agent_id
and trunc(sysdate) between pr.effective_start_date
and pr.effective_end_date;*/
select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
aname => 'REQ_UPDATE_URL' ,
avalue => l_update_req_url);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select POH.po_header_id, poh.currency_code
into x_contract_id_valid, x_contract_currency_code
from po_headers_all POH -- : Use ALL table
where
POH.po_header_id = x_source_doc_po_header_id
and POH.type_lookup_code = 'CONTRACT'
and nvl(POH.cancel_flag,'N') = 'N'
and TRUNC(sysdate) between nvl(TRUNC(start_date), sysdate - 1)
and nvl(TRUNC(end_date), sysdate + 1)
and POH.authorization_status = 'APPROVED'
and nvl(POH.closed_code,'OPEN') = 'OPEN'
AND NVL(POH.frozen_flag, 'N') = 'N'
AND (NVL(POH.global_agreement_flag, 'N') = 'N'
OR EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA,
po_system_parameters PSP
WHERE PGOA.po_header_id = POH.po_header_id
AND PGOA.organization_id = PSP.org_id
AND PGOA.vendor_site_id = decode(Nvl(poh.Enable_All_Sites,'N'),'Y',PGOA.vendor_site_id, l_vendor_site_id) -- Bug#14157987
AND PGOA.enabled_flag = 'Y'));
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
select count(*) into x_count
from po_vendor_contacts
where vendor_site_id = p_vendor_site_id
and vendor_contact_id = p_vendor_contact_id
and nvl(inactive_date, sysdate+1) > sysdate;
select max(vendor_contact_id)
into x_contact_id
from po_supplier_contacts_val_v
where vendor_site_id = p_vendor_site_id
and contact = p_contact_name;
select org_id
into x_owning_org_id
from po_headers_all
where po_header_id = x_source_doc_po_header_id;
po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
select 'Y'
into x_ref_is_valid
from po_headers_all poh
where poh.po_header_id = p_ga_po_header_id and
nvl(poh.cancel_flag, 'N') = 'N' and
nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT 'Y'
INTO l_result
FROM po_buyers_v
WHERE employee_id = p_agent_id;
SELECT 'Y'
INTO l_return_status
FROM po_headers_all poh,
per_all_people_f ppf, --Bug 16249921. Changed per_people_f ppf to per_all_people_f
financials_system_parameters fsp
WHERE poh.agent_id = ppf.person_id
AND ppf.business_group_id = fsp.business_group_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
AND NVL (ppf.effective_end_date, SYSDATE + 1)
AND poh.po_header_id = x_source_doc_po_header_id;
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
SELECT 'Y'
INTO l_return_status
FROM po_headers_all poh,
per_all_people_f ppf,----Bug16249921 Changed per_people_f to per_all_people_f
--as per_people_f is security profile restricted.
financials_system_parameters fsp
WHERE poh.agent_id = ppf.person_id
AND ppf.business_group_id = fsp.business_group_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
AND NVL(ppf.effective_end_date, SYSDATE + 1)
AND poh.po_header_id = x_contract_id;
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
SELECT prl.destination_organization_id
INTO l_dest_org_id
FROM po_requisition_lines_all prl
WHERE requisition_line_id = l_req_line_id;
po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
SELECT org_id
INTO l_purchasing_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = p_suggested_vendor_site_id;
po_wf_debug_pkg.insert_debug(itemtype,
itemkey,
l_progress);
po_wf_debug_pkg.insert_debug(itemtype,
itemkey,
l_progress);
DELETE FROM po_wf_candidate_req_lines_temp
WHERE requisition_line_id = (
SELECT requisition_line_id
FROM po_requisition_lines
WHERE labor_req_line_id = l_req_line_id);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
SELECT clm_flag
INTO l_clm_flag
FROM po_doc_style_headers
WHERE style_id = (SELECT style_id
FROM po_headers_all
WHERE po_header_id = l_po_header_id );