[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT phi.interface_header_id interface_header_id,
phi.interface_source_code interface_source_code,
phi.batch_id batch_id,
phi.process_code process_code,
phi.action action,
phi.document_subtype document_subtype,
phi.document_num document_num,
phi.po_header_id po_header_id,
phi.release_num release_num,
phi.agent_id agent_id,
phi.vendor_id vendor_id,
phi.vendor_site_id vendor_site_id,
phi.vendor_contact_id vendor_contact_id,
phi.ship_to_location_id ship_to_location_id,
phi.bill_to_location_id bill_to_location_id,
phi.terms_id terms_id,
phi.freight_carrier ship_via_lookup_code,
phi.fob fob_lookup_code,
phi.pay_on_code pay_on_code,
phi.freight_terms freight_terms_lookup_code,
phi.creation_date creation_date,
phi.created_by created_by,
phi.last_update_date last_update_date,
phi.last_updated_by last_updated_by,
phi.last_update_login last_update_login,
phi.revision_num revision_num,
phi.print_count print_count,
phi.closed_code h_closed_code,
phi.frozen_flag frozen_flag,
phi.firm_flag h_firm_status_lookup_code,
pli.firm_flag l_firm_status_lookup_code,
phi.confirming_order_flag confirming_order_flag,
phi.acceptance_required_flag acceptance_required_flag,
phi.currency_code h_currency_code,
phi.rate_type_code h_rate_type,
phi.rate_date h_rate_date,
phi.rate h_rate,
phi.min_release_amount h_min_release_amount,
pli.min_release_amount l_min_release_amount,
phi.release_date release_date,
phi.document_subtype quote_type_lookup_code,
phi.vendor_list_header_id vendor_list_header_id,
--DPCARD{
phi.pcard_id,
--DPCARD}
pli.interface_line_id interface_line_id,
pli.line_num line_num,
pli.shipment_num shipment_num,
pli.line_location_id line_location_id,
pli.requisition_line_id requisition_line_id,
pli.line_type_id line_type_id,
pli.item_id item_id,
pli.category_id category_id,
pli.item_revision item_revision,
pli.item_description item_description,
--
pli.base_unit_price base_unit_price,
--
pli.unit_price unit_price,
pli.price_type price_type_lookup_code,
pli.unit_of_measure unit_meas_lookup_code,
pli.un_number_id un_number_id,
pli.hazard_class_id hazard_class_id,
-- pli.contract_num contract_num, --
pli.contract_id contract_id, --
pli.vendor_product_num vendor_product_num,
pli.type_1099 type_1099,
pli.need_by_date need_by_date,
pli.quantity quantity,
pli.amount, --
pli.negotiated_by_preparer_flag negotiated_by_preparer_flag,
pli.closed_code l_closed_code,
pli.transaction_reason_code transaction_reason_code,
pli.from_header_id from_header_id,
pli.from_line_id from_line_id,
pli.from_line_location_id from_line_location_id,--
pli.receipt_required_flag receipt_required_flag,
--DWR4{
pli.tax_status_indicator,
--DWR4}
pli.tax_code_id,
pli.note_to_vendor,
--togeorge 09/27/2000
--Bug#1433282
--added note to receiver and oke columns
pli.note_to_receiver,
pli.oke_contract_header_id,
pli.oke_contract_version_id,
pdi.oke_contract_line_id,
pdi.oke_contract_deliverable_id,
-- adding process related columns
-- start of 1548597
pli.secondary_unit_of_measure,
pli.secondary_quantity,
pli.preferred_grade,
-- end of 1548597
--
phi.amount_agreed, --Bug# 2288408
phi.effective_date, --Bug# 2288408
phi.expiration_date, --Bug# 2288408
pli.committed_amount, --Bug# 2288408
pli.promised_date promised_date,
pli.auction_header_id,
pli.auction_line_number,
pli.auction_display_number,
pli.bid_number,
pli.bid_line_number,
pli.orig_from_req_flag,
pdi.charge_account_id,
pdi.accrual_account_id,
pdi.variance_account_id,
pdi.encumbered_flag,
pdi.budget_account_id,
--
--
phi.amount_limit,
phi.global_agreement_flag,
pli.ship_to_location_id line_ship_to_loc_id,
pli.ship_to_organization_id line_ship_to_org_id,
pli.price_discount,
pli.effective_date line_effective_date,
pli.expiration_date line_expiration_date,
pli.shipment_type,
--Bug #2715037 :Need to capture this coming from Sourcing
pli.price_break_lookup_code,
--
pdi.destination_type_code destination_type_code,
pdi.deliver_to_location_id deliver_to_location_id,
pdi.destination_organization_id destination_organization_id,
pli.vmi_flag, -- VMI FPH
pli.drop_ship_flag, --
--
phi.consume_req_demand_flag,
pli.consigned_flag, -- CONSIGNED FPI
phi.shipping_control, --
pli.supplier_ref_number, --
pli.job_id, --
pli.contractor_first_name, --
pli.contractor_last_name, --
pli.transaction_flow_header_id, --
phi.org_id --
--
, phi.style_id style_id
, pli.retainage_rate retainage_rate
, pli.max_retainage_amount max_retainage_amount
, pli.progress_payment_rate progress_payment_rate
, pli.recoupment_rate recoupment_rate
, pli.advance_amount advance_amount
, NVL(pli.line_loc_populated_flag, 'N') poll_interface_pop_flag
, NVL2(pli.advance_amount, 'Y', 'N') has_advance_flag
--
--
, phi.created_language created_language
, phi.cpa_reference cpa_reference
, pli.ip_category_id ip_category_id
, pli.supplier_part_auxid supplier_part_auxid
, pli.catalog_name catalog_name
--
FROM po_headers_interface phi,
po_lines_interface pli,
po_distributions_interface pdi
WHERE phi.interface_header_id = pli.interface_header_id
AND pli.interface_line_id = pdi.interface_line_id
AND phi.interface_header_id = x_interface_header_id
AND pdi.interface_distribution_id =
(SELECT min(pdi2.interface_distribution_id)
FROM po_distributions_interface pdi2
WHERE pdi2.interface_line_id = pli.interface_line_id)
ORDER BY pli.line_num,
--
nvl(pli.shipment_num,0),
pli.unit_price;
PROCEDURE update_shipment(x_interface_header_id IN NUMBER,
x_po_shipment_num IN NUMBER,
x_po_line_num IN NUMBER,
x_requisition_line_id IN NUMBER,
x_po_line_id IN NUMBER,
x_document_num IN VARCHAR2,
x_release_num IN NUMBER,
x_create_new_line IN VARCHAR2, -- GA FPI
x_row_id IN VARCHAR2 default null);
PROCEDURE update_award_distributions(
p_table_type IN VARCHAR2 DEFAULT 'INTERFACE'
, p_po_line_id IN NUMBER DEFAULT NULL
); --
p_message => 'Before select min interface header for batch_id: '||p_batch_id);
SELECT min(interface_header_id)
INTO x_interface_header_id
FROM po_headers_interface
WHERE batch_id = p_batch_id; --
SELECT document_type_code,document_subtype,nvl(interface_source_code,'NOCODE')
INTO g_document_type,g_document_subtype,g_interface_source_code
FROM po_headers_interface
WHERE interface_header_id = x_interface_header_id;
update po_lines_interface pli
set pli.requisition_line_id= null
where pli.interface_header_id= x_interface_header_id
and exists
(select requisition_line_id
from po_requisition_lines_all prl --
where prl.requisition_line_id= pli.requisition_line_id
and (prl.line_location_id is not null
or prl.cancel_flag='Y'
or prl.closed_code='FINALLY CLOSED'
or (prl.auction_header_id<>pli.auction_header_id
and prl.auction_line_number<>pli.auction_line_number
)
)
);
p_message => 'Sourcing to PO FPH-1: before the update for withdrawn reqs');
UPDATE po_lines_interface pli
SET pli.requisition_line_id= null
WHERE pli.interface_header_id= x_interface_header_id
AND NOT EXISTS
(SELECT requisition_line_id
FROM po_requisition_lines_all prl --
WHERE prl.requisition_line_id= pli.requisition_line_id);
SELECT count(*)
INTO g_req_lines_to_process
FROM po_lines_interface
WHERE interface_header_id = x_interface_header_id;
select release_num
into x_release_number
from po_releases_all --
where po_release_id=x_document_id;
select segment1
into x_document_number
from po_headers_all --
where po_header_id=x_document_id;
select segment1
into x_document_number
from po_headers_all
where po_header_id = x_document_id;
so update the header with 'approved' status and the shipment with the approved flag */
IF (g_interface_source_code = 'CONSUMPTION_ADVICE') and
(x_errorcode = 1 ) THEN
IF g_document_subtype = 'STANDARD' THEN
l_progress:= '160';
update po_headers_all
set authorization_status = 'APPROVED',
approved_date = sysdate,
approved_flag = 'Y'
where po_header_id = x_document_id;
update po_line_locations_all
set approved_flag = 'Y',
approved_date = sysdate
where po_header_id = x_document_id ;
update po_releases_all
set authorization_status = 'APPROVED',
approved_date = sysdate,
approved_flag = 'Y'
where po_release_id = x_document_id;
update po_line_locations_all
set approved_flag = 'Y',
approved_date = sysdate
where po_release_id = x_document_id ;
SELECT phi.style_id
INTO l_style_id
FROM po_headers_interface phi
WHERE phi.interface_header_id = x_interface_header_id;
select distinct 'Y'
into X_line_found
from po_lines_interface
where interface_header_id = x_interface_header_id
and line_num is not null;
The x_new_revised_date was added for bug no 491306 to update
the revised_date.But it was inside the loop for standard and planned
po's only.So the revised_date was Releases was incorrect.
Removed those line and put it here to update the revised date for
Standard,planned and Releases.
*/
-- Bug 902976, zxzhang, 10/04/99
-- Change REVISED_DATE from VarChar(25) to Date.
/*
x_new_revised_date := to_char(interface.last_update_date,
'DD/MM/YYYY HH24:MI');
x_new_revised_date := interface.last_update_date;
SELECT max(revision_num)
INTO x_max_revision_num
FROM po_headers_archive_all poha --
WHERE poha.po_header_id = interface.po_header_id;
* Revised date should be updated
* the same time revision is incremented.
* Database field revised_date is defined as varchar(25).
* Use same format as in po_headers_pkg2.check_new_revision
x_new_revised_date := to_char(interface.last_update_date,
'DD-MON-YY HH24:MI');
p_message => 'Update PO_Headers Doc subtype is Std or planned');
UPDATE po_headers_all --
SET approved_flag =
decode(nvl(approved_flag,'N'),'N','N','F','F','R'),
authorization_status =
decode(nvl(authorization_status,'INCOMPLETE'),
'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED',
'REQUIRES REAPPROVAL'),
closed_code = 'OPEN',
-- Bug 1199462 Amitabh
closed_date = NULL,
revision_num = decode(x_max_revision_num, '',
revision_num,
decode(nvl(authorization_status,'INCOMPLETE'),
'APPROVED',
decode(revision_num, x_max_revision_num,
revision_num + 1, revision_num),
revision_num)),
revised_date = decode(x_max_revision_num, '',
revised_date,
decode(nvl(authorization_status,'INCOMPLETE'),
'APPROVED',
decode(revision_num, x_max_revision_num,
x_new_revised_date, revised_date),
revised_date)),
last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login
WHERE po_header_id = interface.po_header_id;
p_message => 'Update PO_Headers Doc subtype is Release');
UPDATE po_headers_all --
SET closed_code = 'OPEN',
closed_date = NULL,
last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by ,
last_update_login = interface.last_update_login
WHERE po_header_id = interface.po_header_id;
SELECT po_release_id
INTO g_po_release_id
FROM po_releases_all --
WHERE release_num = interface.release_num
AND po_header_id = interface.po_header_id
FOR UPDATE OF approved_flag;
SELECT max(revision_num)
INTO x_max_revision_num
FROM po_releases_archive_all --
WHERE po_release_id = g_po_release_id;
p_message => 'Update PO_Releases Doc subtype is Release');
UPDATE PO_RELEASES_ALL --
SET last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login,
closed_code = 'OPEN',
approved_flag = DECODE(approved_flag,
'N','N','F','F',
'R'),
authorization_status = DECODE(authorization_status,
'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED',
'REQUIRES REAPPROVAL'),
revision_num = decode(x_max_revision_num, '',
revision_num,
decode(nvl(authorization_status, 'INCOMPLETE'),
'APPROVED',
decode(x_max_revision_num, revision_num,
revision_num+1, revision_num),
revision_num)),
revised_date = decode(x_max_revision_num, '',
revised_date,
decode(nvl(authorization_status,'INCOMPLETE'),
'APPROVED',
decode(revision_num, x_max_revision_num,
x_new_revised_date, revised_date),
revised_date))
WHERE po_release_id = g_po_release_id;
* site because we always insert a match type of 'PO' */
if g_interface_source_code <> 'CONSUMPTION_ADVICE' then -- bug 2741985
get_invoice_match_option(interface.vendor_id,
interface.vendor_site_id,
vendor.invoice_match_option);
* The date mask on interface.h_rate_date causes this insertion
* failed on tst115 database.
*/
IF(interface.document_subtype = 'STANDARD' or
interface.document_subtype = 'PLANNED' or
--
--Allow to create blanket also
interface.document_subtype = 'BLANKET'
) THEN
l_progress:= '180';
Use the interface table value for pay_on_code when inserting into po_headers.pay_on_code
*/
/*bug #2997337
validating the ship_to and bill_to locations to check whether they are
active or inactive before inserting into the PO_HEADERS table. If any of them
is found inactive then a null value is inserted in the table in the respective
column*/
if (g_interface_source_code = 'SOURCING') then
x_valid_ship_to := nvl(interface.ship_to_location_id, nvl(vendor.ship_to_location_id,
params.ship_to_location_id));
select 'Y' into x_is_valid
from hr_locations_all
where location_id = x_valid_ship_to
and NVL(ship_to_site_flag, 'N') = 'Y' --bug 4229954
and NVL(trunc(inactive_date),trunc(SYSDATE)+1) > trunc(SYSDATE);
select 'Y' into x_is_valid
from hr_locations_all
where location_id = x_valid_bill_to
and NVL(bill_to_site_flag, 'N') = 'Y' --bug 4229954
and NVL(trunc(inactive_date),trunc(SYSDATE)+1) > trunc(SYSDATE);
p_message => 'Before Insert into PO_headers');
INSERT INTO po_headers_all --
(po_header_id,
last_update_date,
last_updated_by,
segment1,
created_by,
last_update_login,
summary_flag,
enabled_flag,
type_lookup_code,
agent_id,
creation_date,
revision_num,
print_count,
closed_code,
frozen_flag,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
ship_via_lookup_code,
fob_lookup_code,
pay_on_code,
freight_terms_lookup_code,
confirming_order_flag,
currency_code,
rate_type,
rate_date,
rate,
acceptance_required_flag,
firm_status_lookup_code,
min_release_amount,
--DPCARD{
pcard_id,
--DPCARD}
--
blanket_total_amount,--Bug# 2288408
start_date, --Bug# 2288408
end_date, --Bug# 2288408
--
--
amount_limit,
global_agreement_flag,
--
--
consume_req_demand_flag,
consigned_consumption_flag, -- CONSIGNED FPI
shipping_control, --
org_id --
,conterms_exist_flag --
,document_creation_method --
,style_id --
,tax_attribute_update_code --
, created_language --
, cpa_reference --
)
VALUES (interface.po_header_id,
interface.last_update_date,
interface.last_updated_by,
interface.document_num,
interface.created_by,
interface.last_update_login,
'N',
'Y',
interface.document_subtype,
interface.agent_id,
interface.creation_date,
interface.revision_num,
interface.print_count,
interface.h_closed_code,
interface.frozen_flag,
interface.vendor_id,
interface.vendor_site_id,
interface.vendor_contact_id,
x_valid_ship_to,
x_valid_bill_to,
decode(g_interface_source_code,'SOURCING',
nvl(interface.terms_id,
nvl(vendor.terms_id,
params.terms_id)),
nvl(vendor.terms_id,
interface.terms_id)),
decode(g_interface_source_code,'SOURCING',
nvl(interface.ship_via_lookup_code,
nvl(vendor.ship_via_lookup_code,
params.ship_via_lookup_code)),
nvl(vendor.ship_via_lookup_code,
interface.ship_via_lookup_code)),
decode(g_interface_source_code,'SOURCING',
nvl(interface.fob_lookup_code,
nvl(vendor.fob_lookup_code,
params.fob_lookup_code)),
nvl(vendor.fob_lookup_code,
interface.fob_lookup_code)),
decode(g_interface_source_code,'SOURCING',
nvl(interface.pay_on_code,
vendor.pay_on_code),
'CONSUMPTION_ADVICE',
interface.pay_on_code,
nvl(vendor.pay_on_code,
interface.pay_on_code)),
decode(g_interface_source_code,'SOURCING',
nvl(interface.freight_terms_lookup_code,
nvl(vendor.freight_terms_lookup_code,
params.freight_terms_lookup_code)),
nvl(vendor.freight_terms_lookup_code,
interface.freight_terms_lookup_code)),
interface.confirming_order_flag,
interface.h_currency_code,
interface.h_rate_type,
-- to_date(interface.h_rate_date, 'DD/MM/YYYY'),
interface.h_rate_date,
interface.h_rate,
interface.acceptance_required_flag,
interface.h_firm_status_lookup_code,
interface.h_min_release_amount,
--DPCARD{
interface.pcard_id,
--DPCARD}
--
decode(g_document_type,'PA',interface.amount_agreed,null),
decode(g_document_type,'PA',interface.effective_date,null),
decode(g_document_type,'PA',interface.expiration_date,null),
--
--
decode(g_document_type, 'PA', nvl(interface.amount_limit, interface.amount_agreed), null),
decode(interface.global_agreement_flag,'N',null,'Y','Y',null), -- bug 2754954
--
--
interface.consume_req_demand_flag,
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'Y',null),
vendor.shipping_control, --
g_purchasing_ou_id --
,p_conterms_exist_flag --
,l_document_creation_method --
,decode(g_interface_source_code,'CONSUMPTION_ADVICE',
PO_DOC_STYLE_GRP.get_standard_doc_style ,interface.style_id) --
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
, interface.created_language --
, interface.cpa_reference --
);
p_message => 'After Insert into PO_headers');
select org_id
into x_org_id
from po_headers_all
where po_header_id = interface.po_header_id;
l_org_assign_rec.last_update_date := interface.last_update_date;
l_org_assign_rec.last_updated_by := interface.last_updated_by;
l_org_assign_rec.last_update_login := interface.last_update_login;
PO_GA_ORG_ASSIGN_PVT.insert_row(p_init_msg_list => 'T',
x_return_status => l_return_status,
p_org_assign_rec => l_org_assign_rec,
x_row_id => l_org_row_id);
p_message => 'After inserting into Org Assignments');
interface.last_update_login,
null,
null,
null,
'NEG');
interface.last_update_login ,
null,
null,
null);
interface.last_update_login ,
null,
null,
null);
SELECT po_releases_s.nextval
INTO g_po_release_id
FROM sys.dual;
Inserting negative of g_po_release_id as release number to
avoid unique constraint violation
*/
l_progress:='280';
p_message => 'Before insert into PO_RELEASES');
INSERT INTO po_releases_all --
(po_release_id,
last_update_date,
last_updated_by,
po_header_id,
release_num,
agent_id,
release_date,
creation_date,
created_by,
last_update_login,
revision_num,
approved_flag,
authorization_status,
print_count,
release_type,
pay_on_code,
pcard_id,
consigned_consumption_flag, -- CONSIGNED FPI
shipping_control, --
org_id --
,document_creation_method --
,tax_attribute_update_code --
,acceptance_required_flag --Bug 7518967 : Default Acceptance Required Check ER
)
VALUES (g_po_release_id,
interface.last_update_date,
interface.last_updated_by,
interface.po_header_id,
-g_po_release_id, --interface.release_num
interface.agent_id,
nvl(interface.release_date,sysdate),
interface.creation_date,
interface.created_by,
interface.last_update_login,
0,
'N',
'INCOMPLETE',
0,
'BLANKET',
interface.pay_on_code,
interface.pcard_id,
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'Y',null), -- CONSIGNED FPI
interface.shipping_control, --
g_purchasing_ou_id --
,l_document_creation_method -- -- Bug 3599251
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
,decode(params.acceptance_required_flag,'N','N', /* Bug 7518967 : Default Acceptance Required Check ER: Geting the default acceptance_required_flag */
'Y','Y',
'D','Y',
'S','Y',
'N')
);
p_message => 'After insert into PO_RELEASES');
UPDATE po_headers_all --
set segment1=x_document_num
where po_header_id=x_document_id;
/* FPI GA start Update the terms after the successful completion of PO */
IF (g_document_subtype = 'STANDARD') and
(g_interface_source_code <> 'CONSUMPTION_ADVICE') then -- CONSIGNED FPI
po_interface_s2.update_terms(x_document_id);
Since while inserting into po_releases , we inserted a negative
number to avoid unique constraint violation, just before
commit we are updating the correct value for release number.
*/
IF (g_document_subtype = 'RELEASE') then
--Added a new loop as a part of 1805397 for fixing unique
--constraint error
--jbalakri
begin
loop
begin
l_progress := '380';
select nvl(max(release_num),0) + 1
into x_release_num
from po_releases_all por --
where por.po_header_id = interface.po_header_id;
update po_releases_all --
set release_num = x_release_num
where po_releases_all.po_header_id = interface.po_header_id
and release_num = -g_po_release_id;
update_req_pool_fail exception;
SELECT prl.org_id
INTO l_requesting_ou_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = interface.requisition_line_id;
** and the following SELECT statement would cause a NO_DATA_FOUND
** exception. Since the X_match_blanket_line variable hasn't been set
** to 'N', the exception handlier does a 'raise', causing the COMMIT
** statement in create_po() to be skipped. Added an if statement below
** so that it wouldn't raise NO_DATA_FOUND exception until the
** X_match_blanket_line variable is set to 'N' later in the code.
*/
IF (interface.line_type_id IS NOT NULL) THEN -- Bug 586033, lpo, 11/25/97
l_progress := '010';
SELECT order_type_lookup_code
, purchase_basis --
INTO x_order_type_lookup_code
, l_purchase_basis --
FROM po_line_types
WHERE line_type_id = interface.line_type_id;
** interface table since we are in the process of inserting
** lines into the po lines table on a record by record basis.
*/
IF(g_document_subtype='STANDARD' or g_document_subtype='PLANNED' or
g_document_type = 'RFQ'
--
--do the select for blanket also
or g_document_subtype = 'BLANKET') THEN
BEGIN
l_progress := '030';
SELECT po_line_id,
line_type_id,
line_num,
item_id,
item_revision,
unit_meas_lookup_code,
base_unit_price, --
unit_price,
transaction_reason_code,
price_break_lookup_code,
manual_price_change_flag --bug 3495772
INTO x_po_line_id,
x_po_line_type_id,
x_line_num,
x_po_item_id,
x_po_item_revision,
x_po_unit_meas_lookup_code,
l_base_unit_price, --
x_po_unit_price,
x_po_transaction_reason_code,
x_price_break_lookup_code,
l_manual_price_change_flag --bug 3495772
FROM PO_LINES_ALL --
WHERE PO_HEADER_ID = interface.po_header_id
AND LINE_NUM = interface.line_num
FOR UPDATE OF quantity;
SELECT po_line_id
INTO x_po_line_id
FROM po_lines_all --
WHERE po_header_id = interface.po_header_id
AND line_num = interface.line_num
FOR UPDATE OF quantity;
SELECT inventory_organization_id INTO x_inv_org_id
FROM financials_system_params_all --
WHERE NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99); --
SELECT process_enabled_flag INTO x_process_org
FROM mtl_parameters
WHERE organization_id = x_inv_org_id;
SELECT segment1
INTO x_item_number
FROM mtl_system_items
WHERE
inventory_item_id = interface.item_id
AND organization_id = x_inv_org_id;
SELECT pol.unit_meas_lookup_code,poh.type_lookup_code
INTO l_ga_uom,l_from_type_lookup_code
FROM po_lines_all pol,po_headers_all poh
WHERE pol.po_line_id = interface.from_line_id
and poh.po_header_id=interface.from_header_id
and poh.po_header_id=pol.po_header_id;
* from the lowest existing level by splitting the select.
*/
/* Bug 1018048
Prior to the fix we were getting the values of receipt required
flag and inspection required flag of the item/master org to
default in the autocreated document and were not considering the
values defined at item/destination organization.
Now, we derive the values from the item/destination organization
and if it is not defined at the item/destination organization
level, then we derive the values from the item/master organization.
*/
l_progress := '150';
SELECT msi.invoice_close_tolerance,
msi.receive_close_tolerance,
msi.inspection_required_flag,
msi.receipt_required_flag
INTO item.invoice_close_tolerance,
item.receive_close_tolerance,
item.inspection_required_flag,
item.receipt_required_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = interface.item_id
AND msi.organization_id = interface.destination_organization_id;
SELECT decode(x_order_type_lookup_code, 'QUANTITY',
msi.list_price_per_unit/nvl(interface.h_rate,1),
1), --
decode(x_order_type_lookup_code, 'QUANTITY',
msi.market_price/nvl(interface.h_rate,1),
1), --
msi.taxable_flag,
msi.primary_uom_code,
nvl(item.inspection_required_flag,msi.inspection_required_flag),
nvl(item.receipt_required_flag,msi.receipt_required_flag),
nvl(item.invoice_close_tolerance,msi.invoice_close_tolerance),
nvl(item.receive_close_tolerance,msi.receive_close_tolerance),
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND,
msi.secondary_uom_code,NULL),--
nvl(msi.grade_control_flag,'N') --
INTO item.list_price_per_unit,
item.market_price,
item.taxable_flag,
item.unit_meas_lookup_code,
item.inspection_required_flag,
item.receipt_required_flag,
item.invoice_close_tolerance,
item.receive_close_tolerance,
item.secondary_uom_code, --
item.grade_control_flag --
FROM mtl_system_items msi
WHERE msi.inventory_item_id = interface.item_id
AND msi.organization_id = params.inventory_organization_id;
SELECT nvl(item.receive_close_tolerance,receipt_close),
nvl(item.receipt_required_flag,receiving_flag)
INTO item.receive_close_tolerance,
item.receipt_required_flag
FROM po_line_types_v
WHERE line_type_id = interface.line_type_id;
/* Bug: 2106201 Select receipt required flag,inspection required flag
at vendor level before system option level to complete the
default logic
*/
Begin
select nvl(item.inspection_required_flag,
vendor.INSPECTION_REQUIRED_FLAG),
nvl(item.receipt_required_flag,
vendor.RECEIPT_REQUIRED_FLAG)
into item.inspection_required_flag,
item.receipt_required_flag
from po_vendors vendor
where vendor.vendor_id = interface.vendor_id;
/* Bug: 1322342 Select receipt required flag,inspection required flag
receipt close tolerance and insp close tolerance
also from po system parameters if not defined at above level
*/
Begin
select nvl(item.inspection_required_flag,
posp.INSPECTION_REQUIRED_FLAG),
nvl(item.receipt_required_flag,
posp.RECEIVING_FLAG),
nvl(item.invoice_close_tolerance,
posp.INVOICE_CLOSE_TOLERANCE),
nvl(item.receive_close_tolerance,
posp.RECEIVE_CLOSE_TOLERANCE)
into item.inspection_required_flag,
item.receipt_required_flag,
item.invoice_close_tolerance,
item.receive_close_tolerance
FROM po_system_parameters_all posp --
WHERE NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99); --
SELECT MIN(DISTRIBUTION_ID)
INTO x_req_dist_id
FROM PO_REQ_DISTRIBUTIONS_ALL --
WHERE REQUISITION_LINE_ID = interface.requisition_line_id
AND PROJECT_ID IS NOT NULL
AND TASK_ID IS NOT NULL;
SELECT MIN(DISTRIBUTION_ID)
INTO x_req_dist_id
FROM PO_REQ_DISTRIBUTIONS_ALL --
WHERE REQUISITION_LINE_ID = interface.requisition_line_id
AND PROJECT_ID IS NOT NULL;
SELECT project_id
,task_id
INTO x_project_id
, x_task_id
FROM PO_REQ_DISTRIBUTIONS_ALL --
WHERE DISTRIBUTION_ID = x_req_dist_id;
select un_number_id,hazard_class_id
into x_un_number_id,x_hazard_class_id
from mtl_system_items
where inventory_item_id = interface.item_id
and organization_id =params.inventory_organization_id;
select unit_of_measure
into x_unit_of_measure
from po_line_types
where line_type_id= interface.line_type_id;
SELECT po_lines_s.nextval
INTO x_po_line_id
FROM sys.dual;
SELECT type_lookup_code,global_agreement_flag into l_type_lookup_code,l_global_agreement_flag
FROM po_headers_all
WHERE po_header_id=interface.from_header_id;
SELECT negotiated_by_preparer_flag into l_negotiated_by_preparer_flag
FROM po_lines_all
WHERE po_line_id=interface.from_line_id;
SELECT negotiated_by_preparer_flag into l_negotiated_by_preparer_flag
FROM po_requisition_lines_all
WHERE requisition_line_id=interface.requisition_line_id;
SELECT negotiated_by_preparer_flag into l_negotiated_by_preparer_flag
FROM po_requisition_lines_all
WHERE requisition_line_id=interface.requisition_line_id;
SELECT retainage_rate
into l_retainage_rate
FROM po_vendor_sites_all
WHERE vendor_site_id = interface.vendor_site_id;
/*Bug 1391523 . Added market price to the INSERT statement */
INSERT INTO po_lines_all --
( po_line_id,
last_update_date,
last_updated_by,
po_header_id,
line_num,
creation_date,
created_by,
last_update_login,
item_id,
job_id, --
category_id,
item_description,
unit_meas_lookup_code,
list_price_per_unit,
market_price,
base_unit_price, --
unit_price,
quantity,
amount, --
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
-- contract_num, --
contract_id, --
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_status_lookup_code,
min_release_amount,
price_type_lookup_code,
transaction_reason_code,
from_header_id,
from_line_id,
from_line_location_id, --
project_id,
task_id,
note_to_vendor,
--togeorge 09/27/2000
--added oke columns
oke_contract_header_id,
oke_contract_version_id,
--togeorge 11/17/2000
--Bug# 1369049
--Added logic to default tax_name in po_lines
tax_name,
-- start of 1548597
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
-- end of 1548597
--
auction_header_id,
auction_line_number,
auction_display_number,
bid_number,
bid_line_number,
quantity_committed, --Bug# 2288408
committed_amount, --Bug# 2288408
--
--Bug #2715037
price_break_lookup_code,
supplier_ref_number, --
org_id, --
start_date, --
expiration_date, --
contractor_first_name, --
contractor_last_name, --
order_type_lookup_code, --
purchase_basis, --
matching_basis --
--
, retainage_rate
, max_retainage_amount
, progress_payment_rate
, recoupment_rate --
,tax_attribute_update_code --
, ip_category_id --Bug#4656615
, supplier_part_auxid --Bug#4656615
, catalog_name --Bug#4656615
)
VALUES (x_po_line_id,
interface.last_update_date,
interface.last_updated_by,
interface.po_header_id,
interface.line_num,
interface.creation_date,
interface.created_by,
interface.last_update_login,
interface.item_id,
interface.job_id, --
interface.category_id,
interface.item_description,
x_unit_of_measure, -- Bug 2735840
--Bug# 2288408
--sourcing populates the unit price in bidder's currency, so we are
-- not converting the currency. And sourcing does not have
--list_price_per_unit and market price stored in their system,
--so dont do the following for sourcing
-- Bug 3472140: Changed precisions to 15 from 5
-- Bug 3808903: Changed rounding to use extended_precision
decode(g_document_type, 'RFQ', null,
decode(g_interface_source_code,'SOURCING',null,
ROUND(item.list_price_per_unit,nvl(x_ext_precision,15)))),
decode(g_interface_source_code,'SOURCING',null,
ROUND(item.market_price,nvl(x_ext_precision,15))),
--
-- Bug 1353736 use precision in rounding
/* Bug: 2000367 When there is no currency conversion involved we should not
round at all because it gives rise to inconsistency.
So removing the ext precision and blind rounding to 5 also as
this is already done above in case when currency conversion is
involved.
*/
l_base_unit_price , --interface.base_unit_price, --
x_unit_price , --interface.unit_price,
--
--quantity sould be null for a blanket
decode(g_document_type, 'RFQ', 1,'PA',null, x_quantity),
interface.amount, --
nvl(item.taxable_flag,params.taxable_flag),
decode(g_document_type, 'RFQ', null,
vendor.type_1099),
l_negotiated_by_preparer_flag, --
interface.l_closed_code,
interface.item_revision,
--
decode(g_interface_source_code,'SOURCING',
x_un_number_id,interface.un_number_id),
decode(g_interface_source_code,'SOURCING',
x_hazard_class_id,interface.hazard_class_id),
--
-- interface.contract_num, --
interface.contract_id, --
interface.line_type_id,
interface.vendor_product_num,
decode(g_document_type, 'RFQ', null,
rc.qty_rcv_tolerance),
decode(g_document_type, 'RFQ', null,
rc.qty_rcv_exception_code),
interface.l_firm_status_lookup_code,
interface.l_min_release_amount,
interface.price_type_lookup_code,
interface.transaction_reason_code,
nvl(interface.from_header_id,x_quote_header_id),
nvl(interface.from_line_id,x_quote_line_id),
l_price_break_id, --
x_project_id,
x_task_id,
--
--dont copy note to vendor for sourcing this
--would come as attachments from sourcing.
decode(g_interface_source_code,'SOURCING',
null,interface.note_to_vendor),
--interface.note_to_vendor,
--
--togeorge 09/27/2000
--added oke columns
interface.oke_contract_header_id,
interface.oke_contract_version_id,
--togeorge 11/17/2000
--Bug# 1369049
--Added logic to default tax_name in po_lines
x_tax_name,
--
-- don't insert secondary unit/quantity/grade from interface record.
-- start of 1548597
x_secondary_unit_def,
x_secondary_quantity_def,
x_preferred_grade_def,
-- end of 1548597
--
--
interface.auction_header_id,
interface.auction_line_number,
interface.auction_display_number,
interface.bid_number,
interface.bid_line_number,
decode ( g_document_type
, 'PA' , decode ( x_order_type_lookup_code
, 'AMOUNT' , NULL
, interface.quantity
)
, NULL
),
decode ( g_document_type
, 'PA' , decode ( x_order_type_lookup_code
, 'QUANTITY' , NULL--
, interface.committed_amount
)
, NULL
),
--
--Bug #2715037
decode(g_interface_source_code,'SOURCING',
interface.price_break_lookup_code, null),
interface.supplier_ref_number, --
g_purchasing_ou_id, --
interface.line_effective_date, --
interface.line_expiration_date, --
interface.contractor_first_name, --
interface.contractor_last_name, --
l_order_type_lookup_code, --
l_purchase_basis1, --
l_matching_basis --
--
, nvl(interface.retainage_rate, l_retainage_rate) -- bug 4887900 bug#5255878
, interface.max_retainage_amount
, interface.progress_payment_rate
, interface.recoupment_rate --
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
, interface.ip_category_id --Bug#4656615
, interface.supplier_part_auxid --Bug#4656615
, interface.catalog_name --Bug#4656615
);
* from the lowest existing level by splitting the select.
* HAD TO DO THE SAME EVEN IF PO LINE ID IS NOT NULL
* ONLY FOR INVOICE CLOSE TOLERANCE AND RECEIVE CLOSE TOLERANCE
*/
l_progress := '460';
SELECT msi.invoice_close_tolerance,
msi.receive_close_tolerance,
msi.receipt_required_flag,
msi.inspection_required_flag
INTO item.invoice_close_tolerance,
item.receive_close_tolerance,
item.receipt_required_flag,
item.inspection_required_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = interface.item_id
AND msi.organization_id = interface.destination_organization_id;
SELECT nvl(item.invoice_close_tolerance,msi.invoice_close_tolerance),
nvl(item.receive_close_tolerance,msi.receive_close_tolerance),
nvl(item.receipt_required_flag,msi.receipt_required_flag),
nvl(item.inspection_required_flag,msi.inspection_required_flag),
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND,
msi.secondary_uom_code,NULL) --
INTO item.invoice_close_tolerance,
item.receive_close_tolerance,
item.receipt_required_flag,
item.inspection_required_flag,
item.secondary_uom_code --
FROM mtl_system_items msi
WHERE msi.inventory_item_id = interface.item_id
AND msi.organization_id = params.inventory_organization_id;
SELECT nvl(item.receive_close_tolerance,receipt_close),
nvl(item.receipt_required_flag,receiving_flag)
INTO item.receive_close_tolerance,
item.receipt_required_flag
FROM po_line_types_v
WHERE line_type_id = interface.line_type_id;
/* Bug: 2106201 Select receipt required flag,inspection required flag
at vendor level before system option level to complete the
default logic
*/
Begin
select nvl(item.inspection_required_flag,
vendor.INSPECTION_REQUIRED_FLAG),
nvl(item.receipt_required_flag,
vendor.RECEIPT_REQUIRED_FLAG)
into item.inspection_required_flag,
item.receipt_required_flag
from po_vendors vendor
where vendor.vendor_id = interface.vendor_id;
/* Bug: 1322342 Select receipt required flag,inspection required flag
receipt close tolerance and insp close tolerance
also from po system parameters if not defined at above level
*/
Begin
select nvl(item.inspection_required_flag,
posp.INSPECTION_REQUIRED_FLAG),
nvl(item.receipt_required_flag,
posp.RECEIVING_FLAG),
nvl(item.invoice_close_tolerance,
posp.INVOICE_CLOSE_TOLERANCE),
nvl(item.receive_close_tolerance,
posp.RECEIVE_CLOSE_TOLERANCE)
into item.inspection_required_flag,
item.receipt_required_flag,
item.invoice_close_tolerance,
item.receive_close_tolerance
FROM po_system_parameters_all posp --
WHERE NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99); --
/* handled the null value for quantity in the following update statement.
bug 935866 */
-- update secondary quantity to somevalue only if old or new secondary_quantity is not null else update it
-- to null(for discrete items) - 1548597
/* GA FPI start : For a standard PO if the source document exists then we call the pricing
API to get the correct price for the parameters on the requisition */
IF (g_document_subtype='STANDARD')
AND nvl(l_manual_price_change_flag, 'N') <> 'Y' --bug 3495772
AND (interface.from_line_id IS NOT NULL OR
--
interface.contract_id IS NOT NULL)
--
AND g_interface_source_code <> 'CONSUMPTION_ADVICE' THEN
/*bug#2723479 In this case, we will be updating an existing po
*line by adding a req line to it. So we use the combined quantity
*(existing po qty + req qty) when calling the pricing API. */
l_progress := '540';
select pl.quantity
into l_db_quantity
from po_lines_all pl --
where pl.po_line_id = x_po_line_id;
select poll.ship_to_location_id,
poll.ship_to_organization_id,
poll.need_by_date
into l_ship_to_loc,
l_ship_to_org,
l_need_by_date
from po_line_locations_all poll
where poll.po_line_id = x_po_line_id
and poll.shipment_num = l_min_shipment_num;
UPDATE po_lines_all
-- Bug 3417479
-- SET unit_price = x_unit_price,
-- base_unit_price = l_base_unit_price,
SET unit_price = nvl(x_unit_price, unit_price),
base_unit_price = nvl(l_base_unit_price, base_unit_price),
from_line_location_id = l_price_break_id --
WHERE po_line_id = x_po_line_id;
UPDATE po_line_locations_all
-- Bug 3417479
-- SET price_override = x_unit_price
SET price_override = nvl(x_unit_price, price_override)
-- Bug 4902592. Not setting tax_attribute_update_code here because
-- it should be passed as CREATE during tax calculation
WHERE po_line_id = x_po_line_id;
/** If FSP org and item combination is dual uom control, update the po lines secondary quantity
with the default conversion based on the PO lines quantity **/
-- update secondary quantity/uom to null
UPDATE po_lines_all --
SET quantity = (nvl(quantity,0) + nvl(x_quantity,0)),
last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login,
closed_code = 'OPEN',
closed_date = NULL,
-- Bug 1199462 Amitabh
closed_by = NULL,
secondary_quantity = null, --
secondary_unit_of_measure = null --
WHERE po_line_id = x_po_line_id
RETURNING quantity INTO l_quantity_temp; --
SELECT unit_of_measure
INTO x_secondary_unit_def
FROM mtl_units_of_measure
WHERE uom_code = item.secondary_uom_code ;
UPDATE po_lines_all
SET secondary_quantity = x_secondary_quantity_def,
secondary_unit_of_measure = x_secondary_unit_def
WHERE po_line_id = x_po_line_id ;
END IF; --Release check for update
l_who_rec.last_update_login := interface.last_update_login;
l_who_rec.last_updated_by := interface.last_updated_by;
l_who_rec.last_update_date := interface.last_update_date;
interface.last_update_login,
'',
'',
'');
SELECT requisition_header_id
INTO x_requisition_header_id
FROM po_requisition_lines_all --
WHERE requisition_line_id = interface.requisition_line_id;
interface.last_update_login,
'',
'',
'');
interface.last_update_login,
'',
'',
'');
SELECT requisition_header_id
INTO x_requisition_header_id
FROM po_requisition_lines_all --
WHERE requisition_line_id = interface.requisition_line_id;
interface.last_update_login,
'',
'',
'');
SELECT requisition_header_id
INTO x_requisition_header_id
FROM po_requisition_lines_all --
WHERE requisition_line_id =interface.requisition_line_id;
interface.last_update_login);
p_message => 'Before selecting one-time attachment');
SELECT fad.attached_document_id
INTO l_one_time_att_doc_id
FROM fnd_attached_documents fad,
fnd_documents_tl fdt
WHERE fad.entity_name = 'PO_LINES'
AND fad.pk1_value = to_char(x_po_line_id)
AND fad.document_id = fdt.document_id
AND fdt.language = USERENV('LANG')
AND fdt.description like 'POR:%'; -- iP unique identifier
UPDATE fnd_attached_documents
SET entity_name = 'PO_SHIPMENTS',
pk1_value = to_char(x_line_location_id),
pk2_value = 'ONE_TIME_LOCATION'
WHERE attached_document_id = l_one_time_att_doc_id;
, X_last_update_login => interface.last_update_login
);
, p_last_update_login => interface.last_update_login
, p_auction_header_id => interface.auction_header_id
, p_auction_line_number => interface.auction_line_number
, p_bid_number => interface.bid_number
, p_bid_line_number => interface.bid_line_number
);
p_message => 'Create_line: Before update of po_requisition_lines');
UPDATE po_requisition_lines_all --
SET line_location_id = x_line_location_id,
--
reqs_in_pool_flag = NULL, --
--
last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login
WHERE requisition_line_id = interface.requisition_line_id;
UPDATE po_requisition_lines_all --
SET on_rfq_flag = 'Y',
last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login
WHERE requisition_line_id = interface.requisition_line_id;
UPDATE PO_ATTR_VALUES_INTERFACE
SET po_line_id = l_po_line_id_tbl(i),
req_template_name = to_char(g_ATTR_VALUES_NULL_ID),
req_template_line_num = to_char(g_ATTR_VALUES_NULL_ID),
inventory_item_id = nvl(inventory_item_id, g_ATTR_VALUES_NULL_ID)
WHERE po_attr_values_interface.interface_header_id = l_interface_header_id_tbl(i)
AND po_attr_values_interface.interface_line_id = l_interface_line_id_tbl(i);
IF g_debug_stmt THEN PO_DEBUG.debug_stmt(d_mod,l_progress,'Number of PO_ATTR_VALUES_INTERFACE rows updated='||SQL%rowcount);END IF;
UPDATE PO_ATTR_VALUES_TLP_INTERFACE
SET po_line_id = l_po_line_id_tbl(i),
req_template_name = to_char(g_ATTR_VALUES_NULL_ID),
req_template_line_num = to_char(g_ATTR_VALUES_NULL_ID),
inventory_item_id = nvl(inventory_item_id, g_ATTR_VALUES_NULL_ID)
WHERE po_attr_values_tlp_interface.interface_header_id = l_interface_header_id_tbl(i)
AND po_attr_values_tlp_interface.interface_line_id = l_interface_line_id_tbl(i);
IF g_debug_stmt THEN PO_DEBUG.debug_stmt(d_mod,l_progress,'Number of PO_ATTR_VALUES_TLP_INTERFACE rows updated='||SQL%rowcount);END IF;
WHEN update_req_pool_fail then
po_message_s.sql_error('CREATE_LINE',l_progress,sqlcode);
p_message => 'Create_documents: update_req_pool_fail exception : SQLCODE '||sqlcode);
x_doctype varchar2(25) := ''; /* used for call to update close state */
x_return_code varchar2(25) := ''; /* used for call to update close state */
SELECT plt.order_type_lookup_code, plt.matching_basis
INTO x_order_type_lookup_code, l_matching_basis
FROM po_line_types plt
WHERE plt.line_type_id = interface.line_type_id;
SELECT poh.type_lookup_code
INTO l_from_type_lookup_code
FROM po_headers_all poh
WHERE poh.po_header_id=interface.from_header_id ;
select unit_meas_lookup_code
into x_po_uom
from po_lines_all pol , --
po_headers_all poh --
where pol.po_header_id = poh.po_header_id
and pol.po_header_id = interface.po_header_id
and pol.line_num = interface.line_num;
SELECT unit_meas_lookup_code
INTO x_po_uom
FROM po_lines_all
WHERE po_line_id = interface.from_line_id;
/* before inserting the quantity into the shipments table convert the quantity
into the BPA uom if the uom's on the req and BPA are different .
This conversion is done only if the Convert UOM profile option is set to Yes. */
IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
p_token => l_progress,
p_message => 'Create_shipment: UOM: '||x_temp_uom);
SELECT poll.line_location_id,poll.secondary_unit_of_measure --
INTO x_line_location_id,x_secondary_unit_of_measure
FROM po_line_locations_all poll, --
po_lines_all pol --
WHERE poll.po_header_id = interface.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.shipment_num = interface.shipment_num
AND pol.line_num = interface.line_num
AND poll.shipment_type in ('STANDARD','PLANNED', 'RFQ');
SELECT poll.line_location_id,
poll.manual_price_change_flag, --bug 3495772
poll.secondary_unit_of_measure --
INTO x_line_location_id,
l_manual_price_change_flag, --bug 3495772
x_secondary_unit_of_measure
FROM po_line_locations_all poll, --
po_lines_all pol, --
po_releases_all por --
WHERE poll.po_header_id = interface.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.shipment_num = interface.shipment_num
AND pol.line_num = interface.line_num
AND poll.po_release_id=por.po_release_id
AND por.po_release_id=x_po_release_id;
SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
INTO x_price_break_type
FROM po_lines_all --
WHERE po_line_id = x_po_line_id;
select nvl(quantity, 0)
into x_ship_qty
from po_line_locations_all --
where line_location_id = x_line_location_id;
SELECT unit_price
INTO x_price
FROM po_lines_all --
WHERE po_line_id=x_po_line_id;
** Update everything except closed_code
*/
l_progress:='120';
p_message => 'Create_shipment: Update PO line locations');
UPDATE po_line_locations_all --
SET quantity = quantity + x_quantity,
-- start of 1548597
secondary_quantity = secondary_quantity + x_secondary_quantity,
-- end of 1548597
approved_flag = DECODE(approved_flag,
'N','N', 'R'),
last_update_date = interface.last_update_date,
last_update_login = interface.last_update_login,
last_updated_by = interface.last_updated_by,
price_override = decode(g_document_type, 'RFQ',
price_override, DECODE(
nvl(x_price, -1),
-1, price_override,
-- Bug 1353736 use precision in rounding
-- Bug 3472140: Changed precisions to 15
ROUND(x_price, nvl(x_ext_precision,15)))),
-- Bug 5067321. Setting tax_attribute_update_code to update for
-- add_to cases.
tax_attribute_update_code = NVL(tax_attribute_update_code,
NVL2(g_calculate_tax_flag, 'UPDATE', null))
WHERE line_location_id = x_line_location_id;
** Therefore, no need to do call back for update shipment
** Removed oe callback.
*/
/*
** Prepare to call pocupdate_close: - call auto close.
*/
IF (g_document_type = 'PO') THEN
IF (g_mode = 'ADD') THEN
IF (g_document_type = 'PO') THEN
IF (g_document_subtype = 'RELEASE') THEN
x_doctype := 'RELEASE';
** Prepare to call pocupdate_close: - call manual close
** for the line level.
*/
IF (g_mode = 'ADD') THEN
IF (g_document_subtype = 'RELEASE') THEN
x_doctype := 'RELEASE';
SELECT po_line_locations_s.nextval
INTO x_line_location_id
FROM sys.dual;
Last accept date is also inserted into po_line_locations table.
last_accept_date = interface.need_by_date+rc.days_late_receipt_allowed.
Purposely , null handling has not been done, since even if either
need_by_date or days_late_received_allowed is null then the last_accept_date
should be null.
*/
-- Bug 1353736 Call fnd_currency.get_info to get the precision
--added by jbalakri for 1805397
IF interface.h_currency_code IS NOT NULL THEN
fnd_currency.get_info(interface.h_currency_code,
x_precision,
x_ext_precision,
x_min_unit );
p_message => 'Create shipment: Before insert into po line locations');
INSERT INTO po_line_locations_all --
(line_location_id,
last_update_date,
last_updated_by,
po_header_id,
creation_date,
created_by,
last_update_login,
po_line_id,
quantity,
quantity_received,
quantity_accepted,
quantity_rejected,
quantity_billed,
quantity_cancelled,
amount, --
amount_received, --
amount_accepted, --
amount_rejected, --
amount_billed, --
amount_cancelled, --
ship_to_location_id,
need_by_date,
promised_date,
from_header_id,
from_line_id,
--togeorge 09/27/2000
--added note to receiver column
note_to_receiver,
approved_flag,
po_release_id,
closed_code,
closed_reason,
price_override,
encumbered_flag,
shipment_type,
shipment_num,
inspection_required_flag,
receipt_required_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
ship_to_organization_id,
invoice_close_tolerance,
receive_close_tolerance,
accrue_on_receipt_flag,
allow_substitute_receipts_flag,
receiving_routing_id,
qty_rcv_tolerance,
qty_rcv_exception_code,
receipt_days_exception_code,
terms_id,
ship_via_lookup_code,
freight_terms_lookup_code,
fob_lookup_code,
unit_meas_lookup_code,
last_accept_date, -- zxzhang, Mar 04
match_option, -- bgu, Dec. 7, 98
country_of_origin_code, --frkhan 1/12/99
-- start of 1548597
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_accepted,
secondary_quantity_rejected,
secondary_quantity_cancelled,
-- end of 1548597
vmi_flag, -- VMI FPH
drop_ship_flag, --
consigned_flag, -- CONSIGNED FPI
transaction_flow_header_id, --
org_id --
--
, closed_for_receiving_date
, closed_for_invoice_date
--
--
, value_basis
, matching_basis
--
, outsourced_assembly --
,tax_attribute_update_code --
)
VALUES (x_line_location_id,
interface.last_update_date,
interface.last_updated_by,
interface.po_header_id,
interface.creation_date,
interface.created_by,
interface.last_update_login,
x_po_line_id,
x_quantity, --interface.quantity,
0,
0,
0,
0,
0,
interface.amount, -- amount --
0, -- amount_received --
0, -- amount_accepted --
0, -- amount_rejected --
0, -- amount_billed --
0, -- amount_cancelled --
x_ship_to_location_id,
interface.need_by_date,
l_promised_date, --
interface.from_header_id,
interface.from_line_id,
--togeorge 09/27/2000
--added note to receiver column
interface.note_to_receiver,
decode(g_document_type, 'RFQ', '', 'N'),
decode(g_document_subtype,'RELEASE',x_po_release_id,''),
decode(interface.consigned_flag, 'Y', 'CLOSED FOR INVOICE' , -- CONSIGNED FPI
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'CLOSED FOR RECEIVING' , -- CONSIGNED FPI
decode(g_document_type, 'RFQ', '', 'OPEN'))),
x_closed_reason, -- CONSIGNED FPI
/* Bug: 2000367 When there is no currency conversion involved we should not
round at all because it gives rise to inconsistency.
So removing the ext precision and blind rounding to 5 also as
this is already done above in case when currency conversion is
involved.
*/
nvl(x_price,interface.unit_price),
decode(g_document_type, 'RFQ', '', 'N'),
decode(g_document_type, 'RFQ', 'RFQ',
Decode(interface.document_subtype,
'RELEASE','BLANKET',
interface.document_subtype)),
interface.shipment_num,
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'N' , -- CONSIGNED FPI
decode(interface.consigned_flag, 'Y', 'N', --bug 2861408
decode(interface.drop_ship_flag, 'Y', 'N', --bug 3330748
decode(x_order_type_lookup_code,'FIXED PRICE','N','RATE','N',--bug 3483786
decode(g_document_type, 'RFQ',
nvl(item.inspection_required_flag,
nvl(params.inspection_required_flag,'N')),
nvl(item.inspection_required_flag,
nvl(vendor.inspection_required_flag,
nvl(params.inspection_required_flag,'N')))))
)
)
),
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'N' , -- CONSIGNED FPI
decode(interface.consigned_flag, 'Y', 'N', --bug 2861408
decode(g_document_type, 'RFQ',
nvl(item.receipt_required_flag,
nvl(interface.receipt_required_flag,
nvl(params.receiving_flag,'N'))),
nvl(item.receipt_required_flag,
nvl(interface.receipt_required_flag,
nvl(vendor.receipt_required_flag,
nvl(params.receiving_flag,'N'))))
)
)
),
decode(g_document_type, 'RFQ', '',
rc.days_early_receipt_allowed),
decode(g_document_type, 'RFQ', '',
rc.days_late_receipt_allowed),
decode(g_document_type, 'RFQ', '',
rc.enforce_ship_to_location_code),
interface.destination_organization_id, -- ship to org
decode(interface.consigned_flag, 'Y', 100 , -- CONSIGNED FPI
(decode(g_document_type, 'RFQ', '',
(decode(interface.pcard_id, NULL,
nvl(item.invoice_close_tolerance,
params.invoice_close_tolerance), 100))))),
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 100 , -- CONSIGNED FPI
(decode(g_document_type, 'RFQ', '',
nvl(item.receive_close_tolerance,
params.receive_close_tolerance)))),
/** BUG 843414, bgu, Mar. 23, 1999
* "Accrue on Receipt" should not be allowed for P-card
* orders because of accounting restrictions.
*/
decode(interface.transaction_flow_header_id, NULL, --
decode(interface.consigned_flag, 'Y', 'N' , -- CONSIGNED FPI
decode(g_document_type, 'RFQ', '',
DECODE( interface.pcard_id, NULL,
DECODE(interface.destination_type_code,
'EXPENSE',DECODE(nvl(item.receipt_required_flag,
nvl(interface.receipt_required_flag,
nvl(vendor.receipt_required_flag,
nvl(params.receiving_flag,'N')))),
'N', 'N',
DECODE(params.expense_accrual_code,
'PERIOD END', 'N', 'Y')),
'Y'),'N'))), 'Y'), --
decode(g_document_type, 'RFQ','',
rc.allow_substitute_receipts_flag),
decode(g_document_type, 'RFQ', '',
rc.receiving_routing_id),
rc.qty_rcv_tolerance,
rc.qty_rcv_exception_code,
decode(g_document_type, 'RFQ', '',
rc.receipt_days_exception_code),
decode(g_document_type, 'RFQ', interface.terms_id, ''),
decode(g_document_type, 'RFQ', interface.ship_via_lookup_code,
''),
decode(g_document_type, 'RFQ',
interface.freight_terms_lookup_code, ''),
decode(g_document_type, 'RFQ',
interface.fob_lookup_code, ''),
/* Bug 3913683 : we want to import the unit measure lookup code for
all document types and not just RFQ's so commenting out
below decode. */
/* decode(g_document_type, 'RFQ',
interface.unit_meas_lookup_code), */
-- bug 5208159
nvl(x_po_uom,interface.unit_meas_lookup_code),
-- Bug 3496450. Based the defaulting on promised date going
-- into the database rather than needby date. Put a to_date
-- around null so that decode returns date and does not
-- truncate time information
decode(g_document_type,'RFQ',to_date(null),l_promised_date+rc.days_late_receipt_allowed),
decode(g_interface_source_code,'CONSUMPTION_ADVICE', 'P' , -- CONSIGNED FPI
decode(interface.consigned_flag, 'Y', 'P', --bug 2861408
decode(g_document_type, 'RFQ', '', --bgu, Dec. 7, 98
vendor.invoice_match_option)
)
),
x_country_of_origin_code,
-- replace interface.secondary_unit_of_measure/secondary quantity with variables.
-- also replace in the decode
-- start of 1548597
x_secondary_unit_of_measure,
x_secondary_quantity,
interface.preferred_grade,
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
-- end of 1548597
interface.vmi_flag , -- VMI FPH
interface.drop_ship_flag, --
interface.consigned_flag, -- CONSIGNED FPI
interface.transaction_flow_header_id, --
g_purchasing_ou_id --
--
, decode(g_interface_source_code,'CONSUMPTION_ADVICE',
sysdate,null) --- Closed_for_receiving_date
, decode(interface.consigned_flag, 'Y',
sysdate,null ) --- Closed_for_invoice_date
--
--
, x_order_type_lookup_code
, l_matching_basis
--
, p_outsourced_assembly --
, nvl2(g_calculate_tax_flag, 'CREATE', null) --
);
p_message => 'Create shipment: After insert into po line locations');
SELECT PO_HEADER_ID
,PO_LINE_ID
INTO x_p_po_header_id
,x_p_po_line_id
FROM PO_LINE_LOCATIONS_ALL --
WHERE LINE_LOCATION_ID = x_line_location_id;
SELECT requisition_header_id
INTO x_p_req_header_id
FROM po_requisition_lines_all --
WHERE requisition_line_id = interface.requisition_line_id;
oe_drop_ship_grp.update_po_info(x_p_api_version,
x_p_return_status,
x_p_msg_count,
x_p_msg_data,
x_p_req_header_id,
interface.requisition_line_id,
x_p_po_header_id,
x_p_po_line_id,
x_p_line_location_id,
x_p_po_release_id
);
select po_distribution_id
from po_distributions_all --
where line_location_id = x_line_location_id;
SELECT nvl(max(distribution_num), 0)
INTO x_distribution_num
FROM po_distributions_all --
WHERE line_location_id = x_line_location_id;
SELECT order_type_lookup_code
INTO x_order_type_lookup_code
FROM po_line_types
WHERE line_type_id = interface.line_type_id;
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_params_all --
WHERE NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99); --
SELECT KANBAN_CARD_ID
INTO x_kanban_card_id
FROM po_requisition_lines_all pol --
WHERE pol.REQUISITION_LINE_ID = interface.requisition_line_id;
SELECT application_id
INTO x_po_appl_id
FROM fnd_application
WHERE application_short_name = 'PO';
SELECT application_id
INTO x_gl_appl_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT PS1.period_name
INTO params.period_name
FROM GL_PERIOD_STATUSES PS1
, GL_PERIOD_STATUSES PS2
, GL_SETS_OF_BOOKS GSOB
WHERE PS1.application_id = x_gl_appl_id
AND PS1.set_of_books_id = params.sob_id
AND PS1.adjustment_period_flag = 'N'
AND trunc(sysdate) BETWEEN trunc(PS1.start_date)
AND trunc(PS1.end_date)
AND ps1.period_year <= gsob.latest_encumbrance_year
AND gsob.set_of_books_id = params.sob_id
AND PS1.period_name = PS2.period_name
AND PS2.application_id = x_po_appl_id
AND PS2.adjustment_period_flag = 'N'
AND PS2.set_of_books_id = params.sob_id;
* need to put the NEXTVAL inside the insert as there may
* be more than one distribution.
*/
/* Enh : 1660036 */
-- bug 5208159 : Conversion of req UOM to Quotation UOM should always happen if the
-- source document is a quote and profile 'PO: Convert Requisition UOM to Source Document UOM'
-- should be ignored in that case
IF (interface.from_line_id IS NOT NULL) AND (g_document_subtype = 'STANDARD') THEN
l_progress := '085';
SELECT poh.type_lookup_code
INTO l_from_type_lookup_code
FROM po_headers_all poh
WHERE poh.po_header_id=interface.from_header_id ;
select unit_meas_lookup_code
into x_po_uom
from po_lines_all pol , --
po_headers_all poh --
where pol.po_header_id = poh.po_header_id
and pol.po_header_id = interface.po_header_id
and pol.line_num = interface.line_num;
SELECT unit_meas_lookup_code
INTO x_po_uom
FROM po_lines_all
WHERE po_line_id = interface.from_line_id;
/* before inserting into the distributions table get the conversion rate to convert
into the BPA uom if the uom's on the req and BPA are different .
This conversion is done only if the Convert UOM profile option is set to Yes. */
if interface.unit_meas_lookup_code <> x_po_uom then
x_conversion_rate := po_uom_s.po_uom_convert(interface.unit_meas_lookup_code,
x_po_uom,
interface.item_id);
UPDATE po_distributions_interface
SET po_distribution_id = po_distributions_s.NEXTVAL,
distribution_num = x_distribution_num + rownum
WHERE interface_header_id = interface.interface_header_id
AND interface_line_id = interface.interface_line_id;
update_award_distributions;
p_message => 'Sourcing to FPH-5 ends and insert into distributions');
select nvl(drop_ship_flag,'N') into l_drop_ship_flag
from po_line_locations_all where
line_location_id=x_line_location_id;
SELECT pdi.amount_ordered
INTO l_amount_ordered
FROM po_distributions_interface pdi, po_line_locations_all poll
WHERE pdi.interface_header_id = interface.interface_header_id
AND pdi.interface_line_id = interface.interface_line_id
AND poll.line_location_id = x_line_location_id;
SELECT PRL.currency_code,
Nvl(PRL.rate,1)
INTO x_req_currency_code,
x_req_rate
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = INTERFACE.requisition_line_id;
INSERT INTO po_distributions_all --
(po_distribution_id,
last_update_date,
last_updated_by,
po_header_id,
creation_date,
created_by,
last_update_login,
po_line_id,
line_location_id,
po_release_id,
req_distribution_id,
set_of_books_id,
code_combination_id,
deliver_to_location_id,
deliver_to_person_id,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
amount_ordered, --
amount_delivered, --
amount_cancelled, --
amount_billed, --
rate_date,
rate,
accrued_flag,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
distribution_num,
destination_type_code,
destination_organization_id,
destination_subinventory,
budget_account_id,
accrual_account_id,
variance_account_id,
--< Shared Proc FPJ Start >
dest_charge_account_id,
dest_variance_account_id,
--< Shared Proc FPJ End >
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
expenditure_type,
project_accounting_context,
destination_context,
expenditure_organization_id,
expenditure_item_date,
accrue_on_receipt_flag,
kanban_card_id,
tax_recovery_override_flag, --
recovery_rate,
award_id,
--togeorge 09/27/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id,
org_id, --
distribution_type, --
tax_attribute_update_code, --
interface_distribution_ref --
)
SELECT pdi.po_distribution_id, --
interface.last_update_date,
interface.last_updated_by,
interface.po_header_id,
interface.creation_date,
interface.created_by,
interface.last_update_login,
x_po_line_id,
x_line_location_id,
decode(g_document_subtype,'RELEASE',x_po_release_id,''),
pdi.req_distribution_id,
nvl(x_sob_id, pdi.set_of_books_id), --
pdi.charge_account_id,
pdi.deliver_to_location_id,
--bug#3603067 if the drop_ship_flag is 'Y' then we
--need to pass null
decode(l_drop_ship_flag,'Y',NULL,pdi.deliver_to_person_id),
--
-- Bug 3472140: Changed precisions to 15
decode(
x_order_type_lookup_code,
'QUANTITY',
round((pdi.quantity_ordered * x_conversion_rate), 15),
'AMOUNT',
round(
(pdi.quantity_ordered
* x_conversion_rate
/ x_rate), -- -- Bug 7661419
nvl(x_ext_precision, 15)
),
NULL
),
--
0,
0,
0,
--
decode ( x_order_type_lookup_code -- amount_ordered
, 'RATE' ,round ( ( pdi.amount_ordered
* l_uom_conversion_rate
/ l_currency_conversion_rate)
, x_precision )
, 'FIXED PRICE',round ( ( pdi.amount_ordered
/ l_currency_conversion_rate)
, x_precision )
,NULL
),
0, -- amount_delivered
0, -- amount_cancelled
0, -- amount_billed
--
pdi.rate_date,
pdi.rate,
x_accrued_flag,
'N'
--
-- If Req encumbrance is on and the profile option requests
-- that the Req's GL date be used, use the Req's GL date.
-- Otherwise, if PO enc is on, use SYSDATE.
-- if PO enc is not on, use NULL.
-- gl_encumbered_date =
, NVL( DECODE( params.req_encumbrance_flag
, 'Y', DECODE( x_gl_date_option
, 'REQ GL DATE', pdi.gl_encumbered_date
, NULL
)
, NULL
)
, DECODE( params.po_encumbrance_flag
, 'Y', TRUNC(SYSDATE)
, NULL
)
)
-- gl_encumbered_period_name =
, NVL( DECODE( params.req_encumbrance_flag
, 'Y', DECODE(x_gl_date_option
, 'REQ GL DATE', pdi.gl_encumbered_period_name
, NULL
)
, NULL
)
, DECODE( params.po_encumbrance_flag
, 'Y', params.period_name
, NULL
)
)
, pdi.distribution_num, --
pdi.destination_type_code,
pdi.destination_organization_id,
pdi.destination_subinventory,
pdi.budget_account_id,
pdi.accrual_account_id,
pdi.variance_account_id,
--< Shared Proc FPJ Start >
-- Copy the receiving accounts from the interface table to
-- the PO table.
pdi.dest_charge_account_id,
pdi.dest_variance_account_id,
--< Shared Proc FPJ End >
pdi.wip_entity_id,
pdi.wip_line_id,
pdi.wip_repetitive_schedule_id,
pdi.wip_operation_seq_num,
pdi.wip_resource_seq_num,
pdi.bom_resource_id
--
-- prevent_encumbrance_flag =
, DECODE( pdi.destination_type_code
, g_dest_type_code_SHOP_FLOOR, 'Y'
, 'N'
)
, pdi.project_id,
pdi.task_id,
pdi.end_item_unit_number,
pdi.expenditure_type,
pdi.project_accounting_context,
pdi.destination_context,
pdi.expenditure_organization_id,
pdi.expenditure_item_date,
decode(interface.transaction_flow_header_id, NULL, --
DECODE(interface.destination_type_code,
'EXPENSE',
decode(nvl(item.receipt_required_flag,
nvl(interface.receipt_required_flag,
nvl(vendor.receipt_required_flag,
nvl(params.receiving_flag,'N')))),
'N', 'N',
decode(params.expense_accrual_code,
'PERIOD END', 'N', 'Y')),
'INVENTORY', 'Y',
'SHOP FLOOR', 'Y'),
'Y'), --
x_kanban_card_id,
pdi.tax_recovery_override_flag, --
decode(pdi.tax_recovery_override_flag, 'Y', pdi.recovery_rate, null), --
pdi.award_id, -- OGM_0.0 changes..
--togeorge 09/27/2000
--added oke columns
interface.oke_contract_line_id,
interface.oke_contract_deliverable_id,
g_purchasing_ou_id, --
poll.shipment_type, --
nvl2(g_calculate_tax_flag, 'CREATE', null), --
pdi.interface_distribution_ref --
FROM po_distributions_interface pdi, po_line_locations_all poll
WHERE pdi.interface_header_id = interface.interface_header_id
AND pdi.interface_line_id = interface.interface_line_id
AND poll.line_location_id = x_line_location_id; --
SELECT sum(amount_ordered)
, max(po_distribution_id)
INTO l_sum_dist_amounts
, l_last_distribution_id
FROM po_distributions_all
WHERE line_location_id = p_line_location_id;
SELECT amount
INTO l_shipment_amount
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
UPDATE po_distributions_all
SET amount_ordered = l_shipment_amount - (l_sum_dist_amounts - amount_ordered)
WHERE po_distribution_id = l_last_distribution_id
RETURNING amount_ordered
INTO l_last_dist_amount;
SELECT sum(pod.quantity_ordered), max(pod.po_distribution_id)
INTO l_sum_dist_quantities, l_last_dist_id
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_location_id;
SELECT poll.quantity
INTO l_shipment_quantity
FROM po_line_locations_all poll
WHERE poll.line_location_id = p_line_location_id;
UPDATE po_distributions_all pod
SET pod.quantity_ordered = l_shipment_quantity -
(l_sum_dist_quantities - pod.quantity_ordered)
WHERE pod.po_distribution_id = l_last_dist_id
RETURNING pod.quantity_ordered INTO l_last_dist_qty;
UPDATE po_headers_all --
SET last_update_date = interface.last_update_date,
last_updated_by = interface.last_updated_by,
last_update_login = interface.last_update_login,
status_lookup_code = 'I'
WHERE po_header_id = interface.po_header_id;
SELECT quotation_class_code
INTO x_quotation_class_code
FROM po_document_types
WHERE document_type_code = 'RFQ'
and document_subtype = interface.quote_type_lookup_code;
* For inserting record into po_headers view, it used
* to_date(interface.h_rate_date, 'DD/MM/YYYY') for column rate_date.
* But since the data type of interface.h_rate_date is already date,
* this is unneccssary and causing problem when system date mask is
* defined otherwise.
*/
INSERT INTO po_headers_all --
(po_header_id,
last_update_date,
last_updated_by,
segment1,
created_by,
last_update_login,
summary_flag,
enabled_flag,
type_lookup_code,
agent_id,
creation_date,
revision_num,
ship_to_location_id,
bill_to_location_id,
terms_id,
ship_via_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
status_lookup_code,
quotation_class_code,
quote_type_lookup_code,
approval_required_flag,
currency_code,
rate_type,
rate_date,
rate,
org_id --
,style_id --
)
VALUES (interface.po_header_id,
interface.last_update_date,
interface.last_updated_by,
interface.document_num,
interface.created_by,
interface.last_update_login,
'N',
'Y',
g_document_type,
interface.agent_id,
interface.creation_date,
0,
nvl(vendor.ship_to_location_id,
interface.ship_to_location_id),
nvl(vendor.bill_to_location_id,
interface.bill_To_Location_Id),
nvl(vendor.terms_id,
interface.terms_id),
nvl(vendor.ship_via_lookup_code,
interface.ship_via_lookup_code),
nvl(vendor.fob_lookup_code,
interface.fob_lookup_code),
nvl(vendor.freight_terms_lookup_code,
interface.freight_terms_lookup_code),
'I',
x_quotation_class_code,
interface.quote_type_lookup_code,
'N',
interface.h_currency_code,
interface.h_rate_type,
-- to_date(interface.h_rate_date, 'DD/MM/YYYY'),
interface.h_rate_date, -- Bug 881882 , bgu
interface.h_rate,
g_purchasing_ou_id --
,interface.style_id --
);
Using po_vendor_list_entries_v to insert into po_rfq_vendors
as po_vendor_list_entries_v contains vendor_list with active vendors
*/
INSERT INTO po_rfq_vendors
(po_header_id,
sequence_num,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
vendor_id,
vendor_site_id,
vendor_contact_id,
print_flag,
print_count)
SELECT interface.po_header_id,
rownum,
interface.last_update_date,
interface.last_updated_by,
interface.last_update_login,
interface.creation_date,
interface.created_by,
vendor_id,
vendor_site_id,
vendor_contact_id,
'Y',
0
FROM po_vendor_list_entries_v
WHERE vendor_list_header_id = interface.vendor_list_header_id;
UPDATE po_headers set segment1=x_document_num
where po_header_id=x_document_id;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendor_sites_all --
WHERE vendor_site_id = X_vendor_site_id;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendors
WHERE vendor_id = X_vendor_id;
SELECT aps.match_option
INTO x_invoice_match_option
FROM ap_product_setup aps;
SELECT fsp.match_option
INTO x_invoice_match_option
FROM financials_system_params_all fsp --
WHERE NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99); --*/
SELECT pdi.interface_distribution_id,
pli.interface_line_id,
pli.category_id,
pdi.charge_account_id, -- to be copied onto Dest Charge Account
pdi.variance_account_id, -- to be copied onto Dest Variance Account
pdi.destination_organization_id, -- DINV
TO_NUMBER(hoi.org_information3), -- DOU
pli.item_id,
pli.category_id,
pdi.destination_type_code,
pdi.deliver_to_location_id,
pdi.deliver_to_person_id,
pli.line_type_id,
phi.vendor_id,
phi.agent_id,
pdi.expenditure_organization_id,
pdi.project_id,
pdi.task_id,
pdi.bom_resource_id,
pdi.wip_entity_id,
pdi.wip_line_id,
pdi.wip_repetitive_schedule_id,
pdi.gl_encumbered_date,
pdi.destination_subinventory,
pdi.expenditure_type,
pdi.expenditure_item_date,
pdi.wip_operation_seq_num,
pdi.wip_resource_seq_num,
-- Bug 3463242 START
pli.requisition_line_id,
--
nvl(prl.org_id, g_hdr_requesting_ou_id) requesting_ou_id,
pli.unit_price,
pli.base_unit_price,
pli.amount,
NVL(plt.order_type_lookup_code,'QUANTITY'),
phi.currency_code,
phi.rate_type,
phi.rate_date,
phi.rate,
pdi.rate
-- Bug 3463242 END
FROM PO_DISTRIBUTIONS_INTERFACE pdi,
PO_LINES_INTERFACE pli,
PO_HEADERS_INTERFACE phi,
PO_REQUISITION_LINES_ALL prl, --
MTL_PARAMETERS mp,
HR_ORGANIZATION_INFORMATION hoi,
PO_LINE_TYPES_B plt -- Bug 3463242
WHERE phi.interface_header_id = p_interface_header_id
AND pli.interface_header_id = phi.interface_header_id
AND pli.requisition_line_id = prl.requisition_line_id(+) --
AND pdi.interface_line_id = pli.interface_line_id
AND mp.organization_id = pli.ship_to_organization_id
AND mp.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information3 <> TO_CHAR(g_purchasing_ou_id) -- DOU <> POU
AND pli.line_type_id = plt.line_type_id (+) -- Bug 3463242
ORDER BY pli.interface_line_id;
UPDATE po_lines_interface
SET transaction_flow_header_id = l_transaction_flow_header_id
WHERE interface_line_id = l_interface_line_id;
p_message => 'Transaction flow header id updated: SQL%ROWCOUNT = '||SQL%ROWCOUNT);
UPDATE po_distributions_interface
SET charge_account_id = l_charge_account_id,
variance_account_id = l_variance_account_id,
accrual_account_id = l_accrual_account_id,
budget_account_id = NULL,
dest_charge_account_id = l_dest_charge_account_id,
dest_variance_account_id = l_dest_variance_account_id
WHERE interface_distribution_id = l_interface_distribution_id;
SELECT PRL.currency_code,
Nvl(PRL.rate,1)
INTO l_req_currency_code,
l_req_rate
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_requisition_line_id;
SELECT PRL.currency_code
, GSB.currency_code
, PRL.unit_price
, nvl(PRL.currency_unit_price, PRL.unit_price)
, PRL.amount
, nvl(PRL.currency_amount, PRL.amount)
INTO l_req_currency_code
, l_req_ou_currency_code
, l_req_unit_price
, l_req_currency_unit_price
, l_req_amount
, l_req_currency_amount
FROM po_requisition_lines_all PRL,
financials_system_params_all FSP,
gl_sets_of_books GSB
WHERE PRL.requisition_line_id = p_requisition_line_id
AND nvl(PRL.org_id, -99) = nvl(FSP.org_id, -99)
AND FSP.set_of_books_id = GSB.set_of_books_id;
Cursor C is select pli.rowid,
pli.item_id,
phi.vendor_id,
phi.vendor_site_id,
pdi.destination_organization_id
from po_lines_interface pli,
po_headers_interface phi,
po_distributions_interface pdi
where phi.interface_header_id = x_interface_header_id
and phi.interface_header_id = pli.interface_header_id
and pdi.interface_distribution_id =
(SELECT min(pdi2.interface_distribution_id)
FROM po_distributions_interface pdi2
WHERE pdi2.interface_line_id = pli.interface_line_id)
and pli.item_id is not null
and phi.vendor_id is not null
and pli.vendor_product_num is null;
SELECT pli.interface_header_id,
pli.interface_line_id,
pli.item_id,
pli.line_type_id,
pli.quantity,
pli.amount, --
pli.category_id,
pli.ship_to_location_id,
pli.ship_to_organization_id,
phi.vendor_id,
phi.vendor_site_id,
phi.agent_id,
phi.rate,
phi.rate_date,
phi.document_subtype,
pli.unit_price --
FROM po_lines_interface pli,
po_headers_interface phi,
po_line_types plt
WHERE phi.interface_header_id = x_interface_header_id
AND phi.interface_header_id = pli.interface_header_id
AND pli.requisition_line_id is null
AND plt.line_type_id = pli.line_type_id;
UPDATE po_headers_interface
SET process_code = 'IN PROCESS'
WHERE interface_header_id = x_interface_header_id;
p_message => 'Setup interface: before select action type');
SELECT min(action),
min(group_code),
min(document_num)
INTO g_mode,
g_group_code,
x_old_document_num
FROM po_headers_interface
WHERE interface_header_id = x_interface_header_id;
UPDATE po_headers_interface phi
SET po_header_id =
(SELECT ph.po_header_id
FROM po_headers_all ph --
WHERE 'RFQ' = ph.type_lookup_code
AND phi.document_num = ph.segment1
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)) --
WHERE interface_header_id = x_interface_header_id;
UPDATE po_headers_interface phi
SET po_header_id =
(SELECT ph.po_header_id
FROM po_headers_all ph --
WHERE decode(phi.document_subtype,
'RELEASE','BLANKET',
phi.document_subtype) = ph.type_lookup_code
AND phi.document_num = ph.segment1
AND NVL(org_id, -99) = NVL(g_purchasing_ou_id, -99)) --
WHERE interface_header_id = x_interface_header_id;
p_message => 'After update of headers interface');
UPDATE po_headers_interface phi
SET (
rate,
rate_type_code,
rate_date,
currency_code) =
(SELECT
rate,
rate_type,
rate_date,
currency_code
FROM po_headers_all ph --
WHERE ph.po_header_id = phi.po_header_id)
WHERE interface_header_id = x_interface_header_id;
p_message => 'After update of headers interfacei rate and etc.');
SELECT po_headers_s.nextval
INTO x_po_header_id
FROM sys.dual;
p_message => 'After select Doc is '||x_po_header_id);
SELECT ph.po_header_id,
ph.pay_on_code,
ph.shipping_control --
INTO x_po_header_id,
x_pay_on_code,
l_shipping_control --
FROM po_headers_all ph, --
po_headers_interface phi
WHERE phi.interface_header_id = x_interface_header_id
AND ph.segment1 = phi.document_num
AND ph.type_lookup_code='BLANKET'
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
SELECT min(interface_line_id)
INTO x_min_interface_line_id
FROM po_lines_interface pli,
po_headers_interface phi
WHERE phi.interface_header_id=pli.interface_header_id
AND phi.interface_header_id = x_interface_header_id;
UPDATE po_headers_interface phi
SET (po_header_id,
last_update_date,
last_updated_by,
document_num,
created_by,
last_update_login,
agent_id,
creation_date,
revision_num,
print_count,
closed_code,
frozen_flag,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
freight_carrier,
fob,
pay_on_code,
freight_terms,
confirming_order_flag,
currency_code,
rate_type_code,
rate_date,
rate,
acceptance_required_flag,
firm_flag,
min_release_amount,
document_subtype,
shipping_control --
) =
(SELECT x_po_header_id,
nvl(phi.last_update_date,sysdate),
nvl(phi.last_updated_by,who.user_id),
nvl(phi.document_num,x_document_num),
nvl(phi.created_by,who.user_id),
nvl(phi.last_update_login,who.login_id),
phi.agent_id ,
nvl(phi.creation_date,sysdate),
decode(g_document_type, 'PO', nvl(phi.revision_num,0),
'PA', nvl(phi.revision_num,0), phi.revision_num),
decode(g_document_type, 'PO', nvl(phi.print_count,0),
'PA', nvl(phi.print_count,0), phi.print_count),
decode(g_document_type, 'PO', nvl(phi.closed_code,'OPEN'),
'PA', nvl(phi.closed_code,'OPEN'), phi.closed_code),
decode(g_document_type, 'PO', nvl(phi.frozen_flag,'N'),
'PA', nvl(phi.frozen_flag,'N'), phi.frozen_flag),
phi.vendor_id,
phi.vendor_site_id,
decode(g_interface_source_code,'SOURCING',
phi.ship_to_location_id,
nvl(phi.ship_to_location_id,
params.ship_to_location_id)),
decode(g_interface_source_code,'SOURCING',
phi.bill_to_location_id,
nvl(phi.bill_To_Location_Id,
params.bill_to_location_id)),
decode(g_interface_source_code,'SOURCING',phi.terms_id,
nvl(phi.terms_id,params.terms_id)),
decode(g_interface_source_code,'SOURCING',
phi.freight_carrier,nvl(phi.freight_carrier,
params.ship_via_lookup_code)),
decode(g_interface_source_code,'SOURCING',phi.fob,
nvl(phi.fob,params.fob_lookup_code)),
decode(g_interface_source_code,'CONSUMPTION_ADVICE',phi.pay_on_code,
x_pay_on_code),
decode(g_interface_source_code,'SOURCING',phi.freight_terms,
nvl(phi.freight_terms,
params.freight_terms_lookup_code)),
decode(g_document_type, 'PO',
nvl(phi.confirming_order_flag,'N'),
'PA',nvl(phi.confirming_order_flag,'N'),
phi.confirming_order_flag),
phi.currency_code,
phi.rate_type_code,
--bug# 2430982
--phi.rate_date,
nvl(phi.rate_date,decode(g_interface_source_code,'SOURCING',decode(phi.rate_type_code,'User',sysdate),phi.rate_date)),
--
phi.rate,
decode(g_document_type, 'PO',
nvl(params.acceptance_required_flag,'N'), /* Bug 7518967 : Default Acceptance Required Check ER: Geting default acceptance_required_flag */
'PA',nvl(params.acceptance_required_flag,'N'),
params.acceptance_required_flag),
decode(g_document_type, 'PO',
nvl(phi.firm_flag,'N'),
'PA',nvl(phi.firm_flag,'N'),
phi.firm_flag),
decode(g_document_type, 'PO',
nvl(phi.min_release_amount,params.min_rel_amount),
'PA',nvl(phi.min_release_amount,params.min_rel_amount),
null),
phi.document_subtype,
l_shipping_control --
FROM po_headers_interface phi2,
po_lines_interface pli
WHERE phi2.interface_header_id = phi.interface_header_id
AND pli.interface_header_id=phi2.interface_header_id
AND pli.interface_line_id = x_min_interface_line_id)
WHERE interface_header_id = x_interface_header_id;
select po_header_id
into x_po_header_id
from po_headers_interface
where interface_header_id = x_interface_header_id;
p_message => 'Setup interface:Before release update '||x_po_header_id);
The subquery(select stmt) was returning multiple rows
while trying to create release using the manual option
if there are more than one line for the same item
in the referenced blanket agreement.
*/
/*Bug 971798
If the blanket agrement has lines which has expired (new feauture in r11)
,then we should not be considering those lines while matching.
*/
-- Added note_to_vendor - iali 08/26/99
/*Bug 1391523 . Added market price to the update statement */
/* Enh : 1660036 - Check the uom convert profile value. If it is set to yes
we do not check if the Req uom is same as BPA uom. We create the release
with the quantity and uom converted to the BPA uom */
/* CONSIGNED FPI : For consumption PO we do not update the interface table with
requisition values */
IF g_interface_source_code <> 'CONSUMPTION_ADVICE' THEN
-- Bug 2707576 - In 115.142, removed the IF statement and ELSE clause
-- for x_uom_convert, since UOM checking is now handled in
-- source_blanket_line.
l_progress:='120';
UPDATE po_lines_interface pli2
SET (
line_num,
item_id,
category_id,
item_description,
unit_of_measure,
list_price_per_unit,
market_price,
base_unit_price, --
unit_price,
quantity,
amount, --
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
-- contract_num, --
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_flag,
min_release_amount,
price_type,
transaction_reason_code,
line_location_id,
need_by_date,
--togeorge 09/27/2000
--added note to receiver
note_to_receiver,
from_header_id,
from_line_id,
receipt_required_flag,
--DWR4{
tax_status_indicator,
note_to_vendor,
--DWR4}
--togeorge 09/27/2000
--added oke columns
oke_contract_header_id,
oke_contract_version_id,
-- start of bug 1548597
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
-- end of bug 1548597
drop_ship_flag, --
vmi_flag -- VMI FPH
)=
(SELECT
nvl(pli.line_num, pol.line_num),
nvl(pli.item_id,prl.item_id),
nvl(pli.category_id,prl.category_id),
nvl(pli.item_description,prl.item_description),
nvl(pli.unit_of_measure,prl.unit_meas_lookup_code),
pli.list_price_per_unit,
pli.market_price,
nvl(pli.base_unit_price,prl.base_unit_price), --
nvl(pli.unit_price,prl.unit_price),
nvl(pli.quantity,prl.quantity),
nvl(pli.amount, prl.amount), --
pli.taxable_flag,
pli.type_1099,
nvl(pli.negotiated_by_preparer_flag,'N'),
nvl(pli.closed_code,'OPEN'),
nvl(pli.item_revision,prl.item_revision),
nvl(pli.un_number_id,prl.un_number_id),
nvl(pli.hazard_class_id,prl.hazard_class_id),
-- pli.contract_num, --
nvl(pli.line_type_id,prl.line_type_id),
nvl(pli.vendor_product_num,prl.suggested_vendor_product_code),
pli.qty_rcv_tolerance,
pli.over_tolerance_error_flag,
nvl(pli.firm_flag,'N'),
nvl(pli.min_release_amount,params.min_rel_amount),
nvl(pli.price_type,params.price_type_lookup_code),
nvl(pli.transaction_reason_code,prl.transaction_reason_code),
pli.line_location_id,
nvl(pli.need_by_date,prl.need_by_date),
--togeorge 09/27/2000
--added note to receiver
nvl(pli.note_to_receiver,prl.note_to_receiver),
pli.from_header_id,
pli.from_line_id,
nvl(pli.receipt_required_flag,plt.receiving_flag),
--DWR4{
prl.tax_status_indicator,
nvl(pli.note_to_vendor, prl.note_to_vendor),
--DWR4}
--togeorge 09/27/2000
--added oke columns
nvl(pli.oke_contract_header_id,prl.oke_contract_header_id),
nvl(pli.oke_contract_version_id,prl.oke_contract_version_id),
-- start of 1548597
nvl(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
nvl(pli.secondary_quantity,prl.secondary_quantity),
nvl(pli.preferred_grade,prl.preferred_grade),
-- end of 1548597
prl.drop_ship_flag, --
prl.vmi_flag -- VMI FPH
FROM po_lines_interface pli,
po_headers_interface phi,
po_requisition_lines_all prl, --
po_line_types plt,
po_lines_all pol --
WHERE pli.interface_line_id = pli2.interface_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = prl.requisition_line_id(+)
AND plt.line_type_id = nvl(prl.line_type_id,pli.line_type_id)
AND pol.po_header_id = x_po_header_id
-- 2082757 : new
AND pol.line_num = po_interface_s.source_blanket_line(
x_po_header_id,
prl.requisition_line_id,
pli.line_num, -- Bug 2707576:
NVL(x_uom_convert,'N'),
g_purchasing_ou_id --
)
)
/* 2082757: Following logic is now coded in new function source_blanket_line
2082757 */
WHERE pli2.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface pli2
SET (
po_header_id,
negotiated_by_preparer_flag,
firm_flag
)=
(SELECT
x_po_header_id,
nvl(pli.negotiated_by_preparer_flag,'N'),
nvl(pli.firm_flag,'N')
FROM po_lines_interface pli,
po_headers_interface phi
WHERE pli.interface_line_id = pli2.interface_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = x_interface_header_id);
p_message => 'Setup interface: After release update');
/*Bug 1391523 . Added market price to the update statement */
l_progress:='150';
UPDATE po_lines_interface pli2
SET (
line_num,
item_id,
job_id, --
category_id,
item_description,
unit_of_measure,
list_price_per_unit,
market_price,
base_unit_price, --
unit_price,
quantity,
amount, --
taxable_flag,
type_1099,
negotiated_by_preparer_flag,
closed_code,
item_revision,
un_number_id,
hazard_class_id,
-- contract_num, --
line_type_id,
vendor_product_num,
qty_rcv_tolerance,
over_tolerance_error_flag,
firm_flag,
min_release_amount,
price_type,
transaction_reason_code,
line_location_id,
need_by_date,
ship_to_organization_id,
note_to_receiver,
from_header_id,
from_line_id,
receipt_required_flag,
tax_status_indicator,
note_to_vendor,
oke_contract_header_id,
oke_contract_version_id,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
drop_ship_flag, --
vmi_flag, -- bug 2738820
supplier_ref_number, --
effective_date, --
expiration_date, --
contractor_first_name, --
contractor_last_name --
)=
(SELECT
pli.line_num,
nvl(pli.item_id,prl.item_id),
nvl(pli.job_id, prl.job_id), --
nvl(pli.category_id,prl.category_id),
nvl(pli.item_description,prl.item_description),
nvl(pli.unit_of_measure,prl.unit_meas_lookup_code),
pli.list_price_per_unit,
pli.market_price,
nvl(pli.base_unit_price,prl.base_unit_price), --
nvl(pli.unit_price,prl.unit_price),
--
, 'FIXED PRICE' , NULL
, 'RATE' , NULL
, nvl(pli.quantity,prl.quantity)
), --
nvl(pli.amount, prl.amount), --
pli.taxable_flag,
pli.type_1099,
nvl(pli.negotiated_by_preparer_flag,'N'),
decode(g_document_type, 'PO',
nvl(pli.closed_code,'OPEN'), null),
nvl(pli.item_revision,prl.item_revision),
nvl(pli.un_number_id,prl.un_number_id),
nvl(pli.hazard_class_id,prl.hazard_class_id),
-- pli.contract_num, --
nvl(pli.line_type_id,prl.line_type_id),
nvl(pli.vendor_product_num,prl.suggested_vendor_product_code),
pli.qty_rcv_tolerance,
pli.over_tolerance_error_flag,
nvl(pli.firm_flag,'N'),
--bug# 2438142 added min_release_amount for PA
decode(g_document_type, 'PO',
nvl(pli.min_release_amount,params.min_rel_amount),
'PA',nvl(pli.min_release_amount,params.min_rel_amount),null),
decode(g_document_type, 'PO',
nvl(pli.price_type,params.price_type_lookup_code),
null),
nvl(pli.transaction_reason_code,prl.transaction_reason_code),
pli.line_location_id,
nvl(pli.need_by_date,prl.need_by_date),
nvl(pli.ship_to_organization_id,prl.destination_organization_id),
nvl(pli.note_to_receiver,prl.note_to_receiver),
pli.from_header_id,
pli.from_line_id,
nvl(pli.receipt_required_flag,plt.receiving_flag),
prl.tax_status_indicator,
nvl(pli.note_to_vendor, prl.note_to_vendor),
-- 2702892 Added the decode for consigned:
decode(pli.consigned_flag,'Y',null,
nvl(pli.oke_contract_header_id,prl.oke_contract_header_id)),
decode(pli.consigned_flag,'Y',null,
nvl(pli.oke_contract_version_id,prl.oke_contract_version_id)),
nvl(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
nvl(pli.secondary_quantity,prl.secondary_quantity),
nvl(pli.preferred_grade,prl.preferred_grade),
prl.drop_ship_flag, --
prl.vmi_flag, -- bug 2738820
prl.supplier_ref_number, --
--
nvl(pli.effective_date, prl.assignment_start_date),
nvl(pli.expiration_date, prl.assignment_end_date),
nvl(pli.contractor_first_name, prl.candidate_first_name),
nvl(pli.contractor_last_name, prl.candidate_last_name)
--
FROM po_lines_interface pli,
po_headers_interface phi,
po_requisition_lines_all prl, --
po_line_types plt
WHERE pli.interface_line_id = pli2.interface_line_id
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = prl.requisition_line_id(+)
AND plt.line_type_id = nvl(prl.line_type_id,pli.line_type_id))
WHERE pli2.interface_header_id = x_interface_header_id;
do the insert. We get the deliver to information
from the distribution record. */
IF (g_document_type in ('RFQ', 'PO')) THEN
l_progress:='170';
p_message => 'Before insert into Distribution interface');
INSERT INTO po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
charge_account_id,
set_of_books_id,
quantity_ordered,
amount_ordered, --
rate,
rate_date,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
destination_type_code,
destination_organization_id,
destination_subinventory,
budget_account_id,
accrual_account_id,
variance_account_id,
--< Shared Proc FPJ Start >
dest_charge_account_id,
dest_variance_account_id,
--< Shared Proc FPJ End >
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
expenditure_type,
project_accounting_context,
destination_context,
expenditure_organization_id,
expenditure_item_date,
--FRKHAN 12/8/98 copy recovery rate and tax amounts
tax_recovery_override_flag, --
recovery_rate,
recoverable_tax,
nonrecoverable_tax,
-- OGM_0.0 change.
award_id,
--togeorge 09/27/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id
)
SELECT pli.interface_header_id,
pli.interface_line_id,
po_distributions_interface_s.nextval,
prd.distribution_num,
prd.code_combination_id,
prd.set_of_books_id,
prd.req_line_quantity,
decode ( g_interface_source_code --
, 'SOURCING' , prd.req_line_amount * pli.amount/prl.amount
, prd.req_line_amount
),
phi.rate,
phi.rate_date,
prd.distribution_id,
prl.deliver_to_location_id,
prl.to_person_id,
prd.encumbered_flag,
prd.gl_encumbered_date,
prd.gl_encumbered_period_name,
prl.destination_type_code,
prl.destination_organization_id,
prl.destination_subinventory,
prd.budget_account_id,
prd.accrual_account_id,
prd.variance_account_id,
--< Shared Proc FPJ Start >
-- For non SPS case (common case), set Destination Accounts to NULL
NULL, -- dest_charge_account_id
NULL, -- dest_variance_account_id
--< Shared Proc FPJ End >
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.bom_resource_id,
prd.prevent_encumbrance_flag,
prd.project_id,
prd.task_id,
prd.end_item_unit_number,
prd.expenditure_type,
prd.project_accounting_context,
prl.destination_context,
prd.expenditure_organization_id,
prd.expenditure_item_date,
prd.tax_recovery_override_flag, --
prd.recovery_rate,
prd.recoverable_tax,
prd.nonrecoverable_tax,
prd.award_id, -- OGM_0.0 change
--togeorge 09/27/2000
--added oke columns
-- 2702892 Added the decode for consigned:
decode(pli.consigned_flag,'Y',null,
prd.oke_contract_line_id),
decode(pli.consigned_flag,'Y',null,
prd.oke_contract_deliverable_id)
FROM po_requisition_lines_all prl, --
po_req_distributions_all prd, --
po_lines_interface pli,
po_headers_interface phi
WHERE prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_line_id = pli.requisition_line_id -- Bug:1563888
AND pli.interface_header_id = phi.interface_header_id
AND phi.interface_header_id = x_interface_header_id;
SELECT count(*)
INTO x_count_dist
FROM po_distributions_interface
WHERE interface_header_id = x_interface_header_id;
SELECT hrl.inventory_organization_id
INTO l_ship_to_org_id
FROM hr_locations_all hrl
WHERE hrl.location_id = i.ship_to_location_id
AND hrl.ship_to_site_flag = 'Y';
INSERT INTO po_distributions_interface(
interface_header_id
, interface_line_id
, interface_distribution_id
, destination_type_code
, deliver_to_location_id
, destination_organization_id
) VALUES (
i.interface_header_id
, i.interface_line_id
, PO_DISTRIBUTIONS_INTERFACE_S.nextval
, 'EXPENSE'
, i.ship_to_location_id
, l_ship_to_org_id
);
PO_LOG.stmt(g_log_head || l_api_name, 190, 'Num rows inserted', SQL%ROWCOUNT);
update po_lines_interface
set vendor_product_num = x_vendor_product_num
where rowid = x_rowid;
DELETE po_distributions_interface
WHERE interface_header_id = x_interface_header_id;
DELETE po_price_diff_interface
WHERE interface_header_id = x_interface_header_id;
DELETE po_line_locations_interface
WHERE interface_header_id = x_interface_header_id;
DELETE po_lines_interface
WHERE interface_header_id = x_interface_header_id;
DELETE po_headers_interface
WHERE interface_header_id = x_interface_header_id;
** the user selects.
** DEBUG. For now from the front end the users will not be able to
** determine the order in which they want to lines to be placed.
** removed order by interface_line_id and replaced it with the
** above order by.
*/
/** bgu, Mar. 19, 1999
* BUG 853749
* For one time item, item description will distinguish items.
*/
/* Bug 1949160. Created a cursor to retrieve requisition line-id */
CURSOR interface_lines_temp IS
SELECT pli.requisition_line_id
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
-- bug 4000047: start: requisition lines should be entered
-- into PO the same order they appear in the requisition
ORDER BY pli.requisition_line_id;
SELECT pli.action,
pli.requisition_line_id,
pli.line_num,
pli.item_id,
pli.item_description, -- bgu, Mar. 19, 1999
pli.line_type_id,
pli.item_revision,
pli.unit_of_measure,
pli.transaction_reason_code,
pli.need_by_date,
pli.note_to_receiver,
pli.oke_contract_header_id,
pli.oke_contract_version_id,
pli.vendor_product_num, -- Bug# 1763933
pld.deliver_to_location_id,
pld.destination_organization_id,
pli.secondary_unit_of_measure,
pli.preferred_grade,
pli.bid_number,
pli.bid_line_number,
pli.rowid,
pli.vmi_flag, -- VMI FPH
pli.drop_ship_flag, --
pli.from_header_id, -- FPI GA
pli.from_line_id, -- FPI GA
pli.consigned_flag, -- CONSIGNED FPI
pli.contract_id, --
pli.supplier_ref_number --
FROM po_lines_interface pli,
po_distributions_interface pld
WHERE pli.interface_header_id=x_interface_header_id
AND pli.interface_line_id=pld.interface_line_id
AND pld.interface_distribution_id =
( SELECT min(pdi2.interface_distribution_id)
FROM po_distributions_interface pdi2
WHERE pdi2.interface_line_id = pli.interface_line_id)
ORDER BY pli.item_id,
pli.item_description,
pli.unit_price,
pli.need_by_date,
pli.requisition_line_id;
SELECT phi.document_num,
phi.document_type_code,
phi.document_subtype,
phi.release_num
INTO x_document_num,
x_document_type_code,
x_document_subtype,
x_release_num
FROM po_headers_interface phi
WHERE phi.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface pli
SET pli.shipment_num = 1,
pli.line_num =
(
SELECT prl.line_num
FROM po_requisition_lines_all prl -- Bug 3903445
WHERE prl.requisition_line_id = pli.requisition_line_id
)
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num is null
AND pli.shipment_num is null;
UPDATE po_lines_interface pli
SET pli.line_num = x_count,
pli.shipment_num = 1
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NULL
AND pli.shipment_num IS NULL;
SELECT MIN(pli.line_num),
pli.po_line_id
INTO x_po_line_num,
x_po_line_id
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NOT NULL
AND pli.line_type_id = x_line_type_id
AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
AND NVL(pli.item_description, 'null' ) =
NVL(x_item_description,'null')
AND (((pli.item_revision IS NULL) AND (x_item_revision IS NULL))
OR (pli.item_revision = x_item_revision))
AND pli.unit_of_measure = x_unit_meas_lookup_code
AND ( pli.transaction_reason_code IS NULL
OR pli.transaction_reason_code =
NVL(x_transaction_reason_code,
pli.transaction_reason_code))
-- togeorge 09/27/2000
-- added conditions to compare oke contract num and rev.
-- line num is different if contract info is diff. on the
-- same item.
AND NVL(pli.oke_contract_header_id,-1) =
NVL(x_oke_contract_header_id,-1)
AND NVL(pli.oke_contract_version_id,-1) =
NVL(x_oke_contract_version_id,-1)
GROUP BY pli.po_line_id;
UPDATE po_lines_interface pli
SET pli.line_num = x_po_line_num
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id;
UPDATE po_lines_interface pli
SET pli.shipment_num =
( SELECT prl.line_num
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = pli.requisition_line_id )
WHERE pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL;
UPDATE po_lines_interface pli
SET pli.shipment_num = x_count
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL;
SELECT NVL(max(pl.line_num),0)
INTO x_line_num
FROM po_headers_all ph,
po_lines_all pl
WHERE pl.po_header_id = ph.po_header_id
AND ph.segment1 = x_document_num
AND ph.type_lookup_code =
DECODE(g_document_type, 'RFQ', g_document_type, x_document_subtype)
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99);
UPDATE po_lines_interface pli
SET pli.line_num = x_line_num + x_count,
pli.shipment_num = 1
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NULL
AND pli.shipment_num IS NULL;
SELECT MIN(pli.line_num),
pli.po_line_id
INTO x_po_line_num,
x_po_line_id
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NOT NULL
AND pli.line_type_id = x_line_type_id
AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
AND NVL(pli.item_description, 'null' ) =
NVL(x_item_description,'null')
AND (((pli.item_revision IS NULL) AND (x_item_revision IS NULL))
OR (pli.item_revision = x_item_revision))
AND pli.unit_of_measure = x_unit_meas_lookup_code
AND ( pli.transaction_reason_code IS NULL
OR pli.transaction_reason_code =
NVL(x_transaction_reason_code,
pli.transaction_reason_code))
-- togeorge 09/27/2000
-- added conditions to compare oke contract num and rev.
-- line num is different if contract info is diff. on the
-- same item.
AND NVL(pli.oke_contract_header_id,-1) =
NVL(x_oke_contract_header_id,-1)
AND NVL(pli.oke_contract_version_id,-1) =
NVL(x_oke_contract_version_id,-1)
GROUP BY pli.po_line_id;
UPDATE po_lines_interface pli
SET pli.line_num = x_po_line_num
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id;
p_message => 'Group_interface_lines: before select max ship num');
SELECT nvl(max(poll.shipment_num),0)
INTO x_shipment_num
FROM po_headers_all ph,
po_line_locations_all poll,
po_releases_all pr
WHERE ph.po_header_id = poll.po_header_id
AND ph.segment1 = x_document_num
AND pr.po_header_id = ph.po_header_id
AND pr.release_num = x_release_num
AND ph.type_lookup_code = 'BLANKET'
AND poll.po_release_id = pr.po_release_id
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(pr.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
p_message => 'Group_interface_lines: after select max ship num');
UPDATE po_lines_interface pli
SET pli.shipment_num = x_shipment_num + x_count
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NOT NULL
AND pli.shipment_num IS NULL;
SELECT NVL(max(pli.line_num), 0)
INTO l_max_iface_line_num
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface pli
SET pli.line_num = l_max_iface_line_num + 1
WHERE pli.rowid = x_row_id
AND pli.line_num IS NULL;
update_shipment(
x_interface_header_id,
x_po_shipment_num,
x_po_line_num,
x_requisition_line_id,
x_po_line_id,
x_document_num,
x_release_num,
x_create_new_line); -- FPI GA
SELECT pol.po_line_id,
pol.line_num
INTO x_po_line_id,
x_po_line_num
FROM po_lines_all pol,
po_headers_all poh
WHERE poh.segment1 = x_document_num
AND pol.line_num = x_interface_line_num
AND poh.type_lookup_code =
DECODE(g_document_type, 'RFQ', g_document_type,x_document_subtype)
AND poh.po_header_id = pol.po_header_id
AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
update_shipment(
x_interface_header_id,
x_po_shipment_num,
x_po_line_num,
x_requisition_line_id,
x_po_line_id,
x_document_num,
x_release_num,
x_create_new_line); -- FPI GA
SELECT grade_control_flag
INTO l_grade_control_flag
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
and organization_id = params.inventory_organization_id;
SELECT line_num
, po_line_id
INTO x_po_line_num
, x_po_line_id
FROM po_lines_all POL2
, po_headers_all POH
, po_line_types_b PLT --
WHERE POH.segment1 = x_document_num
AND POH.po_header_id = POL2.po_header_id
AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND POH.type_lookup_code = x_document_subtype
-- Any new Service line types should
-- cause the SELECT to fail (i.e. should not be matched).
--
AND POL2.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code NOT IN ('RATE','FIXED PRICE')
--
--
AND pol2.line_num =
(SELECT /*+ NO_UNNEST */ MIN(line_num)
FROM po_lines_all pol --
WHERE pol.po_header_id = poh.po_header_id
AND NVL(CANCEL_FLAG,'N') = 'N'
AND LINE_TYPE_ID = x_line_type_id
AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1) -- bgu, For one time item
AND nvl(pol.ITEM_DESCRIPTION,'null') = nvl(x_item_description,'null')
AND
( ( ITEM_REVISION IS NULL
AND x_item_revision IS NULL
)
OR ITEM_REVISION = x_item_revision
)
AND UNIT_MEAS_LOOKUP_CODE =
x_unit_meas_lookup_code
--
-- replace x_preferred_grade to l_line_grade and removed secondary unit comparison.
AND
(
( POL.PREFERRED_GRADE IS NULL
AND l_line_grade IS NULL
) OR
( POL.PREFERRED_GRADE =
l_line_grade
)
)
--
AND /* FPI GA start */
(
( pol.from_header_id IS NULL
AND x_source_doc_id IS NULL
) OR
( pol.from_header_id =
x_source_doc_id
)
)
AND
(
( pol.from_line_id IS NULL
AND x_source_doc_line_id IS NULL
) OR
( pol.from_line_id =
x_source_doc_line_id
)
) /* FPI GA end */
AND (TRANSACTION_REASON_CODE IS NULL
OR TRANSACTION_REASON_CODE =
NVL(x_transaction_reason_code,
TRANSACTION_REASON_CODE))
AND trunc(nvl(pol.expiration_date,sysdate+1)) >= trunc(sysdate)
AND nvl(pol.oke_contract_header_id,-1)=nvl(x_oke_contract_header_id,-1)
AND nvl(pol.oke_contract_version_id,-1)=nvl(x_oke_contract_version_id,-1)
AND nvl(pol.vendor_product_num,-1)=nvl(x_vendor_product_num,-1)
AND nvl(pol.bid_number,-1)=nvl(x_bid_number,-1)
AND nvl(pol.bid_line_number,-1)=nvl(x_bid_line_number,-1)
--
AND
(
( pol.contract_id IS NULL AND
l_contract_id IS NULL )
OR
( pol.contract_id = l_contract_id )
)
--
--
AND ((pol.supplier_ref_number IS NULL
AND l_supplier_ref_number IS NULL)
OR (pol.supplier_ref_number = l_supplier_ref_number))
--
)
FOR UPDATE OF QUANTITY;
SELECT line_num,
po_line_id
INTO x_po_line_num,
x_po_line_id
FROM po_lines_all POL2,
po_headers_all POH
WHERE POH.segment1 = x_document_num
AND POH.po_header_id = POL2.po_header_id
AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND POH.type_lookup_code = 'RFQ'
AND pol2.line_num =
(
SELECT /*+ NO_UNNEST */ MIN(line_num)
FROM PO_LINES_ALL pol
WHERE pol.po_header_id = poh.po_header_id
AND LINE_TYPE_ID = x_line_type_id
AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1)
AND nvl(pol.ITEM_DESCRIPTION,'null') =
nvl(x_item_description,'null')
AND
( ( ITEM_REVISION IS NULL
AND x_item_revision IS NULL
)
OR ITEM_REVISION = x_item_revision
)
AND nvl(pol.oke_contract_header_id,-1) =
nvl(x_oke_contract_header_id,-1)
AND nvl(pol.oke_contract_version_id,-1) =
nvl(x_oke_contract_version_id,-1)
)
FOR UPDATE OF QUANTITY;
SELECT min(pli.line_num)
INTO x_po_line_num
FROM po_lines_interface pli
, po_requisition_lines_all prl
, po_line_types_b PLT --
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num is not null
AND prl.requisition_line_id <> x_requisition_line_id
AND prl.requisition_line_id = pli.requisition_line_id
AND pli.line_type_id = x_line_type_id
-- Any new Service line types should
-- cause the SELECT to fail (i.e. should not be matched).
--
AND PLI.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code NOT IN ('RATE','FIXED PRICE')
--
--
AND nvl(pli.ITEM_ID, -1) = nvl(x_item_id, -1)
AND nvl(pli.ITEM_DESCRIPTION,'null') =
nvl(x_item_description,'null')
AND ((pli.ITEM_REVISION IS NULL AND x_item_revision IS NULL)
OR pli.ITEM_REVISION = x_item_revision)
AND pli.UNIT_OF_MEASURE = x_unit_meas_lookup_code
--
-- replace x_preferred_grade to l_line_grade and
-- removed secondary unit comparison.
AND (( pli.PREFERRED_GRADE IS NULL AND l_line_grade IS NULL)
OR (pli.PREFERRED_GRADE = l_line_grade))
--
-- FPI GA start
AND (
( pli.from_header_id IS NULL
AND x_source_doc_id IS NULL
) OR
( pli.from_header_id =
x_source_doc_id
)
)
AND
(
( pli.from_line_id IS NULL
AND x_source_doc_line_id IS NULL
) OR
( pli.from_line_id =
x_source_doc_line_id
)
)
AND( nvl(l_needby_prf,'Y') = 'N' -- Bug 3201308
OR
(
( pli.need_by_date IS NULL
AND x_need_by_date IS NULL
) OR
( to_char(pli.need_by_date-(to_number(substr(to_char(pli.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
)
)
AND ( nvl(l_shipto_prf,'Y') = 'N' -- Bug 3201308
OR exists (select 'x'
from HR_LOCATIONS HRL
where PRL.deliver_to_location_id = HRL.location_id
and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
UNION ALL
select 'x'
from HZ_LOCATIONS HZ
where PRL.deliver_to_location_id = HZ.location_id
and HZ.location_id = x_ship_to_location_id)
)
AND( nvl(l_shipto_prf,'Y') = 'N' -- Bug 3201308
OR
(
( pli.ship_to_organization_id IS NULL
AND x_destination_org_id IS NULL
) OR
( pli.ship_to_organization_id =
x_destination_org_id
)
) )
-- FPI GA end
-- CONSIGNED FPI start
AND (
( pli.consigned_flag IS NULL
AND x_consigned_flag IS NULL
) OR
( pli.consigned_flag =
x_consigned_flag
)
)
-- CONSIGNED FPI End
AND (pli.TRANSACTION_REASON_CODE IS NULL
OR pli.TRANSACTION_REASON_CODE =
NVL(x_transaction_reason_code,
pli.TRANSACTION_REASON_CODE))
AND nvl(pli.oke_contract_header_id,-1) =
nvl(x_oke_contract_header_id,-1)
AND nvl(pli.oke_contract_version_id,-1) =
nvl(x_oke_contract_version_id,-1)
AND nvl(pli.vendor_product_num,-1) =
nvl(x_vendor_product_num,-1)
AND nvl(pli.bid_number,-1) = nvl(x_bid_number,-1)
AND nvl(pli.bid_line_number,-1) = nvl(x_bid_line_number,-1)
AND nvl(pli.orig_from_req_flag,'Y') <> 'N'
--
AND
(
( pli.contract_id IS NULL AND
l_contract_id IS NULL )
OR
( pli.contract_id = l_contract_id )
)
--
--
AND ((pli.supplier_ref_number IS NULL
AND l_supplier_ref_number IS NULL)
OR (pli.supplier_ref_number = l_supplier_ref_number))
--
;
p_message => 'Before select min line_num');
SELECT MIN(pli.line_num)
INTO x_po_line_num
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num is not null
AND pli.line_type_id = x_line_type_id
AND nvl(pli.item_id, -1) = nvl(x_item_id, -1)
AND nvl(pli.item_description,'null') =
nvl(x_item_description,'null')
AND ((pli.item_revision IS NULL AND x_item_revision IS NULL)
OR (pli.item_revision = x_item_revision))
AND nvl(pli.oke_contract_header_id,-1) =
nvl(x_oke_contract_header_id,-1)
AND nvl(pli.oke_contract_version_id,-1) =
nvl(x_oke_contract_version_id,-1)
;
UPDATE po_lines_interface pli
SET pli.line_num = x_po_line_num
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id;
p_message => 'Before update_shipment');
update_shipment(
x_interface_header_id,
x_po_shipment_num,
x_po_line_num,
x_requisition_line_id,
x_po_line_id,
x_document_num,
x_release_num,
x_create_new_line); -- FPI GA
UPDATE po_lines_interface pli2
SET (pli2.line_num, pli2.shipment_num) =
(
SELECT (NVL(max(pli.line_num), 0) + 1), 1
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
)
WHERE pli2.rowid = x_row_id;
SELECT NVL(max(pl.line_num), 0)
INTO x_line_num
FROM po_headers_all ph,
po_lines_all pl
WHERE pl.po_header_id = ph.po_header_id
AND ph.segment1 = x_document_num
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)
AND ph.type_lookup_code =
DECODE(g_document_type, 'RFQ', g_document_type, x_document_subtype)
;
p_message => 'Before select max line_num from po_lines_interface');
SELECT NVL(max(pli.line_num), 0)
INTO x_int_line_num
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface pli
SET pli.line_num = x_line_num + 1,
pli.shipment_num = 1
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id;
UPDATE po_lines_interface pli
SET pli.line_num = x_line_num + 1,
pli.shipment_num = 1
WHERE pli.rowid = x_row_id;
SELECT min(line_num)
, po_line_id
INTO x_po_line_num
, x_po_line_id
FROM po_lines_interface PLI
, po_line_types_b PLT --
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num is not null
AND pli.LINE_TYPE_ID = x_line_type_id
-- Any new Service line types should
-- cause the SELECT to fail (i.e. should not be matched).
--
AND PLI.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code NOT IN ('RATE','FIXED PRICE')
--
--
AND nvl(pli.ITEM_ID, -1) = nvl(x_item_id, -1)
AND nvl(pli.ITEM_DESCRIPTION,'null') =
nvl(x_item_description,'null')
AND ( ( pli.ITEM_REVISION IS NULL
AND x_item_revision IS NULL
)
OR pli.ITEM_REVISION = x_item_revision
)
AND pli.UNIT_OF_MEASURE = x_unit_meas_lookup_code
AND (pli.TRANSACTION_REASON_CODE IS NULL
OR pli.TRANSACTION_REASON_CODE =
NVL(x_transaction_reason_code,
pli.TRANSACTION_REASON_CODE))
AND nvl(pli.oke_contract_header_id,-1)=nvl(x_oke_contract_header_id,-1)
AND nvl(pli.oke_contract_version_id,-1)=nvl(x_oke_contract_version_id,-1)
GROUP BY po_line_id;
UPDATE po_lines_interface pli
SET pli.line_num = x_po_line_num
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id;
p_message => 'After update of po_lines_interface line_num');
SELECT pol.po_line_id
INTO x_po_line_id
FROM po_lines_all pol,
po_headers_all poh,
po_lines_interface pli
WHERE pol.po_header_id = poh.po_header_id
AND poh.segment1 = x_document_num
AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99)
AND poh.type_lookup_code = 'BLANKET' -- Bug# 1746943
AND pol.line_num = pli.line_num
AND pli.requisition_line_id = x_requisition_line_id;
update_shipment(
x_interface_header_id,
x_po_shipment_num,
x_po_line_num,
x_requisition_line_id,
x_po_line_id,
x_document_num,
x_release_num,
x_create_new_line, -- FPI GA
x_row_id );
SELECT 'Y'
INTO l_flag
FROM fnd_attached_documents
WHERE entity_name = 'REQ_LINES'
AND pk1_value = to_char(p_req_line_id)
AND pk2_value = 'ONE_TIME_LOCATION'
AND rownum = 1;
/* Consigned FPI start : split the following select to determine if a new line
is to be created or just a new shipment */
-- Bug 3201308 : Further split the select into 3 selects to chenck
-- matching for need by date ,ship to and rest of the information
-- Bug 2757524 Do not execute this select if x_po_line_id is null
IF x_po_line_id is not null THEN
l_progress:='040';
SELECT PLL.shipment_num
,PLL.line_location_id
INTO x_po_shipment_num
,x_line_location_to_check
FROM PO_LINE_LOCATIONS_ALL PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
-- bug 4599140 (included the following OR condition so that the SQL works correctly
-- for null need_by_date)
AND (( to_char(PLL.need_by_date-(to_number(substr(to_char(PLL.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLL.need_by_date is NULL AND x_need_by_date is NULL)
)
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
SELECT PLL.shipment_num
,PLL.line_location_id
INTO x_po_shipment_num
,x_line_location_to_check
FROM PO_LINE_LOCATIONS_ALL PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND PLL.SHIP_TO_LOCATION_ID = x_ship_to_location_id
AND PLL.SHIP_TO_ORGANIZATION_ID =
x_destination_org_id
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
SELECT PLL.shipment_num
,PLL.line_location_id
INTO x_po_shipment_num
,x_line_location_to_check
FROM PO_LINE_LOCATIONS_ALL PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND nvl(PLL.drop_ship_flag, 'N') <> 'Y' -- cannot add to Drop Ship Shipments
AND nvl(PLL.CONSIGNED_FLAG,'N') = nvl(x_consigned_flag,'N')
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
SELECT PLL.shipment_num
INTO x_po_shipment_num
FROM PO_LINE_LOCATIONS_ALL PLL, --
PO_REQUISITION_LINES_ALL PRL, --
PO_SYSTEM_PARAMETERS_ALL PSP --
WHERE PLL.LINE_LOCATION_ID = x_line_location_to_check
AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
AND rtrim(nvl(PLL.note_to_receiver,'99')) = rtrim(nvl(x_note_to_receiver,'99'))
AND PLL.SHIPMENT_TYPE in ('STANDARD', 'SCHEDULED',
'BLANKET')
AND NVL(PLL.ENCUMBERED_FLAG,'N') = 'N'
AND NVL(PLL.CANCEL_FLAG,'N') = 'N'
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99)
AND PLL.ACCRUE_ON_RECEIPT_FLAG =
decode(interface.transaction_flow_header_id, NULL, --
decode(prl.destination_type_code,'EXPENSE',
decode(psp.expense_accrual_code,'PERIOD END','N',
decode(nvl(item.receipt_required_flag,
nvl(interface.receipt_required_flag,
nvl(vendor.receipt_required_flag,
nvl(params.receiving_flag,'N')))),
'N','N','Y')),'Y'), 'Y') --
-- start of 1548597
AND
(
( PLL.PREFERRED_GRADE IS NULL AND x_preferred_grade IS NULL )
OR
( PLL.PREFERRED_GRADE = x_preferred_grade )
)
-- end of 1548597
AND NVL(PLL.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
SELECT PLL.shipment_num
INTO x_po_shipment_num
FROM PO_LINE_LOCATIONS_ALL PLL, --
PO_REQUISITION_LINES_ALL PRL, --
PO_SYSTEM_PARAMETERS_ALL PSP --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
--Bug4599140 (included the following OR condition so that the SQL works correctly
--for null need by date)
AND ( ( to_char(PLL.need_by_date-(to_number(substr(to_char(PLL.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLL.need_by_date is NULL AND x_need_by_date is NULL)
)
AND rtrim(nvl(PLL.note_to_receiver,'99')) = rtrim(nvl(x_note_to_receiver,'99'))
AND PLL.SHIP_TO_LOCATION_ID = x_ship_to_location_id
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
SELECT por.po_release_id
INTO g_po_release_id
FROM po_releases_all por, --
po_headers_interface phi
WHERE phi.interface_header_id = x_interface_header_id
AND phi.release_num = por.release_num
AND phi.po_header_id = por.po_header_id
AND NVL(por.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
FOR UPDATE OF por.approved_flag;
SELECT POL.po_line_id
INTO l_po_line_id
FROM po_headers_interface PHI,
po_lines_all POL
WHERE PHI.interface_header_id = x_interface_header_id
AND PHI.po_header_id = POL.po_header_id
AND POL.line_num = x_po_line_num;
SELECT PLL.shipment_num
,PLL.line_location_id
INTO x_po_shipment_num,
x_line_location_to_check
FROM PO_LINE_LOCATIONS_ALL PLL, --
PO_LINES_ALL POL, --
PO_LINE_TYPES PLT --
WHERE POL.PO_LINE_ID = l_po_line_id -- bug2788115
AND POL.po_line_id = PLL.po_line_id --
AND POL.line_type_id = PLT.line_type_id --
AND PLT.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')--
AND PLL.po_release_id = g_po_release_id
--Bug 4599140 (included the following OR condition so that the SQL works correctly
--for null need by date)
AND ( ( to_char(PLL.need_by_date-(to_number(substr(to_char(PLL.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLL.need_by_date is NULL AND x_need_by_date is NULL)
)
AND nvl(PLL.drop_ship_flag, 'N') <> 'Y' -- cannot add to Drop Ship Shipments
--togeorge 09/27/2000
--added note to receiver
--AND rtrim(PLL.note_to_receiver) = rtrim(x_note_to_receiver)
--Bug# 1867976,togeorge, 07/06/2001
--added nvl
AND rtrim(nvl(PLL.note_to_receiver,'99')) = rtrim(nvl(x_note_to_receiver,'99'))
AND PLL.SHIP_TO_LOCATION_ID = x_ship_to_location_id
AND PLL.SHIP_TO_ORGANIZATION_ID =
x_destination_org_id
AND PLL.SHIPMENT_TYPE in ('STANDARD', 'SCHEDULED',
'BLANKET')
AND NVL(PLL.ENCUMBERED_FLAG,'N') = 'N'
AND NVL(PLL.CANCEL_FLAG,'N') = 'N'
-- start of 1548597
AND
(
( PLL.PREFERRED_GRADE IS NULL AND x_preferred_grade IS NULL )
OR
( PLL.PREFERRED_GRADE = x_preferred_grade )
)
-- end of 1548597
AND NVL(PLL.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI FPH
AND nvl(PLL.CONSIGNED_FLAG,'N') = nvl(x_consigned_flag,'N') -- CONSIGNED FPI
AND ROWNUM = 1
FOR UPDATE OF PLL.QUANTITY;
** will be inserted for the shipment.
*/
l_progress:='130';
SELECT DECODE(PRL.destination_type_code,
'EXPENSE',
decode(nvl(msi.receipt_required_flag,
nvl(plt.receiving_flag,
nvl(pov.receipt_required_flag,
nvl(psp.receiving_flag, 'N')))) ,'N','N',
decode(psp.expense_accrual_code,'PERIOD END', 'N', 'Y')),
'INVENTORY', 'Y',
'SHOP FLOOR','Y')
INTO x_receipt_required_flag
FROM po_lines_interface pli,
po_headers_interface phi,
po_requisition_lines_all prl, --
mtl_system_items msi,
po_line_types plt,
po_vendors pov,
po_system_parameters_all psp, --
financials_system_params_all fsp --
WHERE pli.item_id = msi.inventory_item_id(+)
AND nvl(msi.organization_id,fsp.inventory_organization_id)=
fsp.inventory_organization_id
AND pli.line_type_id = plt.line_type_id
AND PLT.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')--
AND phi.vendor_id = pov.vendor_id(+)
AND phi.interface_header_id =
pli.interface_header_id
AND pli.interface_header_id =
phi.interface_header_id
AND prl.requisition_line_id = pli.requisition_line_id
AND pli.requisition_line_id =
x_requisition_line_id
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
** See if a record that has just been inserted into the
** interface table matches the shipment you are trying to create.
*/
/* Bug # 2224446, Added outer join on mtl_system_items */
/* Bug: 2348161.Changed the below SQL and removed the reference to the tables
HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
clause. Instead added a subquery to check for the location_id
to improve the performance
*/
/* Bug 2466578. Changed the UNION to UNION ALL in the sub query to improve the
performance.
*/
SELECT PLI.shipment_num
,PLI.requisition_line_id
INTO x_po_shipment_num
,x_req_line_to_check
FROM PO_LINES_INTERFACE PLI,
PO_REQUISITION_LINES_ALL PRL, --
--bug 1942696 hr_location changes to reflect the new view
MTL_SYSTEM_ITEMS MSI ,
PO_LINE_TYPES PLT ,
PO_SYSTEM_PARAMETERS_ALL PSP , --
FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
PO_VENDORS POV,
PO_HEADERS_INTERFACE PHI
WHERE PLI.LINE_NUM = x_po_line_num
AND PLI.shipment_num is not null
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND PLI.item_id = MSI.inventory_item_id(+)
AND nvl(MSI.organization_id,FSP.inventory_organization_id)=
FSP.inventory_organization_id
AND PLI.line_type_id = PLT.line_type_id
AND PHI.vendor_id = POV.vendor_id (+)
AND PLI.interface_header_id =
PHI.interface_header_id
AND PRL.REQUISITION_LINE_ID <>
x_requisition_line_id
AND PRL.requisition_line_id = PLI.requisition_line_id
--Bug 4599140 (included the following OR condition so that the SQL works correctly
--for null need by date)
AND ( ( to_char(PLI.need_by_date-(to_number(substr(to_char(PLI.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLI.need_by_date is NULL AND x_need_by_date is NULL)
)
AND nvl(PLI.drop_ship_flag, 'N') <> 'Y' -- cannot add to Drop Ship Shipments
--togeorge 09/27/2000
--added note to receiver
--AND rtrim(PLI.note_to_receiver) = rtrim(x_note_to_receiver)
--Bug# 1867976,togeorge, 07/06/2001
--added nvl
AND rtrim(nvl(PLI.note_to_receiver,'99')) = rtrim(nvl(x_note_to_receiver,'99'))
--bug 1942696 hr_location changes to reflect the new view
AND exists (select 'x'
from HR_LOCATIONS HRL
where PRL.deliver_to_location_id = HRL.location_id
and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
UNION ALL
select 'x'
from HZ_LOCATIONS HZ
where PRL.deliver_to_location_id = HZ.location_id
and HZ.location_id = x_ship_to_location_id)
AND PRL.destination_organization_id = x_destination_org_id
AND DECODE(PRL.destination_type_code,
'EXPENSE',
decode(nvl(msi.receipt_required_flag,
nvl(plt.receiving_flag,
nvl(pov.receipt_required_flag,
nvl(psp.receiving_flag,'N')))),'N','N',
decode(psp.expense_accrual_code, 'PERIOD END', 'N', 'Y')),
'INVENTORY', 'Y',
'SHOP FLOOR', 'Y')
= x_receipt_required_flag
-- start of 1548597
AND
(
( PLI.PREFERRED_GRADE IS NULL AND x_preferred_grade IS NULL )
OR
( PLI.PREFERRED_GRADE = x_preferred_grade )
)
-- end of 1548597
AND NVL(PLI.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI FPH
AND nvl(PLI.CONSIGNED_FLAG,'N') = nvl(x_consigned_flag,'N') --CONSIGNED FPI
AND ROWNUM = 1;
** See if a record that has just been inserted into the
** interface table matches the shipment you are trying to create.
*/
/* Bug: 2348161.Changed the below SQL and removed the reference to the tables
HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
clause. Instead added a subquery to check for the location_id
to improve the performance
*/
SELECT PLI.shipment_num
INTO x_po_shipment_num
FROM PO_LINES_INTERFACE PLI,
PO_REQUISITION_LINES_ALL PRL, --
MTL_SYSTEM_ITEMS MSI ,
PO_LINE_TYPES PLT ,
PO_SYSTEM_PARAMETERS_ALL PSP , --
FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
PO_VENDORS POV,
PO_HEADERS_INTERFACE PHI
WHERE PLI.LINE_NUM = x_po_line_num
AND PLI.shipment_num is not null
AND PLI.item_id = MSI.inventory_item_id
AND MSI.organization_id=
FSP.inventory_organization_id
AND PLI.line_type_id = PLT.line_type_id
AND PHI.vendor_id = POV.vendor_id (+)
AND PLI.interface_header_id =
PHI.interface_header_id
AND PRL.REQUISITION_LINE_ID =
x_requisition_line_id
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
--Bug 4599140 (included the following OR condition so that the SQL works correctly
--for null need by date)
AND ( ( to_char(PLI.need_by_date-(to_number(substr(to_char(PLI.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLI.need_by_date is NULL AND x_need_by_date is NULL)
)
AND nvl(PLI.drop_ship_flag, 'N') <> 'Y' -- cannot add to Drop Ship Shipments
--togeorge 09/27/2000
--added note to receiver
--AND rtrim(PLI.note_to_receiver) = rtrim(x_note_to_receiver)
--Bug# 1867976,togeorge, 07/06/2001
--added nvl
AND rtrim(nvl(PLI.note_to_receiver,'99')) = rtrim(nvl(x_note_to_receiver,'99'))
--bug 1942696 hr_location changes to reflect the new view
AND exists (select 'x'
from HR_LOCATIONS HRL
where PRL.deliver_to_location_id = HRL.location_id
and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
UNION ALL
select 'x'
from HZ_LOCATIONS HZ
where PRL.deliver_to_location_id = HZ.location_id
and HZ.location_id = x_ship_to_location_id)
-- start of 1548597
AND
(
( PLI.PREFERRED_GRADE IS NULL AND x_preferred_grade IS NULL )
OR
( PLI.PREFERRED_GRADE = x_preferred_grade )
)
-- end of 1548597
AND ROWNUM = 1
ORDER BY shipment_num;
** See if a record that has just been inserted into the
** interface table matches the shipment you are trying to create.
*/
begin
l_progress:='180';
SELECT document_subtype
into x_check_doc_sub_type
from
PO_HEADERS_INTERFACE
WHERE
INTERFACE_HEADER_ID=x_interface_header_id;
SELECT PLI.shipment_num
INTO x_po_shipment_num
FROM PO_LINES_INTERFACE PLI,
PO_REQUISITION_LINES_ALL PRL, --
--bug 1942696 hr_location changes to reflect the new view
MTL_SYSTEM_ITEMS MSI ,
PO_LINE_TYPES PLT ,
PO_SYSTEM_PARAMETERS_ALL PSP , --
FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
PO_VENDORS POV,
PO_HEADERS_INTERFACE PHI
WHERE PLI.LINE_NUM = x_po_line_num
AND PLI.shipment_num is not null
AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND PLI.item_id = MSI.inventory_item_id(+)
AND nvl(MSI.organization_id,FSP.inventory_organization_id) =
FSP.inventory_organization_id
AND PLI.line_type_id = PLT.line_type_id
AND PHI.vendor_id = POV.vendor_id (+)
AND PLI.interface_header_id =
PHI.interface_header_id
/* Bug# 1638668, forward fix of 1549754 */
--changed by jbalakri during testing of 1549754
--AND PRL.REQUISITION_LINE_ID =
-- x_requisition_line_id
AND PRL.REQUISITION_LINE_ID <>
x_requisition_line_id
AND PRL.requisition_line_id=PLI.requisition_line_id
--end of change for 1549754
--Bug 4599140 (included the following OR condition so that the SQL works correctly
--for null need by date)
AND ( ( to_char(PLI.need_by_date-(to_number(substr(to_char(PLI.need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS') =
to_char(x_need_by_date-(to_number(substr(to_char(x_need_by_date,
'DD-MM-YYYY HH24:MI:SS'),18, 2))/86400), 'DD-MM-YYYY HH24:MI:SS')
)
OR
(PLI.need_by_date is NULL AND x_need_by_date is NULL)
)
--bug 1942696 hr_location changes to reflect the new view
AND exists (select 'x'
from HR_LOCATIONS HRL
where PRL.deliver_to_location_id = HRL.location_id
and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
UNION ALL
select 'x'
from HZ_LOCATIONS HZ
where PRL.deliver_to_location_id = HZ.location_id
and HZ.location_id = x_ship_to_location_id)
AND ROWNUM = 1
ORDER BY shipment_num;
NAME: UPDATE_SHIPMENT
DESC: Update shipment information in interface table
ARGS: x_interface_header_id IN number
x_po_shipment_num IN number
ALGR:
==========================================================================*/
PROCEDURE update_shipment(x_interface_header_id IN NUMBER,
x_po_shipment_num IN number,
x_po_line_num IN NUMBER,
x_requisition_line_id IN NUMBER,
x_po_line_id IN NUMBER,
x_document_num IN VARCHAR2,
x_release_num IN NUMBER,
x_create_new_line IN VARCHAR2,
x_row_id IN VARCHAR2) IS
x_shipment_num NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'update_shipment'; --< Bug 3210331 >
update po_lines_interface
set shipment_num= x_po_shipment_num
where interface_header_id=x_interface_header_id
and requisition_line_id=x_requisition_line_id;
update po_lines_interface
set shipment_num= x_po_shipment_num
where interface_header_id=x_interface_header_id
and rowid=x_row_id;
select nvl(max(line_num),0)
into x_line_num
from po_headers_all ph, --
po_lines_all pl --
where pl.po_header_id = ph.po_header_id
and ph.segment1 = x_document_num
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
select nvl(max(line_num),0)
into x_int_line_num
from po_lines_interface pli
where pli.interface_header_id = x_interface_header_id;
update po_lines_interface
set line_num = x_line_num + 1
where interface_header_id = x_interface_header_id
and requisition_line_id = x_requisition_line_id;
select nvl(max(shipment_num),0)
into x_shipment_num
from po_line_locations_all poll --
where poll.po_line_id = x_po_line_id
and poll.shipment_type in ('STANDARD', 'PLANNED', 'RFQ');
select nvl(max(shipment_num),0)
into x_int_shipment_num
from po_lines_interface pli
where pli.interface_header_id = x_interface_header_id
and pli.line_num = x_po_line_num;
select nvl(max(shipment_num),0)
into x_shipment_num
from po_headers_all ph, --
po_line_locations_all poll, --
po_releases_all pr --
where ph.po_header_id = poll.po_header_id
and ph.segment1 = x_document_num
and pr.po_header_id = ph.po_header_id
and pr.release_num = x_release_num
and ph.type_lookup_code = 'BLANKET'
and poll.po_release_id=pr.po_release_id
AND NVL(pr.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
select nvl(max(shipment_num),0)
into x_int_shipment_num
from po_lines_interface pli
where pli.interface_header_id = x_interface_header_id;
update po_lines_interface
set shipment_num = x_shipment_num + 1
where interface_header_id=x_interface_header_id
and requisition_line_id=x_requisition_line_id;
update po_lines_interface
set shipment_num = x_shipment_num + 1
where interface_header_id=x_interface_header_id
and rowid=x_row_id;
po_message_s.sql_error('update_shipment',l_progress,sqlcode);
END update_shipment;
SELECT MIN(pol.line_num)
FROM po_lines_all pol,
po_requisition_lines_all prl --
WHERE pol.po_header_id = p_po_header_id
AND prl.requisition_line_id = p_requisition_line_id
AND NVL(pol.cancel_flag,'N') = 'N'
AND NVL(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
-- Bug 3828673 START
-- AND pol.line_type_id = prl.line_type_id
AND pol.order_type_lookup_code = prl.order_type_lookup_code
AND pol.purchase_basis = prl.purchase_basis
AND pol.matching_basis = prl.matching_basis
-- Bug 3828673 END
AND nvl(pol.job_id,-999) = nvl(prl.job_id, -999) --
AND (nvl(pol.item_id,-999) = nvl(prl.item_id, -999)
and pol.item_description =
prl.item_description)
AND ((pol.item_revision IS NULL
and prl.item_revision IS NULL)
or pol.item_revision = prl.item_revision
or (prl.item_revision is null and p_item_rev_control = 1))
AND (pol.transaction_reason_code IS NULL
or pol.transaction_reason_code =
NVL(prl.transaction_reason_code,
pol.transaction_reason_code))
/*Bug4541335 AND nvl(p_interface_line_num,pol.line_num) =
pol.line_num
AND trunc(nvl(pol.expiration_date,sysdate+1))
>= trunc(sysdate) */
/*Bug4541335 start */
AND (p_InterFace_Line_num = pol.Line_num
OR (p_InterFace_Line_num IS NULL
AND prl.Blanket_po_Header_Id = p_po_Header_Id
AND prl.Blanket_po_Line_num = pol.Line_num)
OR (p_InterFace_Line_num IS NULL
AND (prl.Blanket_po_Header_Id <> p_po_Header_Id
OR prl.Blanket_po_Line_num IS NULL )))
AND (((p_InterFace_Line_num IS NOT NULL
OR (prl.Blanket_po_Header_Id = p_po_Header_Id
AND prl.Blanket_po_Line_num = pol.Line_num))
AND Trunc(Nvl(pol.Expiration_Date,SYSDATE + 1)) >= Trunc(SYSDATE))
OR ((p_InterFace_Line_num IS NULL
AND (prl.Blanket_po_Header_Id <> p_po_Header_Id
OR prl.Blanket_po_Line_num IS NULL ))
AND Trunc(Nvl(pol.Expiration_Date,SYSDATE + 1)) >= Trunc(DECODE(pol.Expiration_Date,NULL,SYSDATE,
Nvl(prl.Need_By_Date,SYSDATE)))))
/*Bug4541335 End */
-- Bug 2707576 Start
-- Require the BPA and req to have the same UOM
-- if x_allow_different_uoms is not 'Y'.
AND ( ( NVL(POL.unit_meas_lookup_code,chr(0)) =
decode ( x_allow_different_uoms,'Y',
NVL(POL.unit_meas_lookup_code,chr(0)),
PRL.unit_meas_lookup_code)
) --
OR ( ( POL.unit_meas_lookup_code IS NULL )
AND ( PRL.unit_meas_lookup_code IS NULL ) )
);
SELECT MIN(pol.line_num)
FROM po_lines_all pol,
po_requisition_lines_all prl --
WHERE pol.po_header_id = p_po_header_id
AND prl.requisition_line_id = p_requisition_line_id
AND NVL(pol.cancel_flag,'N') = 'N'
AND NVL(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
-- Bug 3828673 START
-- AND pol.line_type_id = prl.line_type_id
AND pol.order_type_lookup_code = prl.order_type_lookup_code
AND pol.purchase_basis = prl.purchase_basis
AND pol.matching_basis = prl.matching_basis
-- Bug 3828673 END
AND nvl(pol.job_id,-999) = nvl(prl.job_id, -999) --
AND ( ( POL.item_id = PRL.item_id ) --
OR ( ( POL.item_id IS NULL )
AND ( PRL.item_id IS NULL ) )
)
AND ((pol.item_revision IS NULL
and prl.item_revision IS NULL)
or pol.item_revision = prl.item_revision
or (prl.item_revision is null and p_item_rev_control = 1))
AND (pol.transaction_reason_code IS NULL
or pol.transaction_reason_code =
NVL(prl.transaction_reason_code,
pol.transaction_reason_code))
/*Bug4541335 AND nvl(p_interface_line_num,pol.line_num) =
pol.line_num
AND trunc(nvl(pol.expiration_date,sysdate+1))
>= trunc(sysdate) */
/*Bug4541335 start */
AND (p_InterFace_Line_num = pol.Line_num
OR (p_InterFace_Line_num IS NULL
AND prl.Blanket_po_Header_Id = p_po_Header_Id
AND prl.Blanket_po_Line_num = pol.Line_num)
OR (p_InterFace_Line_num IS NULL
AND (prl.Blanket_po_Header_Id <> p_po_Header_Id
OR prl.Blanket_po_Line_num IS NULL )))
AND (((p_InterFace_Line_num IS NOT NULL
OR (prl.Blanket_po_Header_Id = p_po_Header_Id
AND prl.Blanket_po_Line_num = pol.Line_num))
AND Trunc(Nvl(pol.Expiration_Date,SYSDATE + 1)) >= Trunc(SYSDATE))
OR ((p_InterFace_Line_num IS NULL
AND (prl.Blanket_po_Header_Id <> p_po_Header_Id
OR prl.Blanket_po_Line_num IS NULL ))
AND Trunc(Nvl(pol.Expiration_Date,SYSDATE + 1)) >= Trunc(DECODE(pol.Expiration_Date,NULL,SYSDATE,
Nvl(prl.Need_By_Date,SYSDATE)))))
/*Bug4541335 End */
-- Bug 2707576 Start
-- Require the BPA and req to have the same UOM
-- if x_allow_different_uoms is not 'Y'.
AND ( ( NVL(POL.unit_meas_lookup_code,chr(0)) =
decode ( x_allow_different_uoms,'Y',
NVL(POL.unit_meas_lookup_code,chr(0)),
PRL.unit_meas_lookup_code)
) --
OR ( ( POL.unit_meas_lookup_code IS NULL )
AND ( PRL.unit_meas_lookup_code IS NULL ) )
);
SELECT inventory_organization_id
INTO x_inv_org_id
FROM financials_system_params_all --
WHERE NVL(org_id, -99) = NVL(p_purchasing_ou_id, -99); --
select item_id
into x_item_id
from po_requisition_lines_all --
where requisition_line_id = x_requisition_line_id;
SELECT msi.revision_qty_control_code
INTO x_item_rev_control
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_inv_org_id;
Function : This procedure is called from 'create_line'. This procedure inserts
records from po_lines_interface table to po_line_locations_all table
for the price break information.
Pre-req : None
Parameters:
IN : p_po_line_id IN NUMBER REQUIRED
OUT : x_line_location_id OUT NOCOPY
==============================================================================*/
PROCEDURE CREATE_PRICE_BREAK(p_po_line_id IN number,
x_line_location_id OUT NOCOPY number,
p_outsourced_assembly IN NUMBER --
) IS
l_row_id varchar2(18) := NULL;
SELECT po_line_locations_s.nextval
INTO x_line_location_id
FROM sys.dual;
SELECT pol.order_type_lookup_code
INTO l_value_basis
FROM po_lines_all pol
WHERE pol.po_line_id = p_po_line_id;
po_line_locations_pkg_s0.insert_row(
l_row_id,
x_Line_Location_Id,
interface.last_update_date,
interface.last_updated_by,
interface.Po_Header_Id,
p_po_Line_Id,
interface.Last_Update_Login,
interface.creation_Date,
interface.created_By,
interface.quantity,
0, --quantity_received
0, --Quantity_Accepted
0, --Quantity_Rejected
0, --Quantity_Billed
0, --Quantity_Cancelled,
interface.unit_meas_lookup_code, --unit of measure
NULL, -- release_id
interface.line_Ship_To_Loc_Id,
interface.Ship_Via_Lookup_Code,
NULL, --Need_By_Date
NULL, --Promised_Date
NULL, --Last_Accept_Date
interface.unit_price, --Price_override
'N', --Encumbered flag
NULL, --Encumbered_Date
NULL, --Fob_Lookup_Code
NULL, --Freight_Terms_Lookup_Code
'N', --Taxable_Flag
NULL, --Tax_Code_Id
'N', --Tax_User_Override_Flag
NULL, --Calculate_Tax_Flag
NULL, --X_From_Header_Id
NULL, --X_From_Line_Id
NULL, --X_From_Line_Location_Id
interface.line_effective_date, --X_Start_Date
interface.line_expiration_date, --X_End_Date
NULL, --X_Lead_Time,
NULL, --X_Lead_Time_Unit,
interface.Price_Discount,
interface.Terms_Id,
NULL, --X_Approved_Flag,
NULL, --X_Approved_Date,
'N', --X_Closed_Flag,
'N', --X_Cancel_Flag,
NULL, --X_Cancelled_By,
NULL, --X_Cancel_Date,
NULL, --X_Cancel_Reason,
'N', --X_Firm_Status_Lookup_Code,
NULL, --X_Attribute_Category,
NULL, --X_Attribute1,
NULL, --X_Attribute2,
NULL, --X_Attribute3,
NULL, --X_Attribute4,
NULL, --X_Attribute5,
NULL, --X_Attribute6,
NULL, --X_Attribute7,
NULL, --X_Attribute8,
NULL, --X_Attribute9,
NULL, --X_Attribute10,
NULL, --X_Attribute11,
NULL, --X_Attribute12,
NULL, --X_Attribute13,
NULL, --X_Attribute14,
NULL, --X_Attribute15,
'N', --X_Inspection_Required_Flag,
'N', --X_Receipt_Required_Flag,
NULL, --X_Qty_Rcv_Tolerance,
NULL, --X_Qty_Rcv_Exception_Code,
'NONE', --X_Enforce_Ship_To_Location,
NULL, --X_Allow_Substitute_Receipts,
NULL, --X_Days_Early_Receipt_Allowed,
NULL, --X_Days_Late_Receipt_Allowed,
NULL, --X_Receipt_Days_Exception_Code,
NULL, --X_Invoice_Close_Tolerance,
NULL, --X_Receive_Close_Tolerance,
interface.line_Ship_To_Org_Id,
interface.Shipment_Num,
NULL, --X_Source_Shipment_Id,
interface.Shipment_Type,
'OPEN', --X_Closed_Code,
NULL, --
NULL, --X_Government_Context,
NULL, --X_Receiving_Routing_Id,
NULL, --X_Accrue_On_Receipt_Flag,
NULL, --X_Closed_Reason,
NULL, --X_Closed_Date,
NULL, --X_Closed_By,
NULL, --X_Global_Attribute_Category,
NULL, --X_Global_Attribute1,
NULL, --X_Global_Attribute2,
NULL, --X_Global_Attribute3,
NULL, --X_Global_Attribute4,
NULL, --X_Global_Attribute5,
NULL, --X_Global_Attribute6,
NULL, --X_Global_Attribute7,
NULL, --X_Global_Attribute8,
NULL, --X_Global_Attribute9,
NULL, --X_Global_Attribute10,
NULL, --X_Global_Attribute11,
NULL, --X_Global_Attribute12,
NULL, --X_Global_Attribute13,
NULL, --X_Global_Attribute14,
NULL, --X_Global_Attribute15,
NULL, --X_Global_Attribute16,
NULL, --X_Global_Attribute17,
NULL, --X_Global_Attribute18,
NULL, --X_Global_Attribute19,
NULL, --X_Global_Attribute20,
NULL, --X_Country_of_Origin_Code,
'P', --invoice option
l_value_basis, --
NULL, -- : matching basis
NULL, --X_note_to_receiver,
NULL, --X_Secondary_Unit_Of_Measure,
NULL, --X_Secondary_Quantity,
NULL, --X_Preferred_Grade,
NULL, --X_Secondary_Quantity_Received,
NULL, --X_Secondary_Quantity_Accepted,
NULL, --X_Secondary_Quantity_Rejected,
NULL, --X_Secondary_Quantity_Cancelled,
NULL, --X_Consigned_Flag --
interface.amount, --X_Amount --
NULL, -- p_transaction_flow_header_id
NULL, -- p_manual_price_change_flag
interface.org_id --
,p_outsourced_assembly --
);
SELECT NVL(hrl.ship_to_location_id, hrl.location_id)
INTO l_ship_to_location_id
FROM hr_locations hrl
WHERE hrl.location_id = p_deliver_to_loc_id;
SELECT hzl.location_id
INTO l_ship_to_location_id
FROM hz_locations hzl
WHERE hzl.location_id = p_deliver_to_loc_id;
SELECT count(*)
INTO l_num_config_id_lines
FROM po_lines_interface PLI, po_requisition_lines PRL
WHERE PLI.interface_header_id = p_interface_header_id
AND PLI.requisition_line_id = PRL.requisition_line_id -- JOIN
AND PRL.supplier_ref_number IS NOT NULL;
PROCEDURE update_award_distributions(
p_table_type IN VARCHAR2 DEFAULT 'INTERFACE'
, p_po_line_id IN NUMBER DEFAULT NULL
)
IS
l_api_name CONSTANT VARCHAR(30) := 'update_award_distributions';
SELECT pod.po_distribution_id,
pod.distribution_num,
pod.project_id,
pod.task_id,
pod.award_id,
NULL
BULK COLLECT INTO
l_gms_po_interface_obj.distribution_id,
l_gms_po_interface_obj.distribution_num,
l_gms_po_interface_obj.project_id,
l_gms_po_interface_obj.task_id,
l_gms_po_interface_obj.award_set_id_in,
l_gms_po_interface_obj.award_set_id_out
FROM po_distributions_all pod
WHERE pod.po_line_id = p_po_line_id
AND pod.award_id IS NOT NULL;
SELECT po_distribution_id,
distribution_num,
project_id,
task_id,
award_id,
NULL
BULK COLLECT INTO
l_gms_po_interface_obj.distribution_id,
l_gms_po_interface_obj.distribution_num,
l_gms_po_interface_obj.project_id,
l_gms_po_interface_obj.task_id,
l_gms_po_interface_obj.award_set_id_in,
l_gms_po_interface_obj.award_set_id_out
FROM PO_DISTRIBUTIONS_INTERFACE
WHERE interface_header_id = interface.interface_header_id
AND interface_line_id = interface.interface_line_id
AND award_id IS NOT NULL;
UPDATE po_distributions_all
SET award_id =
l_gms_po_interface_obj.award_set_id_out(i)
WHERE po_distribution_id =
l_gms_po_interface_obj.distribution_id(i);
UPDATE po_distributions_interface
SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i);
END update_award_distributions;
select req_fsp.set_of_books_id
into l_req_ou_sob_id
from financials_system_params_all req_fsp
where req_fsp.org_id = p_requesting_ou_id; --
select po_fsp.set_of_books_id
into l_po_ou_sob_id
from financials_system_params_all po_fsp
where po_fsp.org_id = p_purchasing_ou_id; --
select default_rate_type
into l_rate_type
from po_system_parameters_all psp
where psp.org_id = p_purchasing_ou_id; --
SELECT polli.interface_line_location_id,
polli.quantity,
polli.amount,
polli.ship_to_location_id,
polli.need_by_date,
polli.promised_date,
polli.price_override,
polli.shipment_type,
polli.shipment_num,
polli.ship_to_organization_id,
polli.value_basis,
polli.matching_basis,
polli.payment_type,
polli.description,
polli.work_approver_id,
polli.bid_payment_id,
polli.unit_of_measure
FROM po_line_locations_interface polli
WHERE polli.interface_line_id = p_interface_line_id
ORDER BY polli.shipment_num;
SELECT pol.order_type_lookup_code
, pol.matching_basis
, pol.po_header_id
, pol.unit_price
, pol.quantity
, pol.amount
, pol.purchase_basis
INTO l_line_value_basis
, l_line_matching_basis
, l_po_header_id
, l_line_unit_price
, l_line_quantity
, l_line_amount
, l_line_purchase_basis
FROM po_lines_all pol
WHERE pol.po_line_id = p_po_line_id;
INSERT INTO po_line_locations_interface
(
interface_line_location_id
, interface_header_id
, interface_line_id
, quantity
, amount
, price_override
, shipment_type
, payment_type
, shipment_num
, need_by_date
)
VALUES
(
PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
, interface.interface_header_id
, p_interface_line_id
, l_payitem_quantity
, l_payitem_amount
, l_payitem_price
, NULL
, l_payment_type
, 1
, interface.need_by_date
);
INSERT INTO po_line_locations_interface
(
interface_line_location_id
, interface_header_id
, interface_line_id
, quantity
, amount
, price_override
, payment_type
, shipment_type
, description
, shipment_num
, need_by_date
, promised_date
)
VALUES
(
PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
, interface.interface_header_id
, p_interface_line_id
, l_line_quantity
, l_line_amount
, l_line_unit_price
, 'DELIVERY'
, 'STANDARD'
, interface.item_description
, 1
, interface.need_by_date
, interface.promised_date --Bug5532424
);
INSERT INTO po_line_locations_interface
(
interface_line_location_id
, interface_header_id
, interface_line_id
, quantity
, amount
, price_override
, payment_type
, shipment_type
, description
, shipment_num
, need_by_date
)
VALUES
(
PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
, interface.interface_header_id
, p_interface_line_id
, NULL
, interface.advance_amount
, NULL
, 'ADVANCE'
, 'PREPAYMENT'
, l_advance_desc
, 0
, NULL
);
UPDATE po_line_locations_interface polli
SET polli.value_basis =
DECODE(polli.payment_type,
'RATE', 'QUANTITY',
'LUMPSUM', 'FIXED PRICE',
'MILESTONE', l_line_value_basis,
'ADVANCE', 'FIXED PRICE',
'DELIVERY', l_line_value_basis,
polli.value_basis),
polli.matching_basis =
DECODE(polli.payment_type,
'RATE', 'QUANTITY',
'LUMPSUM', 'AMOUNT',
'MILESTONE', l_line_matching_basis,
'ADVANCE', 'AMOUNT',
'DELIVERY', l_line_matching_basis,
polli.matching_basis),
polli.ship_to_location_id =
NVL(polli.ship_to_location_id, l_ship_to_location_id),
polli.ship_to_organization_id =
NVL(polli.ship_to_organization_id,
interface.destination_organization_id),
polli.promised_date =
NVL(polli.promised_date,
DECODE(NVL(l_po_promised_def_prf, 'N'), 'Y', polli.need_by_date,
polli.promised_date)),
polli.shipment_type = NVL(polli.shipment_type, l_shipment_type),
polli.description = NVL(polli.description, interface.item_description),
polli.unit_of_measure = NVL(polli.unit_of_measure, interface.unit_meas_lookup_code)
WHERE polli.interface_line_id = p_interface_line_id;
SELECT prl.tax_code_id
, nvl(prl.tax_user_override_flag,'N')
, nvl(prl.tax_status_indicator,'SYSTEM')
, nvl(prl.org_id, g_hdr_requesting_ou_id)
INTO l_req_tax_code_id
, l_req_tax_user_override_flag
, l_req_tax_status_indicator
, g_line_requesting_ou_id
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = interface.requisition_line_id;
PO_LOG.stmt(d_module, d_progress, 'Inserting payitem into po_line_locations_all');
INSERT INTO po_line_locations_all
(
line_location_id
, last_update_date
, last_updated_by
, po_header_id
, creation_date
, created_by
, last_update_login
, po_line_id
, quantity
, quantity_received
, quantity_accepted
, quantity_rejected
, quantity_billed
, quantity_cancelled
, quantity_financed
, amount
, amount_received
, amount_accepted
, amount_rejected
, amount_billed
, amount_cancelled
, amount_financed
, ship_to_location_id
, need_by_date
, promised_date
, from_header_id
, from_line_id
, note_to_receiver
, approved_flag
, po_release_id
, closed_code
, closed_reason
, price_override
, encumbered_flag
, taxable_flag
, tax_code_id
, tax_user_override_flag
, shipment_type
, shipment_num
, inspection_required_flag
, receipt_required_flag
, days_early_receipt_allowed
, days_late_receipt_allowed
, enforce_ship_to_location_code
, ship_to_organization_id
, invoice_close_tolerance
, receive_close_tolerance
, accrue_on_receipt_flag
, allow_substitute_receipts_flag
, receiving_routing_id
, qty_rcv_tolerance
, qty_rcv_exception_code
, receipt_days_exception_code
, terms_id
, ship_via_lookup_code
, freight_terms_lookup_code
, fob_lookup_code
, unit_meas_lookup_code
, last_accept_date
, match_option
, country_of_origin_code
, vmi_flag
, drop_ship_flag
, consigned_flag
, transaction_flow_header_id
, org_id
, closed_for_receiving_date
, closed_for_invoice_date
, value_basis
, matching_basis
, payment_type
, description
, work_approver_id
, bid_payment_id
, outsourced_assembly
,tax_attribute_update_code --
)
VALUES
(
PO_LINE_LOCATIONS_S.nextval
, interface.last_update_date
, interface.last_updated_by
, interface.po_header_id
, interface.creation_date
, interface.created_by
, interface.last_update_login
, p_po_line_id
, line_location_rec.quantity -- quantity
, 0 -- quantity_received
, 0 -- quantity_accepted
, 0 -- quantity_rejected
, 0 -- quantity_billed
, 0 -- quantity_cancelled
, 0 -- quantity_financed
, line_location_rec.amount -- amount
, 0 -- amount_received
, 0 -- amount_accepted
, 0 -- amount_rejected
, 0 -- amount_billed
, 0 -- amount_cancelled
, 0 -- amount_financed
, line_location_rec.ship_to_location_id
, line_location_rec.need_by_date
, line_location_rec.promised_date
, interface.from_header_id
, interface.from_line_id
, interface.note_to_receiver
, 'N' -- approved_flag
, NULL -- po_release_d
, 'OPEN' -- closed_code
, NULL -- closed_reason
, line_location_rec.price_override
, 'N' -- encumbered_flag
, NVL2(l_tax_code_id, 'Y', 'N') -- taxable_flag
, l_tax_code_id
, l_req_tax_user_override_flag
, line_location_rec.shipment_type
, line_location_rec.shipment_num
, 'N' -- inspection_required_flag
, DECODE(line_location_rec.value_basis, -- receipt_required_flag
'FIXED_PRICE', 'N',
coalesce(item.receipt_required_flag,
vendor.receipt_required_flag,
params.receiving_flag,
'N'))
, payitem_rcv_ctl_rec.days_early_receipt_allowed
, payitem_rcv_ctl_rec.days_late_receipt_allowed
, payitem_rcv_ctl_rec.enforce_ship_to_location_code
, line_location_rec.ship_to_organization_id
, coalesce(item.invoice_close_tolerance, params.invoice_close_tolerance, 100)
, DECODE(line_location_rec.payment_type,
'MILESTONE', 0,
coalesce(item.receive_close_tolerance,
params.receive_close_tolerance,
100))
, DECODE(line_location_rec.shipment_type, -- acrrue_on_receipt_flag
'PREPAYMENT', 'N',
DECODE(coalesce(item.receipt_required_flag,
interface.receipt_required_flag,
vendor.receipt_required_flag,
params.receiving_flag,
'N'),
'N', 'N',
DECODE(params.expense_accrual_code,
'PERIOD END', 'N', 'Y')))
, payitem_rcv_ctl_rec.allow_substitute_receipts_flag
, payitem_rcv_ctl_rec.receiving_routing_id
, payitem_rcv_ctl_rec.qty_rcv_tolerance
, payitem_rcv_ctl_rec.qty_rcv_exception_code
, payitem_rcv_ctl_rec.receipt_days_exception_code
, NULL -- terms_id
, NULL -- ship_via_lookup_code
, NULL -- freight_terms_lookup_code
, NULL -- fob_lookup_code
, line_location_rec.unit_of_measure -- unit_meas_lookup_code
, line_location_rec.promised_date -- last_accept_date
+ payitem_rcv_ctl_rec.days_late_receipt_allowed
, vendor.invoice_match_option
, l_country_of_origin_code
, NULL -- vmi_flag
, NULL -- drop_ship_flag
, NULL -- consigned_flag
, interface.transaction_flow_header_id
, g_purchasing_ou_id
, NULL -- closed_for_receiving_date
, NULL -- closed_for_invoice_date
, line_location_rec.value_basis
, line_location_rec.matching_basis
, line_location_rec.payment_type
, line_location_rec.description
, line_location_rec.work_approver_id
, line_location_rec.bid_payment_id
, 2 -- outsourced_assembly
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
)
RETURNING line_location_id INTO l_line_loc_id;
PO_LOG.stmt(d_module, d_progress, 'Inserted payitem.');
UPDATE po_line_locations_interface polli
SET polli.line_location_id = l_line_loc_id
WHERE polli.interface_line_location_id =
line_location_rec.interface_line_location_id;
SELECT pod.po_distribution_id
, pod.project_id
, pod.task_id
, pod.award_id
, pod.expenditure_type
, pod.expenditure_item_date
, pod.expenditure_organization_id
, pod.destination_type_code
, pod.destination_organization_id
, pod.destination_subinventory
, pod.deliver_to_location_id
, pod.deliver_to_person_id
, pod.gl_encumbered_date
, poll.price_override
, poll.payment_type
, pod.distribution_type
, pod.rate
FROM po_distributions_all pod,
po_line_locations_all poll
WHERE poll.po_line_id = p_po_line_id
AND pod.line_location_id = poll.line_location_id
AND pod.req_distribution_id IS NULL;
SELECT prd.set_of_books_id
INTO l_sob_id
FROM po_req_distributions_all prd
WHERE prd.requisition_line_id = p_req_line_id
AND ROWNUM = 1;
INSERT INTO po_distributions_all(
po_distribution_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, po_header_id
, po_line_id
, line_location_id
, distribution_num
, req_distribution_id
, set_of_books_id
, code_combination_id
, deliver_to_location_id
, deliver_to_person_id
, destination_type_code
, destination_organization_id
, destination_subinventory
, project_id
, task_id
, award_id
, end_item_unit_number
, expenditure_type
, project_accounting_context
, destination_context
, expenditure_organization_id
, expenditure_item_date
, rate
, rate_date
, budget_account_id
, accrual_account_id
, variance_account_id
, accrued_flag
, encumbered_flag
, prevent_encumbrance_flag
, gl_encumbered_date
, gl_encumbered_period_name
, recovery_rate
, recoverable_tax
, nonrecoverable_tax
, accrue_on_receipt_flag
, kanban_card_id
, org_id
, distribution_type
, quantity_ordered
, amount_ordered
,tax_attribute_update_code --
)
SELECT
PO_DISTRIBUTIONS_S.NEXTVAL
, interface.last_update_date
, interface.last_updated_by
, interface.last_update_login
, interface.creation_date
, interface.created_by
, interface.po_header_id
, p_po_line_id
, poll.line_location_id
, prd.distribution_num
, prd.distribution_id --Bug 4744751: these 2 cols were reversed in order
, prd.set_of_books_id --Bug 4744751: these 2 cols were reversed in order
, prd.code_combination_id
, prl.deliver_to_location_id
, prl.to_person_id
, prl.destination_type_code
, prl.destination_organization_id
, prl.destination_subinventory
, prd.project_id
, prd.task_id
, prd.award_id
, prd.end_item_unit_number
, prd.expenditure_type
, prd.project_accounting_context
, prl.destination_context
, prd.expenditure_organization_id
, prd.expenditure_item_date
, interface.h_rate
, interface.h_rate_date
, DECODE(poll.shipment_type, 'PREPAYMENT', null, prd.budget_account_id)
, prd.accrual_account_id
, prd.variance_account_id
, 'N' -- accrued_flag
, 'N' -- encumbered_flag
, DECODE(params.po_encumbrance_flag, 'Y',
DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'),
null) -- prevent_encumbrance_flag
, (CASE -- gl_encumbered_date
WHEN (params.req_encumbrance_flag = 'Y' AND
l_gl_date_option = 'REQ GL DATE')
THEN prd.gl_encumbered_date
WHEN (params.po_encumbrance_flag = 'Y')
THEN trunc(SYSDATE)
ELSE NULL
END)
, (CASE -- gl_encumbered_period_name
WHEN (params.req_encumbrance_flag = 'Y' AND
l_gl_date_option = 'REQ GL DATE')
THEN prd.gl_encumbered_period_name
WHEN (params.po_encumbrance_flag = 'Y')
THEN params.period_name
ELSE NULL
END)
, prd.recovery_rate
, prd.recoverable_tax
, prd.nonrecoverable_tax
, poll.accrue_on_receipt_flag
, prl.kanban_card_id
, g_purchasing_ou_id
, poll.shipment_type
, (CASE -- quantity_ordered
WHEN poll.value_basis <> 'QUANTITY'
THEN NULL
WHEN poll.payment_type IN ('MILESTONE', 'DELIVERY')
THEN ROUND((prd.req_line_quantity / prl.quantity) * poll.quantity, 15)
WHEN poll.payment_type = 'RATE'
THEN ROUND((prd.req_line_amount / prl.amount) * poll.quantity, 15)
END)
, (CASE -- amount_ordered
WHEN poll.value_basis <> 'FIXED PRICE'
THEN NULL
ELSE ROUND((prd.req_line_amount / prl.amount) * poll.amount, p_precision)
END)
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
FROM po_line_locations_all poll
, po_req_distributions_all prd
, po_requisition_lines_all prl
WHERE poll.po_line_id = p_po_line_id
AND prd.requisition_line_id = p_req_line_id
AND prl.requisition_line_id = prd.requisition_line_id
AND poll.payment_type <> 'ADVANCE';
INSERT INTO po_distributions_all(
po_distribution_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, po_header_id
, po_line_id
, line_location_id
, distribution_num
, req_distribution_id
, deliver_to_location_id
, deliver_to_person_id
, destination_type_code
, destination_organization_id
, destination_subinventory
, rate
, rate_date
, accrued_flag
, encumbered_flag
, prevent_encumbrance_flag
, gl_encumbered_date
, gl_encumbered_period_name
, accrue_on_receipt_flag
, org_id
, distribution_type
, project_id
, task_id
, award_id
, end_item_unit_number
, expenditure_type
, project_accounting_context
, destination_context
, expenditure_organization_id
, expenditure_item_date
, quantity_ordered
, amount_ordered
, set_of_books_id
,tax_attribute_update_code --
)
SELECT
PO_DISTRIBUTIONS_S.NEXTVAL
, interface.last_update_date
, interface.last_updated_by
, interface.last_update_login
, interface.creation_date
, interface.created_by
, interface.po_header_id
, p_po_line_id
, poll.line_location_id
, 1 -- distribution_num
, NULL -- req_distribution_id
, poll.ship_to_location_id
, NULL -- deliver_to_person_id
, 'EXPENSE' -- destination_type_code
, poll.ship_to_organization_id
, NULL -- destination_subinventory
, interface.h_rate
, interface.h_rate_date
, 'N' -- accrued_flag
, 'N' -- encumbered_flag
, DECODE(params.po_encumbrance_flag, 'Y',
DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'),
null) -- prevent_encumbrance_flag
, DECODE(params.po_encumbrance_flag, 'Y', trunc(SYSDATE), NULL)
, DECODE(params.po_encumbrance_flag, 'Y', params.period_name, NULL)
, poll.accrue_on_receipt_flag
, g_purchasing_ou_id
, poll.shipment_type
, polli.project_id
, polli.task_id
, polli.award_id
, NULL -- end_item_unit_number
, polli.expenditure_type
, NULL -- project_accounting_context
, 'EXPENSE' -- destination_context
, polli.expenditure_organization_id
, polli.expenditure_item_date
, poll.quantity
, poll.amount
, params.sob_id
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
FROM po_line_locations_all poll
, po_line_locations_interface polli
WHERE poll.po_line_id = p_po_line_id
AND poll.line_location_id = polli.line_location_id
AND poll.payment_type <> 'ADVANCE';
INSERT INTO po_distributions_all
(
po_distribution_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, po_header_id
, po_line_id
, line_location_id
, distribution_num
, req_distribution_id
, deliver_to_location_id
, deliver_to_person_id
, destination_type_code
, destination_organization_id
, destination_subinventory
, rate
, rate_date
, accrued_flag
, encumbered_flag
, prevent_encumbrance_flag
, gl_encumbered_date
, gl_encumbered_period_name
, accrue_on_receipt_flag
, org_id
, distribution_type
, amount_ordered
, quantity_ordered
, project_id
, task_id
, award_id
, end_item_unit_number
, expenditure_type
, project_accounting_context
, destination_context
, expenditure_organization_id
, expenditure_item_date
, set_of_books_id
,tax_attribute_update_code --
)
SELECT
PO_DISTRIBUTIONS_S.NEXTVAL
, interface.last_update_date
, interface.last_updated_by
, interface.last_update_login
, interface.creation_date
, interface.created_by
, interface.po_header_id
, p_po_line_id
, adv.line_location_id
, pod.distribution_num -- distribution_num
, NULL -- req_distribution_id
, pod.deliver_to_location_id
, pod.deliver_to_person_id
, pod.destination_type_code
, pod.destination_organization_id
, pod.destination_subinventory
, pod.rate
, pod.rate_date
, pod.accrued_flag
, pod.encumbered_flag
, DECODE(params.po_encumbrance_flag, 'Y', 'Y', null) --prevent_enc_flag
, NULL -- gl_encumbered_date
, NULL -- gl_encumbered_period_name
, adv.accrue_on_receipt_flag
, pod.org_id
, adv.shipment_type
, ROUND( -- amount_ordered
(NVL(pod.amount_ordered, deliv.price_override * pod.quantity_ordered)
/ NVL(deliv.amount, deliv.price_override * deliv.quantity))
* adv.amount, 15)
, NULL -- quantity_ordered
, pod.project_id
, pod.task_id
, pod.award_id
, pod.end_item_unit_number
, pod.expenditure_type
, pod.project_accounting_context
, pod.destination_context
, pod.expenditure_organization_id
, pod.expenditure_item_date
, params.sob_id
,nvl2(g_calculate_tax_flag, 'CREATE', null) --
FROM po_line_locations_all adv,
po_line_locations_all deliv,
po_distributions_all pod
WHERE adv.po_line_id = p_po_line_id
AND adv.payment_type = 'ADVANCE'
AND deliv.line_location_id =
( SELECT poll.line_location_id
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_po_line_id
AND poll.shipment_type = 'STANDARD'
AND poll.shipment_num =
( SELECT min(poll2.shipment_num)
FROM po_line_locations_all poll2
WHERE poll2.po_line_id = poll.po_line_id
AND poll2.shipment_type = 'STANDARD'))
AND pod.line_location_id = deliv.line_location_id;
SELECT poll.line_location_id, poll.value_basis
BULK COLLECT INTO l_line_loc_id_tbl, l_line_loc_value_basis_tbl
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_po_line_id;
UPDATE po_distributions_all pod
SET pod.code_combination_id = l_code_combination_id
, pod.budget_account_id = DECODE(NVL(params.po_encumbrance_flag, 'N'),
'Y', l_budget_account_id,
NULL)
, pod.accrual_account_id = l_accrual_account_id
, pod.variance_account_id = l_variance_account_id
WHERE pod.po_distribution_id = payitem_acct_rec.po_distribution_id;
DELETE FROM po_distributions_all pod
WHERE pod.po_distribution_id =
payitem_acct_rec.po_distribution_id;
PO_LOG.stmt(d_module, d_progress, 'Calling update_award_distributions');
update_award_distributions(
p_table_type => 'ALL'
, p_po_line_id => p_po_line_id
);
SELECT pod.po_distribution_id
BULK COLLECT INTO l_dist_id_tbl
FROM po_distributions_all pod
WHERE pod.po_line_id = p_po_line_id;