[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
po_headers_interface table and insert records into
po_headers_draft_all table.
PO_AUTO_HEADER_PROCESS_PVT.fetch_headers
PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers
PO_AUTO_HEADER_PROCESS_PVT.validate_headers
PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft
PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft
When the autocreate processing starts we could be in any of the following
scenarios.
I. The process will create a new document.
II. The process will add lines to an already exisitng document.
The document might not have been approved yet.
III. The process might add lines lines to an already approved document.
There might not be any draft.
IV. The process might add lines lines to an already approved document.
There might be a draft already existing.
============================================================================== */
g_pkg_name CONSTANT VARCHAR2(1000) := 'po_autocreate_mainproc_pvt';
delete_flag_tbl PO_TBL_VARCHAR1;
SELECT pla.po_line_id ,
po_autocreate_params.g_draft_id ,
'N' -- Need to check with Vinod.
BULK COLLECT
INTO po_line_id_tbl ,
draft_id_tbl ,
delete_flag_tbl
FROM po_lines_interface pli ,
po_lines_all pla ,
po_headers_interface phi
WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
AND phi.interface_header_id = PLI.interface_header_id
AND PLA.po_header_id = PhI.po_header_id
AND PLI.line_num = PLA.line_num
AND NOT EXISTS
(SELECT 'Y'
FROM po_lines_draft_all pld
WHERE pld.draft_id = po_autocreate_params.g_draft_id
AND pld.po_line_id = PLA.po_line_id
);
SELECT style_id INTO l_style_id
FROM po_headers_all
WHERE po_header_id = po_autocreate_params.g_po_header_id;
po_lines_draft_pkg.sync_draft_from_txn (p_po_line_id_tbl => po_line_id_tbl ,p_draft_id_tbl => draft_id_tbl ,p_delete_flag_tbl => delete_flag_tbl ,x_record_already_exist_tbl => x_record_already_exist_tbl );
SELECT plla.line_location_id ,
po_autocreate_params.g_draft_id ,
'N' -- Need to check with Vinod.
BULK COLLECT
INTO po_line_loc_id_tbl ,
draft_id_tbl ,
delete_flag_tbl
FROM TABLE(po_line_id_tbl) pl ,
po_line_locations_all plla ,
po_lines_interface PLI ,
po_headers_interface phi
WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
AND phi.interface_header_id = PLI.interface_header_id
AND plla.po_header_id = PhI.po_header_id
AND PLI.shipment_num = PLlA.shipment_num
AND plla.po_line_id = pl.column_value
AND NOT EXISTS
(SELECT 'Y'
FROM po_line_locations_draft_all plld
WHERE plld.draft_id = po_autocreate_params.g_draft_id
AND plld.line_location_id = plla.line_location_id
);
p_delete_flag_tbl => delete_flag_tbl ,
x_record_already_exist_tbl => x_record_already_exist_tbl );
SELECT pod.po_distribution_id ,
po_autocreate_params.g_draft_id ,
'N' -- Need to check with Vinod.
BULK COLLECT
INTO po_dist_id_tbl ,
draft_id_tbl ,
delete_flag_tbl
FROM po_distributions_all pod,
TABLE(po_line_loc_id_tbl) pll
WHERE pod.line_location_id = pll.column_value
AND NOT EXISTS
(SELECT 'Y'
FROM po_distributions_draft_all podd
WHERE podd.draft_id = po_autocreate_params.g_draft_id
AND podd.po_distribution_id = pod.po_distribution_id
);
p_delete_flag_tbl => delete_flag_tbl ,
x_record_already_exist_tbl => x_record_already_exist_tbl );
/* Consigned FPI start : split the following select to determine if a new line
is to be created or just a new shipment */
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_MERGE_V PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
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;
SELECT PLL.shipment_num,
PLL.line_location_id
INTO x_po_shipment_num,
x_line_location_to_check
FROM PO_LINE_LOCATIONS_MERGE_V PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND draft_id =PO_AUTOCREATE_PARAMS.g_draft_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;
SELECT PLL.shipment_num,
PLL.line_location_id
INTO x_po_shipment_num,
x_line_location_to_check
FROM PO_LINE_LOCATIONS_MERGE_V PLL --
WHERE PLL.PO_LINE_ID = x_po_line_id
AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_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;
SELECT PLL.shipment_num
INTO x_po_shipment_num
FROM PO_LINE_LOCATIONS_MERGE_V PLL, --
PO_REQUISITION_LINES_ALL PRL, --
PO_SYSTEM_PARAMETERS_ALL PSP --
WHERE PLL.LINE_LOCATION_ID = x_line_location_to_check
AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
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(po_autocreate_params.g_purchasing_ou_id, -99)
AND PLL.ACCRUE_ON_RECEIPT_FLAG = DECODE(l_intf_txn_flow_header_id, NULL, --
DECODE(prl.destination_type_code,
'EXPENSE', DECODE(psp.expense_accrual_code,
'PERIOD END', 'N', DECODE(NVL(l_item_receipt_required_flag,
NVL(l_intf_receipt_required_flag,
NVL(PO_AUTOCREATE_PARAMS.g_vendor_receipt_req_flag,
NVL(PO_AUTOCREATE_PARAMS.g_sys.receiving_flag, 'N')))),
'N', 'N', 'Y')), 'Y'), 'Y') --
AND ( (PLL.PREFERRED_GRADE IS NULL
AND x_preferred_grade IS NULL)
OR (PLL.PREFERRED_GRADE = x_preferred_grade) )
AND NVL(PLL.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI
AND ROWNUM = 1;
** 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 1 =1
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
--CLM Phase-2 Autocreate Change
AND prl.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
--CLM Phase-2 Autocreate Change
AND phi.interface_header_id = po_autocreate_params.g_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(po_autocreate_params.g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(po_autocreate_params.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.
*/
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(po_autocreate_params.g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id,-99) = NVL(po_autocreate_params.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
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
AND rtrim(NVL(PLI.note_to_receiver, '99')) = rtrim(NVL(x_note_to_receiver, '99'))
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
AND ( (PLI.PREFERRED_GRADE IS NULL
AND x_preferred_grade IS NULL)
OR (PLI.PREFERRED_GRADE= x_preferred_grade) )
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.
*/
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(po_autocreate_params.g_purchasing_ou_id, -99) --
AND NVL(fsp.org_id, -99) = NVL(po_autocreate_params.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
AND rtrim(NVL(PLI.note_to_receiver, '99')) = rtrim(NVL(x_note_to_receiver, '99'))
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 ( (PLI.PREFERRED_GRADE IS NULL
AND x_preferred_grade IS NULL)
OR (PLI.PREFERRED_GRADE = x_preferred_grade) )
AND ROWNUM = 1
ORDER BY shipment_num;
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 DEFAULT NULL )
IS
x_shipment_num NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'update_shipment';
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 (po_autocreate_params.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 (po_autocreate_params.g_purchasing_ou_id, -99)
--
AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.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;
po_headers_interface table and insert records into
po_headers_draft_all table.
PO_AUTO_HEADER_PROCESS_PVT.fetch_headers
PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers
PO_AUTO_HEADER_PROCESS_PVT.validate_headers
PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft
PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft
When the autocreate processing starts we could be in any of the following
scenarios.
I. The process will create a new document.
II. The process will add lines to an already exisitng document.
The document might not have been approved yet.
III. The process might add lines lines to an already approved document.
There might not be any draft.
IV. The process might add lines lines to an already approved document.
There might be a draft already existing.
CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
============================================================================== */
PROCEDURE process_headers
IS
l_api_name VARCHAR2(30) := 'process_headers';
* After matching the lines the interface record is updated with the
line number and the shipment number. Rest of the processing is
deferred to the line processing and shipment processing routines.
ALGM:
IF the mode is 'ADD'
Next_Line_Num := Get the latest line number from document + 1
ELSE
Next_Line_Num := 1;
UPDATE the interface with the LINE_NUM;
** 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.
*/
CURSOR interface_lines_temp
IS
SELECT pli.requisition_line_id
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id
ORDER BY pli.requisition_line_id;
SELECT ROWID
FROM po_lines_interface pli
WHERE interface_header_id = po_autocreate_params.g_interface_header_id
ORDER BY bid_line_number;
SELECT pli.action,
pli.requisition_line_id,
pli.line_num,
pli.item_id,
pli.item_description,
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,
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,
pli.drop_ship_flag,
pli.from_header_id,
pli.from_line_id,
pli.consigned_flag,
pli.contract_id,
pli.supplier_ref_number ,
pli.line_num_display ,
pli.group_line_id ,
pli.clm_info_flag ,
pli.clm_option_indicator ,
pli.clm_option_num ,
pli.clm_option_from_date ,
pli.clm_option_to_date ,
pli.clm_funded_flag ,
pli.clm_base_line_num ,
PLI.contract_Type ,
pli.receipt_required_flag
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
)
UNION
SELECT pli.action,
pli.requisition_line_id,
pli.line_num,
pli.item_id,
pli.item_description,
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,
NULL,
NULL,
pli.secondary_unit_of_measure,
pli.preferred_grade,
pli.bid_number,
pli.bid_line_number,
pli.rowid,
pli.vmi_flag,
pli.drop_ship_flag,
pli.from_header_id,
pli.from_line_id,
pli.consigned_flag,
pli.contract_id,
pli.supplier_ref_number ,
pli.line_num_display ,
pli.group_line_id ,
pli.clm_info_flag ,
pli.clm_option_indicator ,
pli.clm_option_num ,
pli.clm_option_from_date ,
pli.clm_option_to_date ,
pli.clm_funded_flag ,
pli.clm_base_line_num ,
PLI.contract_Type ,
pli.receipt_required_flag
FROM po_lines_interface pli
WHERE pli.interface_header_id=x_interface_header_id
AND NOT EXISTS
(SELECT 'x'
FROM po_distributions_interface pdi2
WHERE pdi2.interface_line_id = pli.interface_line_id
)
ORDER BY 4, --pli.item_id,
5, --pli.item_description,
--pli.unit_price,
10,--pli.need_by_date,
2; --pli.requisition_line_id;
SELECT phi.document_num,
phi.document_type_code,
phi.document_subtype,
phi.group_code,
phi.action
INTO x_document_num,
x_document_type_code,
x_document_subtype,
x_group_code,
x_mode
FROM po_headers_interface phi
WHERE phi.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface pli
SET pli.line_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.line_num IS NULL
AND pli.shipment_num IS NULL;
UPDATE po_lines_interface pli
SET pli.shipment_num = 1
WHERE pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL
AND NVL (pli.clm_info_flag, 'N') ='N'
-- Shipments are only for Priced lines
;
UPDATE po_lines_interface pli
SET pli.line_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 NULL
AND pli.shipment_num IS NULL;
UPDATE po_lines_interface pli
SET pli.shipment_num = 1
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL
AND NVL (pli.clm_info_flag, 'N') = 'N' -- Priced line
;
UPDATE po_lines_interface
SET line_num = l_sourcing_temp_num
WHERE rowid = l_rec.ROWID;
UPDATE po_lines_interface
SET shipment_num = 1
WHERE rowid = l_rec.ROWID
AND shipment_num IS NULL
AND NVL (clm_info_flag, 'N') = 'N';
SELECT NVL (MAX (pl.line_num), 0)
INTO x_line_num
FROM po_headers_all ph,
po_lines_merge_v pl
WHERE pl.po_header_id = ph.po_header_id
AND pl.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
AND ph.segment1 = x_document_num
AND ph.type_lookup_code = x_document_subtype
AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99);
UPDATE po_lines_interface pli
SET pli.line_num = x_line_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 NULL
AND pli.shipment_num IS NULL;
UPDATE po_lines_interface pli
SET pli.shipment_num = 1
WHERE pli.requisition_line_id = x_requisition_line_id
AND pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL
AND NVL (pli.clm_info_flag, 'N') = 'N'
-- Shipments are for only Priced lines
;
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 po_lines_interface pli
SET pli.shipment_num = 1
WHERE pli.interface_header_id = x_interface_header_id
AND pli.shipment_num IS NULL
AND NVL (pli.clm_info_flag, 'N') = 'N';
SELECT NVL (MAX (line_num), 0)
INTO l_int_line_num
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id;
UPDATE po_lines_interface
SET line_num = l_int_line_num + 1
WHERE interface_header_id = x_interface_header_id
AND requisition_line_id = x_requisition_line_id
AND line_num IS NULL; --Updating line nums only when they are null, else honor the line nums from interface table
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_merge_v pol,
po_headers_all poh
WHERE poh.segment1 = x_document_num
AND pol.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
AND pol.line_num = x_interface_line_num
AND poh.type_lookup_code = x_document_subtype
AND poh.po_header_id = pol.po_header_id
AND NVL (poh.org_id, -99) = NVL (po_autocreate_params.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 );
SELECT grade_control_flag
INTO l_grade_control_flag
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = po_autocreate_params.g_sys.def_inv_org_id;
SELECT line_num,
po_line_id
INTO x_po_line_num,
x_po_line_id
FROM po_lines_merge_v pol2,
po_headers_all poh,
po_line_types_b plt --
WHERE poh.segment1 = x_document_num
AND pol2.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
AND poh.po_header_id = pol2.po_header_id
AND NVL (poh.org_id, -99) = NVL (po_autocreate_params.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 NVL (pol2.clm_info_flag, 'N') = 'N'
AND pol2.group_line_id IS NULL
AND pol2.clm_base_line_num IS NULL
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 (x_clm_info_flag, 'N') = 'N'
AND x_group_line_id IS NULL
AND x_clm_base_line_num IS NULL
AND NVL (pol.item_id, -1) = NVL (x_item_id, -1)
-- bgu, For one timeitem
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 ) )
--
);
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')
--
--
--autocreate grouping start...Need to match only priced CLINs. Not info CLINs, SLINs or option lines
AND NVL (pli.clm_info_flag, 'N') ='N'
AND pli.group_line_id IS NULL
AND pli.clm_base_line_num IS NULL
AND NVL (x_clm_info_flag, 'N') ='N'
AND x_group_line_id IS NULL
AND x_clm_base_line_num IS NULL
--autocreate grouping end
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'
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'
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'
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)
/*autocreate grouping - Changed the condition from NVL (pli.orig_from_req_flag, 'Y') <> 'N'
to NVL (pli.orig_from_req_flag, 'Y') = 'Y' so that the lines that are soft linked(pli.orig_from_req_flag = 'S') are excluded*/
AND NVL (pli.orig_from_req_flag, 'Y') = 'Y'
--
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 update line_num: Line Id:'||x_requisition_line_id||' Line Num:'||x_po_line_num );
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;
po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, 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_merge_v pl
WHERE pl.po_header_id = ph.po_header_id
AND pl.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
AND ph.segment1 = x_document_num
AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99)
AND ph.type_lookup_code = x_document_subtype;
p_message => 'Before select max line_num from po_lines_interface' );
SELECT NVL (MAX (pli.line_num), 0)
INTO l_int_line_num
FROM po_lines_interface pli
WHERE pli.interface_header_id = x_interface_header_id;
p_message => 'Sourcing to PO: Update Line Num : x_line_num : '|| x_line_num );
UPDATE po_lines_interface pli
SET pli.line_num = x_line_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.shipment_num = 1
WHERE pli.interface_header_id = x_interface_header_id
AND pli.requisition_line_id = x_requisition_line_id
AND NVL (pli.clm_info_flag, 'N') = 'N'
-- Priced Line
AND pli.clm_base_line_num IS NULL;
UPDATE po_lines_interface pli
SET pli.line_num = x_line_num + 1,
pli.shipment_num = 1
WHERE pli.ROWID = x_row_id;
table and insert records into po_lines_draft_all table.
PO_AUTO_LINE_PROCESS_PVT.fetch_lines
PO_AUTO_LINE_PROCESS_PVT.derive_and_default_lines
PO_AUTO_LINE_PROCESS_PVT.validate_lines (Is this required??)
PO_AUTO_LINE_PROCESS_PVT.merge_to_lines_draft
PO_AUTO_LINE_PROCESS_PVT.merge_to_price_diff_draft
PO_AUTO_LINE_PROCESS_PVT.merge_to_attributes_draft
(process attribute values and attribute tlp)
PO_AUTO_LINE_PROCESS_PVT.hanlde_line_attachments
EXMP: After the grouping the document being processed might in the
following state. The Line number is followed by the quantity
on that record.
DOCUMENT DRAFT INTERFACE
======== ===== =========
1 (10) .......1 (12) A - 1 (5)
2 (10) B - 2 (5)
3 (10) .......3 (13) C - 3 (5)
4 (10) D - 2 (5)
5 (10) E - 6 (5)
F - 1 (5)
G - 6 (5)
- We are in ADD TO document flow.
- The document is already approved document.
- Each of the line in the document has 10 qty.
- A Draft is created for the document already. The Line #1 qty is changed
to 12 and the Line #3 qty is changed to 13.
- The interface has six requisition lines 5 qty each.
- The Line A and F matches to the existing document line 1 (which is in draft)
- The Line B and D matches to the existing document line 2 (which is NOT in draft)
- The Line C matches to the existing document line 3 (which is in draft)
- The Line E does not match any line in the document.
- The line G matches to the line E in the interface.
CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
============================================================================== */
PROCEDURE process_lines
IS
l_api_name VARCHAR2(30) := 'process_lines';
table and insert records into po_line_locations_draft_all table.
PO_AUTO_LINE_LOC_PROCESS_PVT.fetch_line_loc
PO_AUTO_LINE_LOC_PROCESS_PVT.derive_and_default_line_loc : most for standard po / complex order
(Country of Origin,Inspection Required,Receipt required,match option)
PO_AUTO_LINE_LOC_PROCESS_PVT.validate_line_loc (??)
PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_line_loc_draft
(Shipments, Pay Items, Price Breaks they can be handled separetly);
PO_AUTO_LINE_LOC_PROCESS_PVT.update_req_lines(x_lines);
table and insert records into po_distributions_draft_all table.
PO_AUTO_DIST_PROCESS_PVT.fetch_dists
PO_AUTO_DIST_PROCESS_PVT.derive_and_default_dists
PO_AUTO_LINE_LOC_PROCESS_PVT.validate_dists (??)
PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_dists_draft
CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
============================================================================== */
PROCEDURE process_distributions
IS
l_api_name VARCHAR2(30) := 'process_distributions';
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;
SELECT NVL (hrl.ship_to_location_id, hrl.location_id)
INTO l_ship_to_location_id
FROM hr_locations_all 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 DISTINCT ag.attr_group_name BULK COLLECT
INTO l_attr_group_tbl
FROM po_lines_all_ext_b ple ,
ego_attr_groups_v ag ,
po_uda_ag_template_usages tu
WHERE ple.po_line_id = p_po_line_id_tbl(i)
AND ple.attr_group_id = ag.attr_group_id
AND ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = l_src_template_id
AND tu.attribute_category = 'PRICING';
SELECT DISTINCT ag.attr_group_name BULK COLLECT
INTO x_attr_group_tbl
FROM po_lines_all_ext_b ple ,
ego_attr_groups_v ag ,
po_uda_ag_template_usages tu
WHERE ple.po_line_id = p_po_line_id_tbl(i)
AND ple.attr_group_id = ag.attr_group_id
AND ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = ple.uda_template_id
AND tu.attribute_category <> 'PRICING';
SELECT DISTINCT ag.attr_group_name BULK COLLECT
INTO x_attr_group_tbl
FROM po_line_locations_all_ext_b plle ,
ego_attr_groups_v ag ,
po_uda_ag_template_usages tu
WHERE plle.line_location_id = p_line_location_id_tbl(i)
AND plle.attr_group_id = ag.attr_group_id
AND ag.attr_group_id = tu.attribute_group_id
AND tu.template_id = plle.uda_template_id;