[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** validation and insert/update of the Shipments.
**
**
** The procedures of this package are called from PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
**
** HISTORY
** 10/11/09 bisdas Created
==============================================================================*/
/* ============================================================================
Name: create_shipment_draft
Pre-reqs:
PO Line has been created
Modifies:
PO_LINE_LOCATIONS_DRAFT_ALL
Locks:
None
Function:
Derives,deaults the shipment info from available lines information and
inserts/updates the PO_LINE_LOCATIONS_DRAFT_ALL.
Parameters:
p_lines IN OUT Derived Line data after lines processing
Returns:
None
Testing:
None
Caller of the Procedure:
PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
==============================================================================*/
PROCEDURE create_shipment_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
IS
p_line_locs po_autocreate_types.line_locs_rec_type;
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 = p_lines.line_type_id_tbl(i);
SELECT poh.type_lookup_code
INTO l_from_type_lookup_code
FROM po_headers_all poh
WHERE poh.po_header_id=p_lines.from_header_id_tbl(i) ;
SELECT unit_meas_lookup_code
INTO x_po_uom
FROM po_lines_draft_all
WHERE po_line_id = p_lines.from_line_id_tbl(i)
AND draft_id =po_autocreate_params.g_draft_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
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_draft_all poll, --
po_lines_draft_all pol --
WHERE poll.po_header_id = p_lines.po_header_id_tbl(i)
AND pol.draft_id =po_autocreate_params.g_draft_id
AND poll.po_line_id = p_lines.po_line_id_tbl(i)
AND poll.shipment_num = p_lines.shipment_num_tbl(i)
AND pol.line_num = p_lines.line_num_tbl(i)
AND poll.shipment_type IN ('STANDARD','PLANNED', 'RFQ')
--
AND pol.po_line_id = poll.po_line_id
AND pol.draft_id = poll.draft_id;
SELECT unit_price
INTO x_price
FROM po_lines_draft_all --
WHERE po_line_id=p_lines.po_line_id_tbl(i)
AND draft_id =po_autocreate_params.g_draft_id;
** Update everything except closed_code
*/
l_progress:='120';
p_message => 'Create_shipment: Update PO line locations');
UPDATE po_line_locations_draft_all --
SET quantity = quantity + x_quantity,
secondary_quantity = secondary_quantity + x_secondary_quantity,
approved_flag = DECODE(approved_flag, 'N','N', 'R'),
last_update_date = p_lines.last_update_date_tbl(i),
last_update_login = p_lines.last_update_login_tbl(i),
last_updated_by = p_lines.last_updated_by_tbl(i),
price_override = DECODE(po_autocreate_params.g_document_type, 'RFQ', price_override, DECODE( NVL(x_price, -1), -1, price_override,
-- Use precision in rounding
ROUND(x_price, NVL(x_ext_precision,15)))),
-- Setting tax_attribute_update_code to update for
-- add_to cases.
tax_attribute_update_code = NVL(tax_attribute_update_code, NVL2(po_autocreate_params.g_calculate_tax_flag, 'UPDATE', NULL))
WHERE line_location_id = x_line_location_id
AND draft_id = po_autocreate_params.g_draft_id;
** Therefore, no need to do call back for update shipment
** Removed oe callback.
*/
/*
** Prepare to call pocupdate_close: - call auto close.
*/
-- Need to change - draft? serukull or move code to post proc?
IF (po_autocreate_params.g_document_type = 'PO') THEN
IF (po_autocreate_params.g_mode = 'ADD') THEN
IF (po_autocreate_params.g_document_type = 'PO') THEN
IF (po_autocreate_params.g_document_subtype = 'RELEASE') THEN
NULL;
** Prepare to call pocupdate_close: - call manual close
** for the line level.
*/
IF (po_autocreate_params.g_mode = 'ADD') THEN
IF (po_autocreate_params.g_document_subtype = 'RELEASE') THEN
NULL;
SELECT po_line_locations_s.nextval INTO x_line_location_id FROM sys.dual;
p_message => 'Create shipment: Before insert into po line locations');
INSERT
INTO po_line_locations_draft_all --
(
draft_id,
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,
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,
match_option,
country_of_origin_code,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_accepted,
secondary_quantity_rejected,
secondary_quantity_cancelled,
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 --
,
clm_period_perf_end_date ,
clm_period_perf_start_date,
--CLM Phase4 Changes
clm_delivery_period,
clm_promise_period,
clm_pop_duration,
clm_delivery_period_uom,
clm_promise_period_uom,
clm_pop_duration_uom
)
VALUES
(
po_autocreate_params.g_draft_id,
x_line_location_id,
p_lines.last_update_date_tbl(i),
p_lines.last_updated_by_tbl(i),
p_lines.po_header_id_tbl(i),
p_lines.creation_date_tbl(i),
p_lines.created_by_tbl(i),
p_lines.last_update_login_tbl(i),
p_lines.po_line_id_tbl(i),
x_quantity, --interface.quantity,
0,
0,
0,
0,
0,
p_lines.amount_tbl(i), -- amount --
0, -- amount_received --
0, -- amount_accepted --
0, -- amount_rejected --
0, -- amount_billed --
0, -- amount_cancelled --
x_ship_to_location_id,
p_lines.need_by_date_tbl(i),
l_promised_date,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_header_id_tbl(i)),
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_line_id_tbl(i)),
p_lines.note_to_receiver_tbl(i),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'N'),
'',
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 'CLOSED FOR INVOICE',
DECODE(po_autocreate_params.g_interface_source_code,
'CONSUMPTION_ADVICE', 'CLOSED FOR RECEIVING' ,
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'OPEN'))),
x_closed_reason,
NVL(x_price,p_lines.unit_price_tbl(i)),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'N'),
DECODE(po_autocreate_params.g_document_type,
'RFQ', 'RFQ',
DECODE(po_autocreate_params.g_document_subtype,'RELEASE','BLANKET',po_autocreate_params.g_document_subtype)),
p_lines.shipment_num_tbl(i),
DECODE(po_autocreate_params.g_interface_source_code,
'CONSUMPTION_ADVICE', 'N' ,
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 'N',
DECODE(p_lines.drop_ship_flag_tbl(i),
'Y', 'N',
DECODE(x_order_type_lookup_code,
'FIXED PRICE','N',
'RATE','N',
DECODE(po_autocreate_params.g_document_type,
'RFQ', NVL(p_lines.it_inspect_req_flag_tbl(i),
NVL(po_autocreate_params.g_sys.inspection_required_flag,'N')),
NVL(p_lines.it_inspect_req_flag_tbl(i),
NVL(po_autocreate_params.g_vendor_inspect_req_flag,
NVL(po_autocreate_params.g_sys.inspection_required_flag,'N'))))) ) ) ),
DECODE(po_autocreate_params.g_interface_source_code,
'CONSUMPTION_ADVICE', 'N' , -- CONSIGNED FPI
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 'N',
DECODE(po_autocreate_params.g_document_type,
'RFQ', NVL(p_lines.receipt_required_flag_tbl(i),
NVL(p_lines.receipt_required_flag_tbl(i),
NVL(po_autocreate_params.g_sys.receiving_flag,'N'))),
NVL(p_lines.it_receipt_req_flag_tbl(i), NVL(p_lines.receipt_required_flag_tbl(i),
NVL(po_autocreate_params.g_vendor_receipt_req_flag, NVL(po_autocreate_params.g_sys.receiving_flag,'N'))))
) ) ),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_days_early_recpt_tbl(i)),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_days_late_recpt_tbl(i)),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_enforce_shipto_loc_code_tbl(i)),
p_lines.dest_organization_id_tbl(i), -- ship to org
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 100 , -- CONSIGNED FPI
(DECODE(po_autocreate_params.g_document_type,
'RFQ', '',
(DECODE(p_lines.hd_pcard_id_tbl(i),
NULL, NVL(p_lines.it_invoice_tolerance_tbl(i),
po_autocreate_params.g_sys.invoice_close_tolerance), 100))))),
DECODE(po_autocreate_params.g_interface_source_code,
'CONSUMPTION_ADVICE', 100 , -- CONSIGNED FPI
(DECODE(po_autocreate_params.g_document_type,
'RFQ', '', NVL(p_lines.it_rcv_tolerance_tbl(i),
po_autocreate_params.g_sys.receive_close_tolerance)))),
DECODE(p_lines.txn_flow_header_id_tbl(i), NULL, --
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 'N' , -- CONSIGNED FPI
DECODE(po_autocreate_params.g_document_type,
'RFQ', '',
DECODE( p_lines.hd_pcard_id_tbl(i),
NULL, DECODE(p_lines.destination_type_code_tbl(i),
'EXPENSE',DECODE(NVL(p_lines.it_receipt_req_flag_tbl(i),
NVL(p_lines.receipt_required_flag_tbl(i),
NVL(po_autocreate_params.g_vendor_receipt_req_flag,
NVL(po_autocreate_params.g_sys.receiving_flag,'N')))),
'N', 'N',
DECODE(po_autocreate_params.g_sys.expense_accrual_code,
'PERIOD END', 'N', 'Y')),
'Y'),'N'))), 'Y'), --
DECODE(po_autocreate_params.g_document_type, 'RFQ','', p_lines.rc_subst_receipt_flag_tbl(i)),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_receiving_routing_id_tbl(i)),
p_lines.rc_qty_rcv_tolerance_tbl(i),
p_lines.rc_qty_rcv_exception_code_tbl(i),
DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_receipt_days_excep_code_tbl(i)),
'',
'',
'',
'',
NVL(x_po_uom,p_lines.unit_of_measure_tbl(i)),
DECODE(po_autocreate_params.g_document_type,'RFQ',to_date(NULL),l_promised_date+p_lines.rc_days_late_recpt_tbl(i)),
DECODE(po_autocreate_params.g_interface_source_code,
'CONSUMPTION_ADVICE', 'P' , -- CONSIGNED FPI
DECODE(p_lines.consigned_flag_tbl(i),
'Y', 'P',
DECODE(po_autocreate_params.g_document_type,
'RFQ', '',
po_autocreate_params.g_vendor_invoice_match_option) ) ),
x_country_of_origin_code,
x_secondary_unit_of_measure,
x_secondary_quantity,
p_lines.preferred_grade_tbl(i),
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),
p_lines.vmi_flag_tbl(i) , -- VMI FPH
p_lines.drop_ship_flag_tbl(i), --
p_lines.consigned_flag_tbl(i), -- CONSIGNED FPI
p_lines.txn_flow_header_id_tbl(i), --
po_autocreate_params.g_purchasing_ou_id --
,
DECODE(po_autocreate_params.g_interface_source_code,'CONSUMPTION_ADVICE', sysdate,NULL) --- Closed_for_receiving_date
,
DECODE(p_lines.consigned_flag_tbl(i), 'Y', sysdate,NULL ) --- Closed_for_invoice_date
,
x_order_type_lookup_code ,
l_matching_basis ,
l_outsourced_assembly --
,
nvl2(po_autocreate_params.g_calculate_tax_flag, 'CREATE', NULL) --
,
p_lines.clm_period_perf_end_date_tbl(i) ,
p_lines.clm_period_perf_start_date_tbl(i),
--CLM Phase4 Changes
p_lines.clm_delivery_period_tbl(i),
p_lines.clm_promise_period_tbl(i),
p_lines.clm_pop_duration_tbl(i),
p_lines.clm_delivery_period_uom_tbl(i),
p_lines.clm_promise_period_uom_tbl(i),
p_lines.clm_pop_duration_uom_tbl(i)
);
p_message => 'Create shipment: After insert into po line locations DRAFT');
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_draft_all pol
WHERE pol.po_line_id = p_lines.po_line_id_tbl(i)
AND draft_id = po_autocreate_params.g_draft_id;
--SQL WHAT: Insert information for default payitem into
-- po_line_locations interface table
--SQL WHY : We will use line_locations_interface as a
-- common entry point for payitems - whether they come from
-- sourcing or we default them from scratch here.
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 ,
promised_date
)
VALUES
(
PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL ,
p_lines.intf_header_id_tbl(i) ,
p_lines.intf_line_id_tbl(i) ,
l_payitem_quantity ,
l_payitem_amount ,
l_payitem_price ,
NULL ,
l_payment_type ,
1 ,
p_lines.need_by_date_tbl(i) ,
p_lines.promised_date_tbl(i)
/* 11840142 added promised 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 ,
p_lines.intf_header_id_tbl(i) ,
p_lines.intf_line_id_tbl(i) ,
l_line_quantity ,
l_line_amount ,
l_line_unit_price ,
'DELIVERY' ,
'STANDARD' ,
p_lines.item_desc_tbl(i) ,
1 ,
p_lines.need_by_date_tbl(i) ,
p_lines.promised_date_tbl(i)
);
--SQL WHAT: Insert information for advance payitem into
-- po_line_locations_interface table
--SQL WHY : We will use line_locations_interface as a
-- common entry point for payitems, including ones we create
-- behind the scenes
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 ,
p_lines.intf_header_id_tbl(i) ,
p_lines.intf_line_id_tbl(i) ,
NULL ,
p_lines.advance_amount_tbl(i) ,
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, p_lines.dest_organization_id_tbl(i)),
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, p_lines.item_desc_tbl(i)),
polli.unit_of_measure = NVL(polli.unit_of_measure, p_lines.unit_of_measure_tbl(i))
WHERE polli.interface_line_id = p_lines.intf_line_id_tbl(i);
SELECT prl.tax_code_id ,
NVL(prl.tax_user_override_flag,'N') ,
NVL(prl.tax_status_indicator,'SYSTEM') ,
NVL(prl.org_id, po_autocreate_params.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 = p_lines.requisition_line_id_tbl(i);
PO_LOG.stmt(d_module, d_progress, 'Inserting payitem into po_line_locations_all');
-- insert payitem into po_line_locations_all
--SQL WHAT: Insert payitem, using info in po_line_locations_interface
--SQL WHY : This allows us to insert all payitems in one location.
INSERT
INTO po_line_locations_draft_all
(
draft_id ,
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 --
,
clm_period_perf_end_date ,
clm_period_perf_start_date,
--CLM Phase4 Changes
clm_delivery_period,
clm_promise_period,
clm_pop_duration,
clm_delivery_period_uom,
clm_promise_period_uom,
clm_pop_duration_uom
)
VALUES
(
po_autocreate_params.g_draft_id ,
PO_LINE_LOCATIONS_S.nextval ,
p_lines.last_update_date_tbl(i) ,
p_lines.last_updated_by_tbl(i) ,
p_lines.po_header_id_tbl(i) ,
p_lines.creation_date_tbl(i) ,
p_lines.created_by_tbl(i) ,
p_lines.last_update_login_tbl(i) ,
p_lines.po_line_id_tbl(i) ,
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 ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_header_id_tbl(i)) ,
DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_line_id_tbl(i)) ,
p_lines.note_to_receiver_tbl(i) ,
'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(p_lines.it_receipt_req_flag_tbl(i),
po_autocreate_params.g_vendor_receipt_req_flag,
po_autocreate_params.g_sys.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(p_lines.it_invoice_tolerance_tbl(i), po_autocreate_params.g_sys.invoice_close_tolerance, 100) ,
DECODE(line_location_rec.payment_type,
'MILESTONE', 0, COALESCE(p_lines.it_rcv_tolerance_tbl(i), po_autocreate_params.g_sys.receive_close_tolerance, 100)) ,
DECODE(line_location_rec.shipment_type, -- acrrue_on_receipt_flag
'PREPAYMENT', 'N',
DECODE(COALESCE(p_lines.it_receipt_req_flag_tbl(i),
p_lines.receipt_required_flag_tbl(i),
po_autocreate_params.g_vendor_receipt_req_flag,
po_autocreate_params.g_sys.receiving_flag, 'N'),
'N', 'N',
DECODE(po_autocreate_params.g_sys.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 ,
po_autocreate_params.g_vendor_invoice_match_option ,
l_country_of_origin_code ,
NULL -- vmi_flag
,
NULL -- drop_ship_flag
,
NULL -- consigned_flag
,
p_lines.txn_flow_header_id_tbl(i) ,
po_autocreate_params.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(po_autocreate_params.g_calculate_tax_flag, 'CREATE', NULL) --
,
p_lines.clm_period_perf_end_date_tbl(i) ,
p_lines.clm_period_perf_start_date_tbl(i),
--CLM Phase4 Changes
p_lines.clm_delivery_period_tbl(i),
p_lines.clm_promise_period_tbl(i),
p_lines.clm_pop_duration_tbl(i),
p_lines.clm_delivery_period_uom_tbl(i),
p_lines.clm_promise_period_uom_tbl(i),
p_lines.clm_pop_duration_uom_tbl(i)
)
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 PO_LINE_LOCATIONS_S.currval
INTO x_line_location_id
FROM sys.dual;
inserts/updates the PO_LINE_LOCATIONS_DRAFT_ALL.
Parameters:
p_lines IN OUT Derived Line data after lines processing
Returns:
None
Testing:
None
Caller of the Procedure:
PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
==============================================================================*/
PROCEDURE create_pricebreak_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
IS
l_row_id VARCHAR2(18) := NULL;
l_tax_attribute_update_code PO_LINE_LOCATIONS_ALL.tax_attribute_update_code%type; --
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_draft_all pol
WHERE pol.po_line_id = p_lines.po_line_id_tbl(i);
l_tax_attribute_update_code := 'CREATE';
INSERT
INTO po_line_locations_draft_all
(
draft_id,
line_location_id,
last_update_date,
last_updated_by,
po_header_id,
po_line_id,
last_update_login,
creation_date,
created_by,
quantity,
quantity_received,
quantity_accepted,
quantity_rejected,
quantity_billed,
quantity_cancelled,
unit_meas_lookup_code,
po_release_id,
ship_to_location_id,
ship_via_lookup_code,
need_by_date,
promised_date,
last_accept_date,
price_override,
encumbered_flag,
encumbered_date,
fob_lookup_code,
freight_terms_lookup_code,
taxable_flag,
calculate_tax_flag,
from_header_id,
from_line_id,
from_line_location_id,
start_date,
end_date,
lead_time,
lead_time_unit,
price_discount,
terms_id,
approved_flag,
approved_date,
closed_flag,
cancel_flag,
cancelled_by,
cancel_date,
cancel_reason,
firm_status_lookup_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
inspection_required_flag,
receipt_required_flag,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
invoice_close_tolerance,
receive_close_tolerance,
ship_to_organization_id,
shipment_num,
source_shipment_id,
shipment_type,
closed_code,
government_context,
receiving_routing_id,
accrue_on_receipt_flag,
closed_reason,
closed_date,
closed_by,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
country_of_origin_code,
match_option,
note_to_receiver,
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_accepted,
secondary_quantity_rejected,
secondary_quantity_cancelled,
consigned_flag,
/* CONSIGNED FPI */
amount, --
transaction_flow_header_id, --< Shared Proc FPJ >
manual_price_change_flag --< Manual Price Override FPJ >
,
shipment_closed_date ,
closed_for_receiving_date ,
closed_for_invoice_date ,
Org_Id --
,
value_basis --
,
matching_basis --
,
outsourced_assembly --
,
tax_attribute_update_code --
,
clm_period_perf_end_date ,
clm_period_perf_start_date,
--CLM Phase4 Changes
clm_delivery_period,
clm_promise_period,
clm_pop_duration,
clm_delivery_period_uom,
clm_promise_period_uom,
clm_pop_duration_uom
)
VALUES
(
po_autocreate_params.g_draft_id,
x_Line_Location_Id,
p_lines.last_update_date_tbl(i),
p_lines.last_updated_by_tbl(i),
p_lines.Po_Header_Id_tbl(i),
p_lines.po_Line_Id_tbl(i),
p_lines.Last_Update_Login_tbl(i),
p_lines.creation_Date_tbl(i),
p_lines.created_By_tbl(i),
p_lines.quantity_tbl(i),
0, --quantity_received
0, --Quantity_Accepted
0, --Quantity_Rejected
0, --Quantity_Billed
0, --Quantity_Cancelled,
p_lines.unit_of_measure_tbl(i), --unit of measure
NULL, -- release_id
p_lines.line_Ship_To_Loc_Id_tbl(i),
po_autocreate_params.g_vendor_Ship_Via_Lookup_Code,
NULL, --Need_By_Date
NULL, --Promised_Date
NULL, --Last_Accept_Date
p_lines.unit_price_tbl(i), --Price_override
'N', --Encumbered flag
NULL, --Encumbered_Date
NULL, --Fob_Lookup_Code
NULL, --Freight_Terms_Lookup_Code
'N', --Taxable_Flag
NULL, --Calculate Tax Flag
NULL, --X_From_Header_Id
NULL, --X_From_Line_Id
NULL, --X_From_Line_Location_Id
p_lines.effective_date_tbl(i), --X_Start_Date
p_lines.expiration_date_tbl(i), --X_End_Date
NULL, --X_Lead_Time,
NULL, --X_Lead_Time_Unit,
p_lines.Price_Discount_tbl(i),
p_lines.Terms_Id_tbl(i),
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,
p_lines.line_Ship_To_Org_Id_tbl(i),
p_lines.Shipment_Num_tbl(i),
NULL, --X_Source_Shipment_Id,
p_lines.Shipment_Type_tbl(i),
'OPEN', --X_Closed_Code,
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
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 --
p_lines.amount_tbl(i), --X_Amount --
NULL, -- p_transaction_flow_header_id
NULL, -- p_manual_price_change_flag
NULL, --shipment Closed Code
NULL, -- closed for receiving Date
NULL, -- closed for invoice date
p_lines.org_id_tbl(i), --
l_value_basis, --
NULL, -- : matching basis
l_outsourced_assembly --
,
l_tax_attribute_update_code ,
p_lines.clm_period_perf_end_date_tbl(i) ,
p_lines.clm_period_perf_start_date_tbl(i),
--CLM Phase4 Changes
p_lines.clm_delivery_period_tbl(i),
p_lines.clm_promise_period_tbl(i),
p_lines.clm_pop_duration_tbl(i),
p_lines.clm_delivery_period_uom_tbl(i),
p_lines.clm_promise_period_uom_tbl(i),
p_lines.clm_pop_duration_uom_tbl(i)
);
Name: update_req_lines
Pre-reqs:
PO shipment has been created
Modifies:
PO_REQUISITION_LINES_ALL
Locks:
None
Function:
Updates the Requistion lines' Line Location Id, Reqs in Pool flag for autocreated lines.
Parameters:
p_lines IN Derived Line data after lines processing
p_lines.requistion_line_id_tbl
p_lines.line_location_id_tbl are used.
Returns:
None
Testing:
None
Caller of the Procedure:
PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
==============================================================================*/
PROCEDURE update_req_lines(p_lines IN PO_AUTOCREATE_TYPES.lines_rec_type)
IS
l_progress VARCHAR2(3) := '000';
l_api_name VARCHAR2(30) := 'update_req_lines';
UPDATE po_requisition_lines_all l
SET
-- po_line_id = p_lines.po_line_id_tbl(i)),
po_line_id = Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',
Decode(Nvl(po_line_id, -999),-999, p_lines.po_line_id_tbl(i),-1), p_lines.po_line_id_tbl(i)),
--line_location_id = p_lines.line_loc_id_tbl(i),
line_location_id = Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',
Decode(Nvl(line_location_id, -999),-999, p_lines.line_loc_id_tbl(i),-1), p_lines.line_loc_id_tbl(i)),
-- Bug: 13948625, update linked_po_count, should be equal to number of req dist if autocreated.
-- Updating count to 1 for Info lines(Funded and NonFunded) and Option lines.
-- Updating count to number of distributions for Priced lines
linked_po_count = Decode((SELECT Count(1) FROM po_req_distributions_all d
WHERE d.requisition_line_id = l.requisition_line_id
AND d.info_line_id IS NULL),
0,1,
(SELECT Count(1) FROM po_req_distributions_all d WHERE d.requisition_line_id = l.requisition_line_id
AND d.info_line_id IS NULL)),
--
reqs_in_pool_flag = 'N',
--
last_update_date = p_lines.last_update_date_tbl(i),
last_updated_by = p_lines.last_updated_by_tbl(i),
last_update_login = p_lines.last_update_login_tbl(i)
WHERE requisition_line_id = p_lines.requisition_line_id_tbl(i);
UPDATE po_requisition_lines_all
SET po_draft_id = po_autocreate_params.g_draft_id,
LINKED_TO_FUND = 'Y'
WHERE requisition_line_id = p_lines.requisition_line_id_tbl(i);
UPDATE po_requisition_lines_all prl
SET PO_LINE_ID = -1,
LINE_LOCATION_ID = -1,
PO_DRAFT_ID = NULL,
LINKED_PO_COUNT = Nvl(LINKED_PO_COUNT,0) + (SELECT Count(*) FROM pon_award_allocations paa1
WHERE paa1.bid_number = l_bid_number
AND paa1.IS_LINKED_PR_LINE_YN = 'Y'
AND prl.REQUISITION_LINE_ID = paa1.ORIG_REQ_LINE_ID),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE prl.REQUISITION_LINE_ID IN (SELECT paa.ORIG_REQ_LINE_ID
FROM pon_award_allocations paa
WHERE paa.bid_number = l_bid_number
AND paa.IS_LINKED_PR_LINE_YN = 'Y')
returning REQUISITION_LINE_ID BULK COLLECT INTO l_req_line_ids_tbl;
p_message => 'Updated the req lines with Po Line Ref. l_count: ' || l_count);
SELECT requisition_header_id INTO l_requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i);
p_message => 'Before update statment l_count: row i is : ' || i || ' header id : ' || l_requisition_header_id);
UPDATE po_requisition_lines_all
SET reqs_in_pool_flag = null,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_header_id = l_requisition_header_id
AND Nvl(reqs_in_pool_flag, 'N') = 'Y'
AND ((requisition_line_id = l_req_line_ids_tbl(i))
OR (group_line_id = l_req_line_ids_tbl(i))
OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num = l_req_line_ids_tbl(i))
OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id )
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = l_req_line_ids_tbl(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)));
p_message => 'Update the req in pool');
END update_req_lines;