[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Step 4: insert into oe_headers_iface_all from po_requisition_headers
Step 5: For each row
insert into oe_lines_iface_all from po_requisition_lines
Step 6: Update the transferred_to_oe_flag to 'Y' for all 'I' ones and
to 'N' for all 'E' ones.
Step 7: return
==============================================================================*/
PROCEDURE LOAD_OM_INTERFACE(
errbuf out NOCOPY varchar2,
retcode out NOCOPY number,
p_req_header_id number default null)
IS
l_currency_code VARCHAR2(16);
select /*+ leading(HDR) index(HDR PO_REQUISITION_HEADERS_N4) USE_NL(HDR
LIN PLA)*/
nvl(org.operating_unit,-1),
hdr.requisition_header_id,
lin.requisition_line_id,
Nvl((select PLA.CUSTOMER_ID
from PO_LOCATION_ASSOCIATIONS_ALL PLA
where pla.location_id = lin.deliver_to_location_id
AND nvl(PLA.ORG_ID,-1) = nvl(org.operating_unit,-1)
AND rownum=1),-1) customer_id --bug 8692047
from po_requisition_lines lin,
po_requisition_headers hdr,
org_organization_definitions org
where lin.requisition_header_id = hdr.requisition_header_id
and lin.source_organization_id = org.organization_id
and hdr.transferred_to_oe_flag = 'I'
and lin.source_type_code = 'INVENTORY'
and nvl(lin.cancel_flag,'N') = 'N'
and nvl(lin.closed_code,'OPEN') <> 'FINALLY CLOSED'
order by org.operating_unit,
hdr.requisition_header_id,
CUSTOMER_ID,
lin.line_num;
/* Select all requisition headers which have been approved since
last run (or =req_id)
We are setting the transferred_to_oe_flag to N for externally
sourced reqs as well
so that the program does not have to sort through these rows
again next time */
Fnd_File.Put_Line(FND_FILE.LOG, 'Updating Req Headers');
/*UPDATE PO_REQUISITION_HEADERS PRH
SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
WHERE nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N'
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND exists (select /*+ HASH_SJ */ /*'At least one inventory sourced line'
from po_requisition_lines prl
where prh.requisition_header_id =
prl.requisition_header_id
and prl.source_type_code = 'INVENTORY'
and nvl(prl.cancel_flag,'N') = 'N'
and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');*/
UPDATE po_requisition_headers prh
SET prh.transferred_to_oe_flag = 'I'
WHERE prh.authorization_status = 'APPROVED'
AND prh.requisition_header_id IN (SELECT /*+ index ( PRL po_requisition_lines_f5 ) */
DISTINCT prl.requisition_header_id
FROM po_requisition_lines prl
WHERE Nvl(prl.line_location_id,- 999) = - 999
AND prl.source_type_code = 'INVENTORY'
AND Nvl(prl.transferred_to_oe_flag,'N') = 'N' --Bug7699084
AND Nvl(prl.cancel_flag,'N') = 'N'
AND Nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');
Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
UPDATE PO_REQUISITION_HEADERS PRH
SET PRH.TRANSFERRED_TO_OE_FLAG = 'I'
WHERE /*nvl(PRH.TRANSFERRED_TO_OE_FLAG,'N') = 'N' AND Bug 7699084*/
PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PRH.REQUISITION_HEADER_ID = p_req_header_id
AND exists (select 'At least one inventory sourced line'
from po_requisition_lines prl
where prh.requisition_header_id =
prl.requisition_header_id
and prl.source_type_code = 'INVENTORY'
and Nvl(prl.transferred_to_oe_flag,'N') = 'N' /*Bug 7699084*/
and nvl(prl.cancel_flag,'N') = 'N'
and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED');
Fnd_File.Put_Line(FND_FILE.LOG,to_char(SQL%ROWCOUNT)||' Reqs selected for processing');
Fnd_File.Put_Line(FND_FILE.LOG, 'No reqs selected for processing');
UPDATE PO_REQUISITION_HEADERS
SET TRANSFERRED_TO_OE_FLAG = 'E'
WHERE REQUISITION_HEADER_ID = l_req_hdr_id
and TRANSFERRED_TO_OE_FLAG = 'I';
INSERT INTO OE_HEADERS_IFACE_ALL
(creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
orig_sys_document_ref,
sold_to_org_id,
order_type_id,
order_source_id,
order_category,
ordered_date,
transactional_curr_code,
request_date,
price_list_id,
accounting_rule_id,
invoicing_rule_id,
ship_to_org_id,
org_id,
ORDER_DATE_TYPE_CODE)
SELECT
SYSDATE,
RH.CREATED_BY,
SYSDATE,
RH.LAST_UPDATED_BY,
RH.LAST_UPDATE_LOGIN,
RH.REQUISITION_HEADER_ID, /* Requisition Header Id */
PLA.CUSTOMER_ID,
l_ot_id,
10, -- seeded order source for internal reqs
'P',
RH.CREATION_DATE,
l_currency_code,
RL.NEED_BY_DATE,
l_pr_id,
l_ac_id,
l_ir_id,
PLA.SITE_USE_ID,
decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
'ARRIVAL' --Bug 7662103:
FROM PO_REQUISITION_HEADERS RH,
PO_REQUISITION_LINES RL,
PO_LOCATION_ASSOCIATIONS_ALL PLA
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID
AND nvl(PLA.ORG_ID,-1) = l_op_unit_id
AND RL.REQUISITION_LINE_ID = l_req_line_id;
Added the if to Insert in to OE_ACTIONS_IFACE_ALL only when the Header
was inserted. */
IF SQL%ROWCOUNT>0 then
Fnd_File.Put_Line(FND_FILE.LOG, '-----');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Header : '||to_char(l_req_hdr_id));
inserting with the Org_id of the Destination Org. This resulted in Sales
Order not getting Created in a Booked State. */
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting the customer id');
SELECT PLA.CUSTOMER_ID
INTO l_customer_id
FROM PO_REQUISITION_LINES RL,
PO_LOCATION_ASSOCIATIONS_ALL PLA
WHERE RL.DELIVER_TO_LOCATION_ID = PLA.LOCATION_ID
AND nvl(PLA.ORG_ID,-1) = l_op_unit_id
AND RL.REQUISITION_LINE_ID = l_req_line_id;
INSERT INTO OE_ACTIONS_IFACE_ALL
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
OPERATION_CODE,
ORG_ID,
SOLD_TO_ORG_ID) -- Bug 3365408
values
(10,
l_req_hdr_id,
'BOOK_ORDER',
decode(l_op_unit_id, -1, NULL, l_op_unit_id) ,
l_customer_id -- Bug 3365408
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Header not Inserted : '||to_char(l_req_hdr_id));
SELECT PRL.destination_type_code,
PRL.source_organization_id,
PRL.org_id,
PRL.need_by_date,
PRD.project_id,
PRD.task_id,
--
PRL.secondary_quantity,
PRL.secondary_unit_of_measure,
PRL.item_id
--
INTO l_dest_type_code,
l_source_org_id,
l_org_id,
l_need_by_date,
l_project_id,
l_task_id,
l_dest_secondary_quantity ,
l_dest_secondary_unit ,
l_item_id
FROM po_requisition_lines PRL, po_req_distributions PRD
WHERE PRL.requisition_line_id = l_req_line_id
AND PRL.requisition_line_id = PRD.requisition_line_id; -- JOIN
Hence added a new function get_cst_price and this is called while inserting the unit_price
columns in so_oe_lines_iface_all table */
/* 1906141 -Subinventory was not populated in oe_lines_iface_all.
Populating the source subinventory from requisition lines */
/* Bug 1988404 - Removed the insert of value to schedule_ship_date. If this is
populated, when Order Import calls Scheduling that fails if the on hand inventory
is not available on that date. We expect MRP to populate the proper date when
scheduling is called based on the request_date provided, which is the need_by_date */
/* 2034580 - Pass project and task id only if PJM is installed for
destination type EXPENSE */
/* Bug2357247 if PJM is installed in shared product do not pass project_id
and task_id for destination type EXPENSE */
--begin bug 3249134, forward port of 3122219
--Bug 10037733
SELECT currency_code
INTO l_Source_Currency_Code
FROM gl_sets_of_books glsob,
org_organization_definitions ood
WHERE glsob.set_of_books_id = ood.set_of_books_id
AND ood.organization_id = l_source_org_id;
select currency_code
into l_dest_currency_code
from
financials_system_parameters fsp,
gl_sets_of_books gl
where gl.set_of_books_id = fsp.set_of_books_id
--#bug 12816938 add org condition to return single row for multi org.
and fsp.org_id = l_org_id;
and unit_list_price to extended precision while inserting data into
oe_lines_iface_all*/
--end bug 3249134
--
IF l_dest_secondary_quantity IS NOT NULL THEN
-- get source secondary uom
PO_UOM_S.get_secondary_uom( l_item_id,
l_source_org_id,
l_source_secondary_uom,
l_source_secondary_unit);
SELECT rl.unit_price,
rl.unit_price
INTO l_unit_selling_price,
l_unit_list_price
FROM po_requisition_lines rl
WHERE requisition_line_id = l_req_line_id;
SELECT set_of_books_id
INTO l_sob_id
FROM financials_system_parameters
--#bug 12816938 add org condition to return single row for multi org.
WHERE org_id = l_org_id;
SELECT default_rate_type
INTO l_conversion_type
FROM po_system_parameters
--#bug 12816938 add org condition to return single row for multi org.
WHERE org_id = l_org_id;
SELECT rl.unit_price/l_rate,
rl.unit_price/l_rate
INTO l_unit_selling_price,
l_unit_list_price
FROM po_requisition_lines rl
WHERE requisition_line_id = l_req_line_id;
headers Order Import was Erroring out. To insert different
OM Headers for OU+customer combination is not possible
as a Bugfix.
As a workaround we are just popuating the same Customer
information as the header for the all the sales Order
lines for the same OU to by pass OM Validation. */
INSERT INTO OE_LINES_IFACE_ALL
(CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
SOLD_TO_ORG_ID, -- Bug 3365408
LINE_NUMBER,
ORDER_QUANTITY_UOM,
ORDERED_QUANTITY,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
INVENTORY_ITEM_ID,
SHIP_FROM_ORG_ID,
REQUEST_DATE,
ITEM_TYPE_CODE,
OPTION_FLAG,
ORDER_SOURCE_ID,
CALCULATE_PRICE_FLAG,
SHIP_TO_ORG_ID,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
SUBINVENTORY,
ORG_ID,
ORDERED_QUANTITY_UOM2,
ORDERED_QUANTITY2,
PREFERRED_GRADE,
SHIPMENT_PRIORITY_CODE
) /* B1548597 OPM */
SELECT SYSDATE,
RL.CREATED_BY,
SYSDATE,
RL.LAST_UPDATED_BY,
RL.LAST_UPDATE_LOGIN,
RH.REQUISITION_HEADER_ID,
RL.REQUISITION_LINE_ID,
l_customer_id, -- Bug 5280573
--RL.LINE_NUM,
l_req_line_number, --Bug 12576879 Commented above added the variable to get from counter
MUM.UOM_CODE,
round(RL.QUANTITY,9),
--begin bug 3249134: changed the following values to be rounded
--< INVCONV R12 START> umoogala: Added Dest. OrgId and qty parameters
decode (fnd_profile.value('PO_CUSTOM_UNIT_PRICE'),'Y',round(l_unit_list_price,l_ext_precision),round(PO_CREATE_ISO.GET_CST_PRICE(RL.ITEM_ID,
RL.SOURCE_ORGANIZATION_ID,
RL.UNIT_MEAS_LOOKUP_CODE,
RL.DESTINATION_ORGANIZATION_ID,
RL.QUANTITY),
l_ext_precision)), --Bug10037733
decode (fnd_profile.value('PO_CUSTOM_UNIT_PRICE'),'Y',round(l_unit_selling_price,l_ext_precision),round(PO_CREATE_ISO.GET_CST_PRICE(RL.ITEM_ID,
RL.SOURCE_ORGANIZATION_ID,
RL.UNIT_MEAS_LOOKUP_CODE,
RL.DESTINATION_ORGANIZATION_ID,
RL.QUANTITY),
l_ext_precision)), --Bug10037733
--end bug 3249134
RL.ITEM_ID,
RL.SOURCE_ORGANIZATION_ID,
RL.NEED_BY_DATE,
DECODE(SI.PICK_COMPONENTS_FLAG,
'N','STANDARD',
'Y','KIT',
'STANDARD'),
'N',
10,
'N',
LA.SITE_USE_ID,
-- Bug 2873877 START
-- Only pass project and task for Inventory lines that
-- pass the PJM validations in the source org. See above.
l_project_id,
l_task_id,
-- Bug 2873877 END
RD.END_ITEM_UNIT_NUMBER,
RL.SOURCE_SUBINVENTORY,
decode(l_op_unit_id, -1, NULL, l_op_unit_id),
l_source_secondary_uom, --MUM1.UOM_CODE,
round( l_source_secondary_quantity,9), --RL.SECONDARY_QUANTITY,
decode(si.grade_control_flag,'Y',RL.preferred_grade,NULL) -- RL.PREFERRED_GRADE
, decode(RL.URGENT_FLAG,'Y', fnd_profile.value('POR_URGENT_FLAG_SHIPMENT_PRIORITY_CODE'),null)
FROM PO_REQUISITION_LINES RL,
PO_REQUISITION_HEADERS RH,
PO_REQ_DISTRIBUTIONS RD, --only one distribution allowed!
MTL_SYSTEM_ITEMS SI,
PO_LOCATION_ASSOCIATIONS_ALL LA,
MTL_UNITS_OF_MEASURE MUM
--,MTL_UNITS_OF_MEASURE MUM1
WHERE RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID
AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
AND RL.ITEM_ID = SI.INVENTORY_ITEM_ID
AND RL.SOURCE_ORGANIZATION_ID = SI.ORGANIZATION_ID
AND RL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE
--AND RL.SECONDARY_UNIT_OF_MEASURE = MUM1.UNIT_OF_MEASURE(+) /* B1548597 OPM */
AND RL.DELIVER_TO_LOCATION_ID = LA.LOCATION_ID
AND RL.REQUISITION_LINE_ID = l_req_line_id
AND nvl(LA.ORG_ID, -1) = l_op_unit_id;
/* Update transferred_to_oe_flag for all rows processed */
UPDATE PO_REQUISITION_HEADERS
SET TRANSFERRED_TO_OE_FLAG =
DECODE(TRANSFERRED_TO_OE_FLAG,'I','Y','E','N')
WHERE TRANSFERRED_TO_OE_FLAG IN ('I', 'E');
UPDATE po_requisition_lines prl
SET prl.transferred_to_oe_flag =
(SELECT prh.transferred_to_oe_flag
FROM po_requisition_headers prh
WHERE prh.requisition_header_id = l_req_hdr_id_tbl(i)
)
WHERE prl.requisition_header_id = l_req_hdr_id_tbl(i)
and prl.source_type_code = 'INVENTORY'
and nvl(prl.cancel_flag,'N') = 'N'
and nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the TRANSFERRED_TO_OE_FLAG of the corresponding lines');
Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Currency Code');
SELECT glsob.CURRENCY_CODE
INTO l_currency_code
FROM GL_SETS_OF_BOOKS GLSOB,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE GLSOB.SET_OF_BOOKS_ID=FSP.SET_OF_BOOKS_ID
AND nvl(FSP.org_id,-1) = l_op_unit_id;
Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Order Type');
SELECT ORDER_TYPE_ID
INTO l_ot_id
FROM PO_SYSTEM_PARAMETERS_ALL
WHERE nvl(ORG_ID,-1) = l_op_unit_id;
Fnd_File.Put_Line(FND_FILE.LOG, 'Error selecting order type');
Fnd_File.Put_Line(FND_FILE.LOG, 'Selecting Price List from Order Type');
SELECT PRICE_LIST_ID,
ACCOUNTING_RULE_ID,
INVOICING_RULE_ID
INTO
l_pr_id,
l_ac_id,
l_ir_id
FROM OE_TRANSACTION_TYPES_ALL
WHERE transaction_type_id = l_ot_id
AND nvl(ORG_ID, -1) = l_op_unit_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error selecting OE Information');
SELECT NVL(src.process_enabled_flag,'N'), NVL(dest.process_enabled_flag,'N')
INTO l_src_process_enabled_flag, l_dest_process_enabled_flag
FROM mtl_parameters src, mtl_parameters dest
WHERE src.organization_id = l_src_org_id
AND dest.organization_id = l_dest_org_id;
SELECT to_number(src.org_information3) src_ou, to_number(dest.org_information3) dest_ou
INTO l_from_ou, l_to_ou
FROM hr_organization_information src, hr_organization_information dest
WHERE src.organization_id = l_src_org_id
AND src.org_information_context = 'Accounting Information'
AND dest.organization_id = l_dest_org_id
AND dest.org_information_context = 'Accounting Information';
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = x_unit_of_measure
;
SELECT primary_unit_of_measure
INTO x_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = x_item_id
AND organization_id = x_organization_id;