[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_dependent_line_acc_flag; -- bug5149827
PROCEDURE verify_action_update;
update_dependent_line_acc_flag; -- bug5149827 - Renamed the procedure
verify_action_update;
PROCEDURE update_dependent_line_acc_flag IS
d_api_name CONSTANT VARCHAR2(30) := 'update_dependent_line_acc_flag';
l_update_flag_value_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
PO_PDOI_UTL.pls_integer_tbl_type();
SELECT interface_line_id,
price_chg_accept_flag,
price_break_flag
BULK COLLECT
INTO l_intf_line_id_tbl,
l_price_chg_accept_flag_tbl,
l_price_break_flag_tbl
FROM po_lines_interface
WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
AND NVL(process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
ORDER BY po_line_id, interface_line_id;
l_update_flag_value_idx_tbl.extend;
l_update_flag_value_idx_tbl(l_update_flag_value_idx_tbl.COUNT) := i;
IF (l_update_flag_value_idx_tbl.COUNT > 0) THEN
FORALL i IN VALUES OF l_update_flag_value_idx_tbl
UPDATE po_lines_interface
SET price_chg_accept_flag = l_price_chg_accept_flag_tbl(i)
WHERE interface_line_id = l_intf_line_id_tbl(i);
UPDATE po_lines_interface lines
SET lines.price_chg_accept_flag =
( SELECT parent_lines.price_chg_accept_flag
FROM po_lines_interface parent_lines
WHERE lines.parent_interface_line_id =
parent_lines.interface_line_id )
WHERE lines.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
AND NVL(lines.process_code, PO_PDOI_CONSTANTS.g_process_code_PENDING) =
PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
AND lines.parent_interface_line_id IS NOT NULL;
PO_LOG.stmt(d_module, d_position, '# of lines updated based on parent_intf_line_id',
SQL%ROWCOUNT);
UPDATE po_lines_interface
SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
WHERE interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
AND process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
AND price_chg_accept_flag = 'N';
END update_dependent_line_acc_flag;
SELECT PHI.interface_header_id,
PHI.process_code,
PHI.request_id
FROM po_headers_interface PHI
WHERE PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
AND NVL(PHI.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
AND (PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
OR
PO_PDOI_PARAMS.g_request.batch_id IS NULL)
AND (PHI.process_code = PO_PDOI_PARAMS.g_request.process_code
OR
( NVL(PO_PDOI_PARAMS.g_request.process_code,
PO_PDOI_CONSTANTS.g_process_code_PENDING) <>
PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
AND
PHI.process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS)
OR
PHI.process_code IS NULL)
AND (PHI.interface_header_id = PO_PDOI_PARAMS.g_request.interface_header_id
OR
PO_PDOI_PARAMS.g_request.interface_header_id IS NULL)
AND (PHI.document_type_code = PO_PDOI_PARAMS.g_request.document_type
OR
PHI.document_type_code IS NULL)
AND (PHI.processing_id IS NULL
OR
PHI.processing_id <> PO_PDOI_PARAMS.g_processing_id)
-- bug5471513
-- Catalog uploaded records should only be processed by catalog upload
-- request
-- bug5463188
-- Buyer acceptance process shouldn't worry about the calling module
AND ( PO_PDOI_PARAMS.g_request.process_code =
PO_PDOI_CONSTANTS.g_process_code_NOTIFIED
OR
DECODE (PHI.interface_source_code,
PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
1, 2) =
DECODE (PO_PDOI_PARAMS.g_request.calling_module,
PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD,
1, 2));
UPDATE po_headers_interface PHI
SET PHI.org_id = PO_PDOI_PARAMS.g_request.org_id
WHERE PHI.batch_id = PO_PDOI_PARAMS.g_request.batch_id
AND PHI.org_id IS NULL;
PO_LOG.stmt(d_module, d_position, 'updatec org id for ' ||
SQL%ROWCOUNT || ' records.');
UPDATE po_headers_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id,
process_code = PO_PDOI_CONSTANTS.g_process_code_IN_PROCESS,
processing_round_num = NULL, -- reset processing number
request_id = FND_GLOBAL.conc_request_id,
approval_status = NVL(approval_status,
PO_PDOI_PARAMS.g_request.approved_status)
WHERE interface_header_id = l_intf_header_id_tbl(i);
PO_LOG.stmt(d_module, d_position, 'after header assignment. Updated ' ||
SQL%ROWCOUNT || ' records');
UPDATE po_lines_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id,
action = DECODE (action,
PO_PDOI_CONSTANTS.g_action_ADD, action,
NULL), -- null out process code unless it is force add
process_code = DECODE (PO_PDOI_PARAMS.g_request.process_code,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
process_code) -- bug5149827
WHERE interface_header_id = l_intf_header_id_tbl(i)
AND (PO_PDOI_PARAMS.g_request.process_code = process_code
OR
( NVL(PO_PDOI_PARAMS.g_request.process_code,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING) <>
PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)))
AND (NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT)
OR
(process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED AND
NVL(price_chg_accept_flag, 'N') = 'Y'))
RETURNING interface_line_id
BULK COLLECT INTO l_intf_line_id_tbl;
PO_LOG.stmt(d_module, d_position, 'after line assignment. Updated ' ||
SQL%ROWCOUNT || ' records');
UPDATE po_line_locations_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id
WHERE interface_line_id = l_intf_line_id_tbl(i);
' Updated ' || SQL%ROWCOUNT || ' records');
UPDATE po_price_diff_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id
WHERE interface_line_id = l_intf_line_id_tbl(i);
'Updated ' || SQL%ROWCOUNT || ' records');
UPDATE po_distributions_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id
WHERE interface_line_id = l_intf_line_id_tbl(i);
'Updated ' || SQL%ROWCOUNT || ' records');
UPDATE po_attr_values_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id
WHERE interface_line_id = l_intf_line_id_tbl(i);
'Updated ' || SQL%ROWCOUNT || ' records');
UPDATE po_attr_values_tlp_interface
SET processing_id = PO_PDOI_PARAMS.g_processing_id
WHERE interface_line_id = l_intf_line_id_tbl(i);
' Updated ' || SQL%ROWCOUNT || ' records');
SELECT interface_header_id, action
BULK COLLECT
INTO l_reject_tbl, l_action_tbl
FROM po_headers_interface
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND (action IS NULL
OR
(PO_PDOI_PARAMS.g_request.document_type IN
(PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) AND
action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_ADD,
PO_PDOI_CONSTANTS.g_ACTION_REPLACE,
PO_PDOI_CONSTANTS.g_ACTION_UPDATE))
OR
(PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD AND
action NOT IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_ADD,
PO_PDOI_CONSTANTS.g_ACTION_UPDATE)));
SELECT interface_header_id,
vendor_name,
vendor_num,
vendor_id,
clm_source_document_disp,
clm_source_document_id,
document_type_code
BULK COLLECT
INTO l_intf_header_id_tbl,
l_vendor_name_tbl,
l_vendor_num_tbl,
l_vendor_id_tbl,
l_clm_source_document_disp_tbl,
l_clm_source_document_id_tbl,
l_doc_type_tbl
FROM po_headers_interface PHI
WHERE vendor_id IS NULL
AND po_header_id IS NULL -- if po_header_id is already provided,
-- skip vendor_id derivation as it is
-- not needed
AND processing_id = PO_PDOI_PARAMS.g_processing_id;
UPDATE po_headers_interface
SET vendor_id = l_vendor_id_tbl(i)
WHERE interface_header_id = l_intf_header_id_tbl(i)
AND l_vendor_id_tbl(i) IS NOT NULL;
SELECT interface_header_id,
vendor_id,
effective_date,
expiration_date,
vendor_doc_num
BULK COLLECT INTO l_interface_header_id_tbl, l_vendor_id_tbl,
l_start_date_tbl, l_end_date_tbl, l_vendor_doc_num_tbl
FROM po_headers_interface
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND action = PO_PDOI_CONSTANTS.g_ACTION_REPLACE;
SELECT po_header_id,
NVL(closed_code, 'OPEN'),
NVL(cancel_flag, 'N'),
NULL
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_closed_code_tbl,
l_orig_cancel_flag_tbl,
l_orig_ga_tbl
FROM po_headers POH
WHERE vendor_id = l_vendor_id_tbl(i)
AND quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(l_end_date_tbl(i), SYSDATE)) <= TRUNC(NVL(end_date, SYSDATE));
SELECT po_header_id,
NVL(closed_code, 'OPEN'),
NVL(cancel_flag, 'N'),
NVL(global_agreement_flag, 'N')
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_closed_code_tbl,
l_orig_cancel_flag_tbl,
l_orig_ga_tbl
FROM po_headers POH
WHERE vendor_id = l_vendor_id_tbl(i)
AND vendor_order_num = l_vendor_doc_num_tbl(i)
AND TRUNC(l_start_date_tbl(i)) >= TRUNC(NVL(start_date, SYSDATE));
UPDATE po_headers_interface
SET original_po_header_id = l_final_orig_header_id_tbl(i)
WHERE interface_header_id = l_final_intf_header_id_tbl(i);
SELECT MAX('Y')
INTO l_rel_exists
FROM DUAL
WHERE EXISTS (SELECT 'Exists std PO ref the orig GA'
FROM po_lines_all POL,
po_headers_all POH
WHERE POL.from_header_id = p_po_header_id
AND POL.po_header_id = POH.po_header_id
AND POH.creation_date >= l_exp_date);
SELECT MAX('Y')
INTO l_rel_exists
FROM DUAL
WHERE EXISTS (SELECT 'release exist after expiration date'
FROM po_releases POR
WHERE POR.po_header_id = p_po_header_id
AND POR.release_date >= l_exp_date);
PROCEDURE verify_action_update IS
d_api_name CONSTANT VARCHAR2(30) := 'verify_action_update';
SELECT interface_header_id,
vendor_id,
effective_date,
expiration_date,
po_header_id,
vendor_doc_num,
document_num
BULK COLLECT
INTO l_interface_header_id_tbl,
l_vendor_id_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_po_header_id_tbl,
l_vendor_doc_num_tbl,
l_document_num_tbl
FROM po_headers_interface
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
SELECT DECODE(MAX(POH.po_header_id), NULL, 'N', 'Y'),
NVL(MAX(POH.consigned_consumption_flag), 'N')
INTO l_existing_header,
l_consigned_consumption_flag
FROM po_headers POH
WHERE POH.po_header_id = l_po_header_id_tbl(i)
AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type;
p_mode => 'CHECK_UPDATEABLE',
p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
p_role => PO_PDOI_PARAMS.g_request.role,
p_skip_cat_upload_chk => l_skip_cat_upload_chk,
x_po_status_rec => l_status_rec,
x_return_status => l_return_status
);
SELECT NVL(MIN(segment1), 'UNKNOWN')
INTO l_doc_num_for_msg_dsp
FROM po_headers_all
WHERE po_header_id = l_po_header_id_tbl(i);
SELECT POH.po_header_id,
NVL(POH.consigned_consumption_flag, 'N')
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_consumption_flag_tbl
FROM po_headers POH
WHERE POH.vendor_id = l_vendor_id_tbl(i)
AND POH.vendor_order_num = l_vendor_doc_num_tbl(i)
AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
AND (POH.type_lookup_code = 'STANDARD'
OR
(POH.type_lookup_code = 'BLANKET'
AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
TRUNC(NVL(POH.start_date, SYSDATE))
AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
TRUNC(nvl(POH.end_date, SYSDATE))))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y';
SELECT POH.po_header_id,
NVL(POH.consigned_consumption_flag, 'N')
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_consumption_flag_tbl
FROM po_headers POH
WHERE POH.vendor_id = l_vendor_id_tbl(i)
AND POH.quote_vendor_quote_number = l_vendor_doc_num_tbl(i)
AND POH.segment1 = NVL(l_document_num_tbl(i), POH.segment1)
AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
AND POH.type_lookup_code = 'QUOTATION'
AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
TRUNC(NVL(POH.start_date, SYSDATE))
AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
TRUNC(nvl(POH.end_date, SYSDATE))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y';
SELECT POH.po_header_id,
NVL(POH.consigned_consumption_flag, 'N')
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_consumption_flag_tbl
FROM po_headers POH
WHERE POH.vendor_id = l_vendor_id_tbl(i)
AND NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR) =
NVL(l_vendor_doc_num_tbl(i),
NVL(POH.vendor_order_num, FND_API.G_MISS_CHAR))
AND POH.segment1 = l_document_num_tbl(i)
AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
AND (POH.type_lookup_code = 'STANDARD'
OR
(POH.type_lookup_code = 'BLANKET'
AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
TRUNC(NVL(POH.start_date, SYSDATE))
AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
TRUNC(nvl(POH.end_date, SYSDATE))))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y';
SELECT POH.po_header_id,
NVL(POH.consigned_consumption_flag, 'N')
BULK COLLECT
INTO l_orig_po_header_id_tbl,
l_orig_consumption_flag_tbl
FROM po_headers POH
WHERE POH.vendor_id = l_vendor_id_tbl(i)
AND NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR) =
NVL(l_vendor_doc_num_tbl(i),
NVL(POH.quote_vendor_quote_number, FND_API.G_MISS_CHAR))
AND POH.segment1 = l_document_num_tbl(i)
AND POH.type_lookup_code = PO_PDOI_PARAMS.g_request.document_type
AND POH.type_lookup_code = 'QUOTATION'
AND TRUNC(NVL(l_start_date_tbl(i), SYSDATE)) >=
TRUNC(NVL(POH.start_date, SYSDATE))
AND TRUNC(nvl(l_end_date_tbl(i), SYSDATE)) <=
TRUNC(nvl(POH.end_date, SYSDATE))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y';
p_mode => 'CHECK_UPDATEABLE',
p_calling_module => PO_DRAFTS_PVT.g_call_mod_PDOI,
p_role => PO_PDOI_PARAMS.g_request.role,
p_skip_cat_upload_chk => l_skip_cat_upload_chk,
x_po_status_rec => l_status_rec,
x_return_status => l_return_status
);
UPDATE po_headers_interface
SET po_header_id = l_final_po_header_id_tbl(i)
WHERE interface_header_id = l_final_intf_header_id_tbl(i);
END verify_action_update;
SELECT POH.po_header_id,
PHI.interface_header_id,
PHI.vendor_doc_num
BULK COLLECT
INTO l_po_header_id_tbl,
l_reject_list,
l_vendor_doc_num_tbl
FROM po_headers POH,
po_headers_interface PHI
WHERE POH.vendor_id = PHI.vendor_id
AND POH.quote_vendor_quote_number = PHI.vendor_doc_Num
AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
TRUNC (NVL(POH.start_date, SYSDATE))
AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
TRUNC (NVL(POH.end_date, SYSDATE))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y'
AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_ADD);
SELECT POH.po_header_id,
PHI.interface_header_id,
PHI.vendor_doc_num
BULK COLLECT
INTO l_po_header_id_tbl,
l_reject_list,
l_vendor_doc_num_tbl
FROM po_headers POH,
po_headers_interface PHI
WHERE POH.vendor_id = PHI.vendor_id
AND POH.vendor_order_num = PHI.vendor_doc_Num
AND TRUNC (NVL(PHI.effective_date, SYSDATE)) >=
TRUNC (NVL(POH.start_date, SYSDATE))
AND TRUNC (NVL(PHI.expiration_date, SYSDATE)) <=
TRUNC (NVL(POH.end_date, SYSDATE))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y'
AND PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
AND PHI.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_ADD);
UPDATE po_headers_interface
SET po_header_id = PO_HEADERS_S.nextval
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND po_header_id IS NULL
AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_ADD,
PO_PDOI_CONSTANTS.g_ACTION_REPLACE);
SELECT rowid
FROM po_lines_interface
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND NVL(line_loc_populated_flag, 'N') = 'N';
SELECT PLLI.interface_line_id,
PLLI.interface_line_location_id
FROM po_line_locations_interface PLLI,
po_lines_interface PLI
WHERE PLLI.processing_id = PO_PDOI_PARAMS.g_processing_id
AND PLLI.interface_line_id = PLI.interface_line_id
AND NVL(PLI.line_loc_populated_flag, 'N') = 'N';
INSERT INTO po_line_locations_interface
(
interface_line_location_id,
interface_header_id,
interface_line_id,
processing_id,
process_code,
line_location_id,
shipment_type,
shipment_num,
ship_to_organization_id,
ship_to_organization_code,
ship_to_location_id,
ship_to_location,
terms_id,
payment_terms,
qty_rcv_exception_code,
freight_carrier,
fob,
freight_terms,
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,
receiving_routing_id,
receiving_routing,
accrue_on_receipt_flag,
firm_flag,
need_by_date,
promised_date,
from_line_location_id,
inspection_required_flag,
receipt_required_flag,
source_shipment_id,
note_to_receiver,
transaction_flow_header_id,
quantity,
price_discount,
start_date,
end_date,
price_override,
lead_time,
lead_time_unit,
amount,
secondary_quantity,
secondary_unit_of_measure,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
unit_of_measure,
preferred_grade,
taxable_flag,
tax_code_id,
tax_name,
qty_rcv_tolerance
)
SELECT po_line_locations_interface_s.nextval,
PLI.interface_header_id,
PLI.interface_line_id,
PLI.processing_id,
PLI.process_code,
PLI.line_location_id,
PLI.shipment_type,
PLI.shipment_num,
PLI.ship_to_organization_id,
PLI.ship_to_organization_code,
PLI.ship_to_location_id,
PLI.ship_to_location,
PLI.terms_id,
PLI.payment_terms,
PLI.qty_rcv_exception_code,
PLI.freight_carrier,
PLI.fob,
PLI.freight_terms,
PLI.enforce_ship_to_location_code,
PLI.allow_substitute_receipts_flag,
PLI.days_early_receipt_allowed,
PLI.days_late_receipt_allowed,
PLI.receipt_days_exception_code,
PLI.invoice_close_tolerance,
PLI.receive_close_tolerance,
PLI.receiving_routing_id,
PLI.receiving_routing,
PLI.accrue_on_receipt_flag,
PLI.firm_flag,
PLI.need_by_date,
PLI.promised_date,
PLI.from_line_location_id,
PLI.inspection_required_flag,
PLI.receipt_required_flag,
PLI.source_shipment_id,
PLI.note_to_receiver,
PLI.transaction_flow_header_id,
PLI.quantity,
PLI.price_discount,
PLI.effective_date,
PLI.expiration_date,
PLI.unit_price,
PLI.lead_time,
PLI.lead_time_unit,
PLI.amount,
PLI.secondary_quantity,
PLI.secondary_unit_of_measure,
PLI.shipment_attribute_category,
PLI.shipment_attribute1,
PLI.shipment_attribute2,
PLI.shipment_attribute3,
PLI.shipment_attribute4,
PLI.shipment_attribute5,
PLI.shipment_attribute6,
PLI.shipment_attribute7,
PLI.shipment_attribute8,
PLI.shipment_attribute9,
PLI.shipment_attribute10,
PLI.shipment_attribute11,
PLI.shipment_attribute12,
PLI.shipment_attribute13,
PLI.shipment_attribute14,
PLI.shipment_attribute15,
PLI.creation_date,
PLI.created_by,
PLI.last_update_date,
PLI.last_updated_by,
PLI.last_update_login,
PLI.request_id,
PLI.program_application_id,
PLI.program_id,
PLI.program_update_date,
PLI.unit_of_measure,
PLI.preferred_grade,
PLI.taxable_flag,
PLI.tax_code_id,
PLI.tax_name,
PLI.qty_rcv_tolerance
FROM po_lines_interface PLI
WHERE PLI.rowid = l_rowid_tbl(i);
SELECT Nvl(DECODE(PHI.po_header_id,NULL,PHI.style_id,
(SELECT PH.style_id FROM po_headers_all PH
WHERE PH.po_header_id = PHI.po_header_id)),1),
-- In 'UPDATE' mode, the style_id corresponding to the base PO
-- needs to be considered.
PLI.interface_line_id,
po_line_locations_interface_s.currval
INTO l_style_id, l_interface_line_id, l_interface_line_location_id
FROM po_headers_interface PHI,
po_lines_interface PLI
WHERE PLI.rowid = l_rowid_tbl(i)
AND PLI.interface_header_id = PHI.interface_header_id;
UPDATE po_line_locations_interface
SET payment_type = DECODE(quantity,NULL,'LUMPSUM','MILESTONE'),
price_override = DECODE(quantity,NULL,NULL,0)
WHERE interface_line_location_id = l_interface_line_location_id;
UPDATE po_line_locations_interface
SET shipment_type = 'PREPAYMENT',
shipment_num = DECODE(shipment_num,NULL,1,shipment_num)
WHERE interface_line_location_id = l_interface_line_location_id;
UPDATE po_distributions_interface
SET prevent_encumbrance_flag = 'Y'
WHERE interface_line_id = (SELECT PLI.interface_line_id
FROM po_lines_interface PLI
WHERE PLI.rowid = l_rowid_tbl(i));
UPDATE po_distributions_interface
SET interface_line_location_id = l_intf_line_loc_tbl(i)
WHERE interface_line_id = l_intf_line_tbl(i)
AND interface_line_location_id IS NULL; -- PDOI for Complex PO Project
UPDATE po_price_diff_interface
SET interface_line_location_id = l_intf_line_loc_tbl(i)
WHERE interface_line_id = l_intf_line_tbl(i);
UPDATE po_distributions_interface pd1
SET (charge_account_id,
charge_account_segment1,
charge_account_segment2,
charge_account_segment3,
charge_account_segment4,
charge_account_segment5,
charge_account_segment6,
charge_account_segment7,
charge_account_segment8,
charge_account_segment9,
charge_account_segment10,
charge_account_segment11,
charge_account_segment12,
charge_account_segment13,
charge_account_segment14,
charge_account_segment15,
charge_account_segment16,
charge_account_segment17,
charge_account_segment18,
charge_account_segment19,
charge_account_segment20,
charge_account_segment21,
charge_account_segment22,
charge_account_segment23,
charge_account_segment24,
charge_account_segment25,
charge_account_segment26,
charge_account_segment27,
charge_account_segment28,
charge_account_segment29,
charge_account_segment30) =
(SELECT charge_account_id,
charge_account_segment1,
charge_account_segment2,
charge_account_segment3,
charge_account_segment4,
charge_account_segment5,
charge_account_segment6,
charge_account_segment7,
charge_account_segment8,
charge_account_segment9,
charge_account_segment10,
charge_account_segment11,
charge_account_segment12,
charge_account_segment13,
charge_account_segment14,
charge_account_segment15,
charge_account_segment16,
charge_account_segment17,
charge_account_segment18,
charge_account_segment19,
charge_account_segment20,
charge_account_segment21,
charge_account_segment22,
charge_account_segment23,
charge_account_segment24,
charge_account_segment25,
charge_account_segment26,
charge_account_segment27,
charge_account_segment28,
charge_account_segment29,
charge_account_segment30
FROM po_distributions_interface pd2
WHERE pd2.interface_line_id = l_intf_line_tbl(i)
AND (charge_account_id IS NOT NULL OR charge_account_segment1 IS NOT NULL)
AND ROWNUM = 1)
WHERE pd1.interface_line_id = l_intf_line_tbl(i)
AND pd1.charge_account_id IS NULL
AND pd1.charge_account_segment1 IS NULL;
UPDATE po_lines_interface
SET line_loc_populated_flag = 'S'
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND NVL(line_loc_populated_flag, 'N') = 'N';
INSERT INTO po_line_locations_interface
(
interface_line_location_id,
interface_header_id,
interface_line_id,
processing_id,
process_code,
line_location_id,
shipment_type,
shipment_num,
ship_to_organization_id,
ship_to_organization_code,
ship_to_location_id,
ship_to_location,
terms_id,
payment_terms,
qty_rcv_exception_code,
freight_carrier,
fob,
freight_terms,
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,
receiving_routing_id,
receiving_routing,
accrue_on_receipt_flag,
firm_flag,
need_by_date,
promised_date,
from_line_location_id,
inspection_required_flag,
receipt_required_flag,
source_shipment_id,
note_to_receiver,
transaction_flow_header_id,
quantity,
price_discount,
start_date,
end_date,
price_override,
lead_time,
lead_time_unit,
amount,
secondary_quantity,
secondary_unit_of_measure,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
unit_of_measure,
payment_type,
value_basis,
matching_basis,
preferred_grade,
taxable_flag,
tax_code_id,
tax_name,
qty_rcv_tolerance
)
SELECT po_line_locations_interface_s.nextval,
PLI.interface_header_id,
PLI.interface_line_id,
PLI.processing_id,
PLI.process_code,
PLI.line_location_id,
'PREPAYMENT', -- shipment_type
0, -- shipment_num
PLI.ship_to_organization_id,
PLI.ship_to_organization_code,
PLI.ship_to_location_id,
PLI.ship_to_location,
PLI.terms_id,
PLI.payment_terms,
PLI.qty_rcv_exception_code,
PLI.freight_carrier,
PLI.fob,
PLI.freight_terms,
PLI.enforce_ship_to_location_code,
PLI.allow_substitute_receipts_flag,
PLI.days_early_receipt_allowed,
PLI.days_late_receipt_allowed,
PLI.receipt_days_exception_code,
PLI.invoice_close_tolerance,
PLI.receive_close_tolerance,
PLI.receiving_routing_id,
PLI.receiving_routing,
PLI.accrue_on_receipt_flag,
PLI.firm_flag,
NULL, -- need_by_date
NULL, -- promised_date
PLI.from_line_location_id,
PLI.inspection_required_flag,
'N', -- receipt_required_flag
PLI.source_shipment_id,
PLI.note_to_receiver,
PLI.transaction_flow_header_id,
NULL, -- quantity
NULL, -- price_discount
PLI.effective_date,
PLI.expiration_date,
NULL, -- unit_price
PLI.lead_time,
PLI.lead_time_unit,
PLI.advance_amount, -- amount
NULL, -- secondary_quantity
NULL, -- secondary_unit_of_measure
PLI.shipment_attribute_category,
PLI.shipment_attribute1,
PLI.shipment_attribute2,
PLI.shipment_attribute3,
PLI.shipment_attribute4,
PLI.shipment_attribute5,
PLI.shipment_attribute6,
PLI.shipment_attribute7,
PLI.shipment_attribute8,
PLI.shipment_attribute9,
PLI.shipment_attribute10,
PLI.shipment_attribute11,
PLI.shipment_attribute12,
PLI.shipment_attribute13,
PLI.shipment_attribute14,
PLI.shipment_attribute15,
PLI.creation_date,
PLI.created_by,
PLI.last_update_date,
PLI.last_updated_by,
PLI.last_update_login,
PLI.request_id,
PLI.program_application_id,
PLI.program_id,
PLI.program_update_date,
NULL, -- unit_of_measure
'ADVANCE', -- payment_type
'FIXED PRICE', -- value_basis
'AMOUNT', -- matching_basis
PLI.preferred_grade,
PLI.taxable_flag,
PLI.tax_code_id,
PLI.tax_name,
PLI.qty_rcv_tolerance
FROM po_lines_interface PLI
WHERE PLI.interface_line_id = p_interface_line_id
AND Nvl(PLI.advance_amount,0) > 0;
SELECT po_line_locations_interface_s.CURRVAL
INTO l_interface_line_location_id
FROM DUAL;
INSERT INTO po_distributions_interface
(
interface_distribution_id,
interface_header_id,
interface_line_id,
interface_line_location_id,
processing_id,
process_code,
distribution_num,
rate_date,
amount_ordered,
destination_type_code,
accrue_on_receipt_flag,
prevent_encumbrance_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT po_distributions_interface_s.nextval,
PLL.interface_header_id,
PLL.interface_line_id,
PLL.interface_line_location_id,
PLL.processing_id,
PLL.process_code,
1, -- distribution_num
sysdate, -- rate_date
PLL.amount, -- amount_ordered
'EXPENSE', -- destination_type_code
PLL.accrue_on_receipt_flag,
'Y', -- prevent_encumbrance_flag
PLL.creation_date,
PLL.created_by,
PLL.last_update_date,
PLL.last_updated_by,
PLL.last_update_login,
PLL.request_id,
PLL.program_application_id,
PLL.program_id,
PLL.program_update_date
FROM po_line_locations_interface PLL
WHERE PLL.interface_line_location_id = p_interface_line_location_id;
INSERT INTO po_line_locations_interface
(
interface_line_location_id,
interface_header_id,
interface_line_id,
processing_id,
process_code,
line_location_id,
shipment_type,
shipment_num,
ship_to_organization_id,
ship_to_organization_code,
ship_to_location_id,
ship_to_location,
terms_id,
payment_terms,
qty_rcv_exception_code,
freight_carrier,
fob,
freight_terms,
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,
receiving_routing_id,
receiving_routing,
accrue_on_receipt_flag,
firm_flag,
need_by_date,
promised_date,
from_line_location_id,
inspection_required_flag,
receipt_required_flag,
source_shipment_id,
note_to_receiver,
transaction_flow_header_id,
quantity,
price_discount,
start_date,
end_date,
price_override,
lead_time,
lead_time_unit,
amount,
secondary_quantity,
secondary_unit_of_measure,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
unit_of_measure,
payment_type,
preferred_grade,
taxable_flag,
tax_code_id,
tax_name,
qty_rcv_tolerance
)
SELECT po_line_locations_interface_s.nextval,
PLI.interface_header_id,
PLI.interface_line_id,
PLI.processing_id,
PLI.process_code,
PLI.line_location_id,
'STANDARD', -- shipment_type
1, -- shipment_num
PLI.ship_to_organization_id,
PLI.ship_to_organization_code,
PLI.ship_to_location_id,
PLI.ship_to_location,
PLI.terms_id,
PLI.payment_terms,
PLI.qty_rcv_exception_code,
PLI.freight_carrier,
PLI.fob,
PLI.freight_terms,
PLI.enforce_ship_to_location_code,
PLI.allow_substitute_receipts_flag,
PLI.days_early_receipt_allowed,
PLI.days_late_receipt_allowed,
PLI.receipt_days_exception_code,
PLI.invoice_close_tolerance,
PLI.receive_close_tolerance,
PLI.receiving_routing_id,
PLI.receiving_routing,
PLI.accrue_on_receipt_flag,
PLI.firm_flag,
PLI.need_by_date,
PLI.promised_date,
PLI.from_line_location_id,
PLI.inspection_required_flag,
PLI.receipt_required_flag,
PLI.source_shipment_id,
PLI.note_to_receiver,
PLI.transaction_flow_header_id,
PLI.quantity,
PLI.price_discount,
PLI.effective_date,
PLI.expiration_date,
PLI.unit_price,
PLI.lead_time,
PLI.lead_time_unit,
PLI.amount,
PLI.secondary_quantity,
PLI.secondary_unit_of_measure,
PLI.shipment_attribute_category,
PLI.shipment_attribute1,
PLI.shipment_attribute2,
PLI.shipment_attribute3,
PLI.shipment_attribute4,
PLI.shipment_attribute5,
PLI.shipment_attribute6,
PLI.shipment_attribute7,
PLI.shipment_attribute8,
PLI.shipment_attribute9,
PLI.shipment_attribute10,
PLI.shipment_attribute11,
PLI.shipment_attribute12,
PLI.shipment_attribute13,
PLI.shipment_attribute14,
PLI.shipment_attribute15,
PLI.creation_date,
PLI.created_by,
PLI.last_update_date,
PLI.last_updated_by,
PLI.last_update_login,
PLI.request_id,
PLI.program_application_id,
PLI.program_id,
PLI.program_update_date,
PLI.unit_of_measure,
'DELIVERY', -- payment_type
PLI.preferred_grade,
PLI.taxable_flag,
PLI.tax_code_id,
PLI.tax_name,
PLI.qty_rcv_tolerance
FROM po_lines_interface PLI
WHERE PLI.interface_line_id = p_interface_line_id;
SELECT po_line_locations_interface_s.CURRVAL
INTO l_interface_line_location_id
FROM DUAL;
INSERT INTO po_distributions_interface
(
interface_distribution_id,
interface_header_id,
interface_line_id,
interface_line_location_id,
processing_id,
process_code,
distribution_num,
quantity_ordered,
rate_date,
amount_ordered,
destination_type_code,
accrue_on_receipt_flag,
prevent_encumbrance_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT po_distributions_interface_s.nextval,
PLL.interface_header_id,
PLL.interface_line_id,
PLL.interface_line_location_id,
PLL.processing_id,
PLL.process_code,
1, -- distribution_num
PLL.quantity,
sysdate, -- rate_date
PLL.amount, -- amount_ordered
'EXPENSE', -- destination_type_code
PLL.accrue_on_receipt_flag,
'N', -- prevent_encumbrance_flag
PLL.creation_date,
PLL.created_by,
PLL.last_update_date,
PLL.last_updated_by,
PLL.last_update_login,
PLL.request_id,
PLL.program_application_id,
PLL.program_id,
PLL.program_update_date
FROM po_line_locations_interface PLL
WHERE PLL.interface_line_location_id = p_interface_line_location_id;
SELECT PLI.interface_header_id,
PLI.interface_line_id,
PLI.line_num_display,
PLI.clm_info_flag,
phi.po_header_id
BULK COLLECT
INTO l_intf_header_id_tbl,
l_intf_line_id_tbl,
l_line_num_disp_tbl,
l_clm_info_flag_tbl,
l_po_header_id_tbl
FROM po_lines_interface PLI,po_headers_interface phi
WHERE PLI.processing_id = PO_PDOI_PARAMS.g_processing_id
AND PLI.interface_header_id = phi.interface_header_id
ORDER BY PLI.interface_header_id, PLI.line_num_display;
SELECT Max(line_num) INTO max_line_num
FROM po_lines_merge_v
WHERE po_header_id = l_po_header_id_tbl(i)
AND draft_id = -1;
UPDATE po_lines_interface
SET line_num = l_line_num_tbl(i),
group_line_id = l_group_line_id_tbl(i)
WHERE interface_header_id = l_intf_header_id_tbl(i)
AND interface_line_id = l_intf_line_id_tbl(i)
AND l_line_num_tbl(i) IS NOT NULL;
INSERT INTO po_session_gt(KEY, num1, num2, num3, num4, char1, char2)
SELECT l_key,
l_num_list(i), --num1
p_intf_header_id_tbl(i),--num2
p_intf_line_id_tbl(i), --num3
null, --num4
p_line_num_disp_tbl(i), --char1
p_is_line_num_disp_valid(i)--char2
FROM dual;
UPDATE po_session_gt psg1
SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
WHERE Nvl(psg2.char2,'Y') = 'Y'
AND Length(psg2.char1) = 4
AND psg2.char1 = SubStr(psg1.char1,1,4)
AND psg2.num2 = psg1.num2
)
WHERE Nvl(psg1.char2,'Y') = 'Y'
AND Length(psg1.char1) = 6
AND psg1.num3 = p_intf_line_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num4 BULK COLLECT INTO l_index_tbl , l_group_line_id_tbl;
INSERT INTO po_session_gt(KEY, num1, num2, num3, char1, char2, char3, num4)
SELECT l_key,
l_num_list(i), --num1
p_intf_header_id_tbl(i),--num2
p_intf_line_id_tbl(i), --num3
p_line_num_disp_tbl(i), --char1
'Y', --char2
p_clm_info_flag_tbl(i), --char3
p_po_line_id_tbl(i)
FROM dual;
UPDATE po_session_gt psg1
SET char2 = 'N'
WHERE 1 <= ( SELECT Count(num3)
FROM po_session_gt psg2
WHERE psg2.char1 = psg1.char1
AND psg2.num2 = psg1.num2
AND psg2.num3 <> psg1.num3
)
OR psg1.char1 IN ( SELECT line_num_display FROM po_lines_merge_v
WHERE po_header_id = p_po_line_id_tbl(i)
)
AND psg1.num3 = p_intf_line_id_tbl(i);
UPDATE po_session_gt
SET char2 = 'N'
WHERE Length(char1) NOT IN (4,6)
OR
NOT REGEXP_LIKE (SubStr(char1,1,4),'^[[:digit:]]+$') --not a clin or slin
OR
(Length(char1) = 6 --slin
AND Nvl(char3,'N') = 'Y' --info
AND NOT REGEXP_LIKE (SubStr(char1,5,2),'^[[:digit:]]+$') --last two characters not digits
)
OR
(Length(char1) = 6 --slin
AND Nvl(char3,'N') = 'N' --priced
AND NOT REGEXP_LIKE (SubStr(char1,5,2),'^[[:alpha:]]+$') --last two charcters not alphabets
);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char2 BULK COLLECT INTO l_index_tbl , l_is_line_num_disp_valid;