[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT intf_attrs.interface_attr_values_id,
intf_attrs.org_id,
-- attributes read from line
draft_lines.po_line_id,
draft_lines.ip_category_id,
draft_lines.item_id,
-- attributes read from header
intf_headers.draft_id,
-- attribute values id
NULL,
-- initial value for error_flag
FND_API.g_FALSE
FROM po_attr_values_interface intf_attrs,
po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_lines_draft_all draft_lines
WHERE intf_attrs.interface_line_id = intf_lines.interface_line_id
AND intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.draft_id = draft_lines.draft_id
AND intf_lines.po_line_id = draft_lines.po_line_id
AND intf_attrs.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_attrs.interface_attr_values_id > p_max_intf_attr_values_id
AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
<> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
ORDER BY intf_attrs.interface_attr_values_id;
INSERT INTO po_session_gt(key, num1, num2, char1)
SELECT l_key,
x_processing_row_tbl(i),
attribute_values_id,
'DRAFT'
FROM po_attribute_values_draft
WHERE draft_id = x_attr_values.draft_id_tbl(i)
AND po_line_id = x_attr_values.ln_po_line_id_tbl(i)
AND org_id = x_attr_values.org_id_tbl(i);
INSERT INTO po_session_gt(key, num1, num2, char1)
SELECT l_key,
x_processing_row_tbl(i),
attribute_values_id,
'TXN'
FROM po_attribute_values
WHERE po_line_id = x_attr_values.ln_po_line_id_tbl(i)
AND org_id = x_attr_values.org_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, char1 BULK COLLECT INTO
l_index_tbl, l_result_tbl, l_source_tbl;
x_processing_row_tbl.DELETE(l_counter);
SELECT intf_attrs_tlp.interface_attr_values_tlp_id,
intf_attrs_tlp.language,
intf_attrs_tlp.org_id,
-- attributes read from line
draft_lines.po_line_id,
draft_lines.ip_category_id,
draft_lines.item_id,
draft_lines.item_description,
-- attributes read from header
intf_headers.draft_id,
-- attr values tlp id
NULL,
-- initial value for error_flag
FND_API.g_FALSE
FROM po_attr_values_tlp_interface intf_attrs_tlp,
po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_lines_draft_all draft_lines
WHERE intf_attrs_tlp.interface_line_id = intf_lines.interface_line_id
AND intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.draft_id = draft_lines.draft_id
AND intf_lines.po_line_id = draft_lines.po_line_id
AND intf_attrs_tlp.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_attrs_tlp.interface_attr_values_tlp_id > p_max_intf_attr_values_tlp_id
AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
<> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
ORDER BY intf_attrs_tlp.interface_attr_values_tlp_id;
INSERT INTO po_session_gt(key, num1, num2, char1)
SELECT l_key,
x_processing_row_tbl(i),
attribute_values_tlp_id,
'DRAFT'
FROM po_attribute_values_tlp_draft
WHERE draft_id = x_attr_values_tlp.draft_id_tbl(i)
AND po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
AND language = x_attr_values_tlp.language_tbl(i)
AND org_id = x_attr_values_tlp.org_id_tbl(i);
INSERT INTO po_session_gt(key, num1, num2, char1)
SELECT l_key,
x_processing_row_tbl(i),
attribute_values_tlp_id,
'TXN'
FROM po_attribute_values_tlp
WHERE po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
AND language = x_attr_values_tlp.language_tbl(i)
AND org_id = x_attr_values_tlp.org_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, char1 BULK COLLECT INTO
l_index_tbl, l_result_tbl, l_source_tbl;
x_processing_row_tbl.DELETE(l_counter);
SELECT mtl.master_organization_id,
fsp.inventory_organization_id
INTO l_master_org_id,
l_inv_org_id
FROM mtl_parameters mtl,
financials_system_parameters fsp
WHERE fsp.inventory_organization_id = mtl.organization_id;
SELECT draft_lines.draft_id,
draft_lines.po_line_id,
draft_lines.item_id,
draft_lines.ip_category_id,
draft_lines.item_description,
NVL(draft_headers.created_language, txn_headers.created_language),
msi.full_lead_time -- Bug7039409: Get the lead time also
BULK COLLECT INTO
l_draft_id_tbl,
l_po_line_id_tbl,
l_item_id_tbl,
l_ip_category_id_tbl,
l_item_desc_tbl,
l_created_lang_tbl,
l_lead_time_tbl -- Bug7039409: Get lead time into l_lead_time_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_lines_draft_all draft_lines,
po_headers_draft_all draft_headers,
po_headers_all txn_headers,
mtl_system_items_b msi -- Bug7039409: Added to get lead time
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
AND intf_lines.action = PO_PDOI_CONSTANTS.g_ACTION_ADD
AND intf_lines.po_line_id = draft_lines.po_line_id
AND intf_headers.draft_id = draft_lines.draft_id
AND draft_lines.po_header_id = draft_headers.po_header_id(+)
AND draft_lines.draft_id = draft_headers.draft_id(+)
AND draft_lines.po_header_id = txn_headers.po_header_id(+)
-- Added for Bug 6503535 -- Start --
-- Exclude the entries for shipments and price-breaks
AND intf_lines.shipment_num IS NULL
AND intf_lines.shipment_type IS NULL
-- Added for Bug 6503535 -- End --
AND msi.inventory_item_id (+)= draft_lines.item_id -- Bug7039409: Join msi
AND msi.organization_id (+)= l_inv_org_id -- Bug7039409: Join msi
AND NOT EXISTS
(SELECT 1
FROM po_attribute_values_draft
WHERE po_line_id = draft_lines.po_line_id
AND draft_id = draft_lines.draft_id);
INSERT INTO po_attribute_values_draft
(
ATTRIBUTE_VALUES_ID,
DRAFT_ID,
PO_LINE_ID,
REQ_TEMPLATE_NAME,
REQ_TEMPLATE_LINE_NUM,
IP_CATEGORY_ID,
INVENTORY_ITEM_ID,
ORG_ID,
LEAD_TIME,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
PO_ATTRIBUTE_VALUES_S.nextval,
l_draft_id_tbl(i),
l_po_line_id_tbl(i),
'-2', -- REQ_TEMPLATE_NAME
-2, --REQ_TEMPLATE_LINE_NUM
NVL(l_ip_category_id_tbl(i), -2),
NVL(l_item_id_tbl(i), -2),
PO_PDOI_PARAMS.g_request.org_id,
l_lead_time_tbl(i), -- Bug7039409: LEAD_TIME
FND_GLOBAL.login_id,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
sysdate
);
INSERT INTO po_attribute_values_tlp_draft
(
ATTRIBUTE_VALUES_TLP_ID,
DRAFT_ID,
PO_LINE_ID,
REQ_TEMPLATE_NAME,
REQ_TEMPLATE_LINE_NUM,
IP_CATEGORY_ID,
INVENTORY_ITEM_ID,
ORG_ID,
LANGUAGE,
DESCRIPTION,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
PO_ATTRIBUTE_VALUES_TLP_S.nextval,
l_draft_id_tbl(i),
l_po_line_id_tbl(i),
'-2', -- REQ_TEMPLATE_NAME
-2, --REQ_TEMPLATE_LINE_NUM
NVL(l_ip_category_id_tbl(i), -2),
NVL(l_item_id_tbl(i), -2),
PO_PDOI_PARAMS.g_request.org_id,
l_created_lang_tbl(i),
l_item_desc_tbl(i),
FND_GLOBAL.login_id,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
sysdate
);
SELECT mmpn.inventory_item_id,
mmpn.mfg_part_num,
mmpn.manufacturer_name
BULK COLLECT INTO l_item_id_tbl2,
l_mfg_part_num_tbl,
l_mfg_name_tbl
FROM mtl_mfg_part_numbers_all_v mmpn
WHERE row_id IN (SELECT MIN(mmpn2.row_id)
FROM mtl_mfg_part_numbers_all_v mmpn2,
po_attribute_values_draft pavd
WHERE pavd.inventory_item_id = mmpn2.inventory_item_id
AND mmpn2.organization_id = l_master_org_id
AND pavd.request_id = fnd_global.conc_request_id
AND pavd.program_application_id = fnd_global.prog_appl_id
AND pavd.program_id = fnd_global.conc_program_id
GROUP BY pavd.inventory_item_id);
UPDATE po_attribute_values_draft
SET manufacturer_part_num = l_mfg_part_num_tbl(i)
WHERE inventory_item_id = l_item_id_tbl2(i)
AND org_id = l_master_org_id
AND request_id = fnd_global.conc_request_id
AND program_application_id = fnd_global.prog_appl_id
AND program_id = fnd_global.conc_program_id;
UPDATE po_attribute_values_tlp_draft
SET manufacturer = l_mfg_name_tbl(i)
WHERE inventory_item_id = l_item_id_tbl2(i)
AND org_id = l_master_org_id
AND request_id = fnd_global.conc_request_id
AND program_application_id = fnd_global.prog_appl_id
AND program_id = fnd_global.conc_program_id;
UPDATE po_attribute_values_tlp_draft pavd_tlp
SET long_description = (SELECT long_description
FROM mtl_system_items_tl msi_tl,
fnd_languages lang
WHERE msi_tl.inventory_item_id = pavd_tlp.inventory_item_id
AND msi_tl.organization_id = l_master_org_id
AND msi_tl.language = NVL(pavd_tlp.language,lang.language_code)
AND lang.installed_flag = 'B')
WHERE pavd_tlp.request_id = fnd_global.conc_request_id
AND pavd_tlp.program_application_id = fnd_global.prog_appl_id
AND pavd_tlp.program_id = fnd_global.conc_program_id;