[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO x_start_date_count
FROM po_line_locations
WHERE start_date < X_start_date
AND po_header_id = X_po_header_id;
SELECT count(*)
INTO x_end_date_count
FROM po_line_locations
WHERE end_date > X_end_date
AND po_header_id = X_po_header_id;
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_login NUMBER,
X_po_header_id NUMBER,
X_po_line_id NUMBER,
X_type_lookup_code VARCHAR2,
X_quantity NUMBER,
X_ship_to_location_id NUMBER,
X_ship_org_id NUMBER,
X_need_by_date DATE,
X_promised_date DATE,
X_unit_price NUMBER,
X_tax_code_id NUMBER,
X_taxable_flag VARCHAR2,
X_enforce_ship_to_location VARCHAR2,
X_receiving_routing_id NUMBER,
X_inspection_required_flag VARCHAR2,
X_receipt_required_flag VARCHAR2,
X_qty_rcv_tolerance NUMBER,
X_qty_rcv_exception_code VARCHAR2,
X_days_early_receipt_allowed NUMBER,
X_days_late_receipt_allowed NUMBER,
X_allow_substitute_receipts VARCHAR2,
X_receipt_days_exception_code VARCHAR2,
X_invoice_close_tolerance NUMBER,
X_receive_close_tolerance NUMBER,
X_item_status VARCHAR2,
X_outside_operation_flag VARCHAR2,
X_destination_type_code VARCHAR2,
X_expense_accrual_code VARCHAR2,
X_item_id NUMBER,
X_ussgl_transaction_code VARCHAR2,
X_accrue_on_receipt_flag IN OUT NOCOPY VARCHAR2,
X_autocreated_ship IN OUT NOCOPY BOOLEAN,
X_unit_meas_lookup_code IN VARCHAR2, -- Added Bug 731564
p_value_basis IN VARCHAR2, --
p_matching_basis IN VARCHAR2, --
-- start of bug# 1548597
X_secondary_unit_of_measure IN VARCHAR2,
X_secondary_quantity IN NUMBER,
X_preferred_grade IN VARCHAR2,
p_consigned_from_supplier_flag IN VARCHAR2 --bug 3523348
-- end of bug# 1548597
,p_org_id IN NUMBER --
,p_outsourced_assembly IN NUMBER --
)
is
X_Progress varchar2(3) := '';
select vendor_site_id, vendor_id
into x_vendor_site_id, x_vendor_id
from po_headers
where po_header_id = x_po_header_id;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendor_sites
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 fsp.match_option
INTO x_invoice_match_option
FROM financials_system_parameters fsp;
SELECT po_line_locations_s.nextval
INTO X_line_location_id
FROM sys.dual;
/* GA FPI start : we need to insert the source doc info from the lines to shipments */
/* SERVICES FPJ : We also need to insert the line amount to the shipments */
begin
select from_line_id,from_header_id,amount
into l_from_line_id, l_from_header_id,l_amount
from po_lines
where po_line_id = X_po_line_id ;
INSERT into po_line_locations
(line_location_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
po_header_id ,
po_line_id ,
shipment_num ,
shipment_type ,
quantity ,
quantity_received ,
quantity_accepted ,
quantity_rejected ,
quantity_billed ,
quantity_cancelled ,
ship_to_location_id ,
ship_to_organization_id ,
need_by_date ,
promised_date ,
last_accept_date ,
cancel_flag ,
closed_code ,
approved_flag ,
price_override ,
encumbered_flag ,
tax_code_id ,
taxable_flag ,
enforce_ship_to_location_code,
receiving_routing_id ,
inspection_required_flag,
receipt_required_flag ,
qty_rcv_tolerance ,
qty_rcv_exception_code ,
days_early_receipt_allowed,
days_late_receipt_allowed,
allow_substitute_receipts_flag,
receipt_days_exception_code,
invoice_close_tolerance ,
receive_close_tolerance,
accrue_on_receipt_flag,
tax_user_override_flag,
calculate_tax_flag,
unit_meas_lookup_code, -- Added Bug 731564
match_option, -- Bug 880864
-- start of bug# 1548597
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_accepted,
secondary_quantity_rejected,
secondary_quantity_cancelled,
country_of_origin_code, -- bug 2350043
-- end of bug# 1548597
consigned_flag, /* CONSIGNED FPI */
closed_reason , /* CONSIGNED FPI */
from_header_id, -- GA FPI
from_line_id , -- GA FPI
amount
--
,shipment_closed_date
,closed_for_receiving_date
,closed_for_invoice_date
--
,Org_Id --
, value_basis --
, matching_basis --
,outsourced_assembly --
)
VALUES
(X_line_location_id ,
X_last_update_date ,
X_last_updated_by ,
X_creation_date ,
X_created_by ,
X_last_update_login ,
X_po_header_id ,
X_po_line_id ,
'1' ,
X_type_lookup_code ,
X_quantity ,
'0' ,
'0' ,
'0' ,
'0' ,
'0' ,
X_ship_to_location_id ,
X_ship_org_id ,
X_need_by_date ,
X_promised_date ,
'' ,
'N' ,
x_closed_code, /* CONSIGNED FPI */ --'OPEN' ,
'N' ,
X_unit_price,
'N' ,
X_tax_code_id ,
X_taxable_flag ,
X_enforce_ship_to_location ,
X_receiving_routing_id ,
l_inspection_required_flag , /* CONSIGNED FPI */
l_receipt_required_flag , /* CONSIGNED FPI */
X_qty_rcv_tolerance ,
X_qty_rcv_exception_code ,
X_days_early_receipt_allowed ,
X_days_late_receipt_allowed ,
X_allow_substitute_receipts ,
X_receipt_days_exception_code ,
l_invoice_close_tolerance , /* CONSIGNED FPI */
X_receive_close_tolerance ,
nvl(X_accrue_on_receipt_flag,'N'),
'N',
'Y',
X_unit_meas_lookup_code, -- Added Bug 731564
X_invoice_match_option, -- Bug 880864
-- start of bug# 1548597
X_secondary_unit_of_measure,
X_secondary_quantity,
X_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),
x_country_of_origin_code, -- bug 2350043
-- end of bug# 1548597
x_consigned_flag, /* CONSIGNED FPI */
x_closed_reason, /* CONSIGNED FPI */
l_from_header_id, -- GA FPI
l_from_line_id, -- GA FPI
l_amount -- SERVICES FPJ
--
,decode(x_closed_code,'CLOSED',
sysdate, null) -- Shipment_closed_date
,decode(x_closed_code,'CLOSED',sysdate,
'CLOSED FOR RECEIVING',sysdate,null) -- Closed_for_receiving_date
,decode(x_closed_code,'CLOSED',sysdate,
'CLOSED FOR INVOICE',sysdate,null) -- closed_for_invoice_date
--
,p_org_id --
, p_value_basis --
, p_matching_basis --
, p_outsourced_assembly --
);
SELECT receiving_flag,
inspection_required_flag
INTO X_receipt_required_flag,
X_inspection_required_flag
FROM po_system_parameters;
SELECT receipt_required_flag,
inspection_required_flag
INTO X_receipt_required_flag_tmp,
X_inspection_required_flag_tmp
FROM po_vendors
WHERE vendor_id = X_vendor_id;
SELECT receiving_flag
INTO X_receipt_required_flag_tmp
FROM po_line_types
WHERE line_type_id = X_line_type_id;
SELECT receipt_required_flag,
inspection_required_flag
INTO X_receipt_required_flag_tmp,
X_inspection_required_flag_tmp
FROM mtl_system_items,
financials_system_parameters
WHERE inventory_item_id = X_item_id
AND organization_id = inventory_organization_id;