[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
oel.inventory_item_id,
oel.ship_from_org_id,
oel.org_id,
oel.line_id,
oel.header_id,
oel.ordered_quantity, /* Always explode using ordered quantity, use lv_wip_demand_exists to manipulate quantities*/
oel.order_quantity_uom,
nvl(oel.schedule_ship_date,oel.request_date),
oel.demand_class_code,
temp, -- calling module
temp, -- customer_id
temp, -- customer_site_id
temp, -- destination_time_zone
oel.schedule_arrival_date,
temp1, -- latest acceptable_date
oel.delivery_lead_time , -- delivery lead time
temp, -- Freight_Carrier
temp, -- Ship_Method
temp, --Ship_Set_Name
temp, -- Arrival_Set_Name
1, -- Override_Flag
temp, -- Action
temp1, -- Ship_date
temp, -- available_quantity
temp, -- requested_date_quantity
temp1, -- group_ship_date
temp1, -- group_arrival_date
temp, -- vendor_id
temp, -- vendor_site_id
temp, -- insert_flag
temp, -- error_code
temp -- Message
BULK COLLECT INTO
p_atp_table.Inventory_Item_Id ,
p_atp_table.Source_Organization_Id ,
p_atp_table.Organization_id ,
p_atp_table.Identifier ,
p_atp_table.Demand_Source_Header_Id ,
p_atp_table.Quantity_Ordered ,
p_atp_table.Quantity_UOM ,
p_atp_table.Requested_Ship_Date ,
p_atp_table.Demand_Class ,
p_atp_table.Calling_Module ,
p_atp_table.Customer_Id ,
p_atp_table.Customer_Site_Id ,
p_atp_table.Destination_Time_Zone ,
p_atp_table.Requested_Arrival_Date ,
p_atp_table.Latest_Acceptable_Date ,
p_atp_table.Delivery_Lead_Time ,
p_atp_table.Freight_Carrier ,
p_atp_table.Ship_Method ,
p_atp_table.Ship_Set_Name ,
p_atp_table.Arrival_Set_Name ,
p_atp_table.Override_Flag ,
p_atp_table.Action ,
p_atp_table.Ship_Date ,
p_atp_table.Available_Quantity ,
p_atp_table.Requested_Date_Quantity ,
p_atp_table.Group_Ship_Date ,
p_atp_table.Group_Arrival_Date ,
p_atp_table.Vendor_Id ,
p_atp_table.Vendor_Site_Id ,
p_atp_table.Insert_Flag ,
p_atp_table.Error_Code ,
p_atp_table.Message
FROM oe_order_lines_all oel
WHERE oel.line_id = pLineId;
INSERT INTO MRP_DERIVED_SO_DEMANDS
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
PRIMARY_UOM_QUANTITY,
RESERVATION_TYPE,
RESERVATION_QUANTITY,
DEMAND_SOURCE_TYPE,
DEMAND_HEADER_ID,
COMPLETED_QUANTITY,
SUBINVENTORY,
DEMAND_CLASS,
REQUIREMENT_DATE,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
PARENT_DEMAND_ID,
DEMAND_ID,
SALES_CONTACT,
REFRESH_NUMBER
)
VALUES(
l_smc_table.inventory_item_id(i),
p_atp_table.Source_Organization_Id(1),
DECODE(lv_wip_demand_exists_flag,0,
decode(pITEM_TYPE_CODE,'MODEL',
NVL(inv_decimals_pub.get_primary_quantity(
p_atp_table.Source_Organization_Id(1),
l_smc_table.inventory_item_id(i),
p_atp_table.Quantity_UOM(1),
l_smc_table.quantity_ordered(i)),
l_smc_table.quantity_ordered(i)),
decode(to_number(l_smc_table.attribute_01(i)),6,
decode(G_MRP_BACKWARD_PRF,'N',0,
NVL(inv_decimals_pub.get_primary_quantity(
p_atp_table.Source_Organization_Id(1),
l_smc_table.inventory_item_id(i),
p_atp_table.Quantity_UOM(1),
l_smc_table.quantity_ordered(i)),
l_smc_table.quantity_ordered(i))),
NVL(inv_decimals_pub.get_primary_quantity(
p_atp_table.Source_Organization_Id(1),
l_smc_table.inventory_item_id(i),
p_atp_table.Quantity_UOM(1),
l_smc_table.quantity_ordered(i)),
l_smc_table.quantity_ordered(i))
)),
0),
1, /*Reservation Type*/
0, /*Reservation Quantity*/
2, /*DEMAND_SOURCE_TYPE*/
p_atp_table.Demand_Source_Header_Id(1),
0, /*Completed Quantity*/
TO_CHAR(NULL), /*subinventory*/
p_atp_table.Demand_Class(1),
l_component_ship_date,
TO_CHAR(l_smc_table.identifier(i)),
TO_CHAR(NULL), /*demand source delivery*/
TO_NUMBER(NULL), /*parent demand id*/
l_smc_table.identifier(i), /*demand id */
to_char(null), /* Sales_rep */
pLRN /* Refresh number, -1 means complete refresh */
);
SELECT a.oracle_username
INTO lv_mrp_schema
FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
and b.application_id= 704;
lv_sql_stmt:= 'select MRP_CL_FUNCTION.CHECK_BOM_VER from dual';
' SELECT /*+ index(oel oe_odr_lines_sn_n1) */ oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, '
||' oel.INVENTORY_ITEM_ID, oel.organization_id '
||' FROM MRP_SN_ODR_LINES oel '
||' WHERE ato_line_id is not null'
||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
||' AND ( oel.rn > :p_lrn ) '
||' UNION ALL'
||' SELECT /*+ index(mr mtl_reservations_sn_n1) */ oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, '
||' oel.INVENTORY_ITEM_ID, oel.organization_id '
||' FROM MRP_SN_ODR_LINES oel, '
||' MRP_SN_MTL_RESERVATIONS mr '
||' WHERE ato_line_id is not null'
||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
||' AND mr.DEMAND_SOURCE_LINE_ID = oel.LINE_ID '
||' AND ( mr.RN > :p_lrn AND oel.RN <= :p_lrn) '
||' ORDER BY organization_id ';
' SELECT UNIQUE oel.LINE_ID , oel.ATO_LINE_ID ,oel.ITEM_TYPE_CODE,oel.RN, oel.INVENTORY_ITEM_ID, oel.organization_id '
||' FROM MRP_SN_ODR_LINES oel '
||' WHERE ato_line_id is not null'
||' AND ordered_quantity > NVL(shipped_quantity,0) '
||' AND item_type_code in (''MODEL'',''STANDARD'',''CONFIG'') '
||' ORDER BY oel.organization_id ';
lv_sql_stmt1 := ' SELECT 1 '
||' FROM mtl_reservations '
||' WHERE demand_source_line_id = :pLineId '
||' AND SUPPLY_SOURCE_TYPE_ID in (5,13) '
||' AND rownum = 1 ';
lv_sql_stmt1 := ' SELECT 1 '
||' FROM WIP_FLOW_SCHEDULES '
||' WHERE demand_source_line = to_char(:pLineId) '
||' AND SCHEDULED_FLAG = 1 '
||' AND (STATUS = 1 OR (STATUS = 2 AND QUANTITY_COMPLETED > 0)) '
||' AND rownum = 1 ';
SELECT
oel.inventory_item_id,
oel.ship_from_org_id,
oel.org_id,
oel.line_id,
oel.header_id,
decode(NVL(oel.shipped_quantity,0),0,oel.ordered_quantity,0),
oel.order_quantity_uom,
nvl(oel.schedule_ship_date,oel.request_date),
oel.demand_class_code,
nvl(oel.mfg_lead_time,0),
oel.ITEM_TYPE_CODE
BULK COLLECT INTO
p_get_oe_record.Inventory_Item_Id ,
p_get_oe_record.Source_Organization_Id ,
p_get_oe_record.Organization_id ,
p_get_oe_record.Identifier ,
p_get_oe_record.Demand_Source_Header_Id ,
p_get_oe_record.Quantity_Ordered ,
p_get_oe_record.Quantity_UOM ,
p_get_oe_record.Requested_Ship_Date ,
p_get_oe_record.Demand_Class ,
p_get_oe_record.mfg_lead_time,
p_get_oe_record.ITEM_TYPE_CODE
FROM oe_order_lines_all oel
WHERE oel.ato_line_id = pATOLineId
AND oel.line_id <> pLineId;
lv_sql_stmt1 := 'select NVL(bic.wip_supply_type, msi.wip_supply_type) '
||' from ' || lv_comp_table || ' bic, ' || lv_bom_table || ' bbom '
||', mtl_system_items msi'
||' where bbom.assembly_item_id = :lv_item_id '
||' and bbom.organization_id = :Source_Organization_Id '
||' and bbom.alternate_bom_designator IS NULL '
||' and bic.bill_sequence_id = bbom.common_bill_sequence_id '
||' and bic.component_item_id = :Inventory_Item_Id '
||' and msi.inventory_item_id = bic.component_item_id '
||' and msi.organization_id = bbom.organization_id '
||' and rownum = 1 ';
INSERT INTO MRP_DERIVED_SO_DEMANDS
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
PRIMARY_UOM_QUANTITY,
RESERVATION_TYPE,
RESERVATION_QUANTITY,
DEMAND_SOURCE_TYPE,
DEMAND_HEADER_ID,
COMPLETED_QUANTITY,
SUBINVENTORY,
DEMAND_CLASS,
REQUIREMENT_DATE,
DEMAND_SOURCE_LINE,
DEMAND_SOURCE_DELIVERY,
PARENT_DEMAND_ID,
DEMAND_ID,
SALES_CONTACT,
REFRESH_NUMBER
)
VALUES(
p_get_oe_record.inventory_item_id(i),
p_get_oe_record.Source_Organization_Id(i),
decode(lv_wip_demand_exists,0,
decode(p_get_oe_record.ITEM_TYPE_CODE(i),
'MODEL', 0,
'CLASS', 0,
decode(lv_wip_supply_type,
6, decode(G_MRP_BACKWARD_PRF,
'N',0,
NVL(inv_decimals_pub.get_primary_quantity(
p_get_oe_record.Source_Organization_Id(i),
p_get_oe_record.inventory_item_id(i),
p_get_oe_record.Quantity_UOM(i),
p_get_oe_record.quantity_ordered(i)),
p_get_oe_record.quantity_ordered(i))),
NVL(inv_decimals_pub.get_primary_quantity(
p_get_oe_record.Source_Organization_Id(i),
p_get_oe_record.inventory_item_id(i),
p_get_oe_record.Quantity_UOM(i),
p_get_oe_record.quantity_ordered(i)),
p_get_oe_record.quantity_ordered(i)))),
0),
1, /*Reservation Type*/
0, /*Reservation Quantity*/
2, /*DEMAND_SOURCE_TYPE*/
p_get_oe_record.Demand_Source_Header_Id(i),
0, /*Completed Quantity*/
TO_CHAR(NULL), /*subinventory*/
p_get_oe_record.Demand_Class(i),
lv_offset_ship_date,
TO_CHAR(p_get_oe_record.Identifier(i)),
TO_CHAR(NULL), /*demand source delivery*/
TO_NUMBER(NULL), /*parent demand id*/
p_get_oe_record.Identifier(i), /*demand id */
to_char(null), /* Sales_rep */
lv_lrn /* Refresh number, -1 means complete refresh */
);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Inserting row for :' || p_get_oe_record.Identifier(i) );
SELECT decode(NVL(oel.shipped_quantity,0),0,0,1)
INTO lv_ato_item_shipped
FROM oe_order_lines_all oel
WHERE oel.line_id = pLineId;