[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT doc_type,trans_date,orgn_code,trans_qty
FROM mr_tran_tbl
WHERE mrp_id =V_mrp_id
AND item_id =V_item_id
AND INSTR(V_whse_list, whse_code) <> 0
ORDER BY trans_date asc, trans_qty desc;
SELECT no_days, no_weeks, no_4weeks, no_13weeks
FROM ps_schd_hdr
WHERE schedule_id = V_schedule
AND delete_mark = 0;
SELECT matl_rep_id
FROM ps_matl_hdr
WHERE matl_rep_id = V_matl_rep_id;
SELECT gem5_matl_rep_id_s.NEXTVAL
FROM dual;
INSERT INTO ps_matl_hdr (matl_rep_id, item_id)
VALUES (X_matl_rep_id, V_item_id);
DELETE
FROM ps_matl_dtl
WHERE matl_rep_id = V_matl_rep_id
AND item_id = V_item_id;
INSERT INTO ps_matl_dtl
(MATL_REP_ID,
ITEM_ID,
WHSE_CODE,
QTY_ON_HAND,
PERD_NAME,
PERD_END_DATE,
SALES_ORDERS,
FORE_CAST,
PLND_INGRED,
OTHER_DEMAND,
TOTAL_DEMAND,
PO_RECEIPTS,
PREQ_SUPPLY,
PRCV_SUPPLY,
SHMT_SUPPLY,
SCHED_PROD,
SCHED_INGRED,
PLND_PURCHASE,
PLND_PROD,
ENDING_BAL,
NET_SS_REQMT,
SCHED_TRANSFER_OUT,
SCHED_TRANSFER_IN,
PLND_TRANSFER_OUT,
PLND_TRANSFER_IN)
VALUES
(X_matl_rep_id,
V_item_id,
X_whse_code,
V_on_hand,
period_name_tab(X_j),
period_end_date_tab(X_j),
X_sales_orders,
X_forecast,
X_plnd_ingred,
X_other_demand,
X_total_demand,
X_po_receipts,
X_preq_supply ,
X_prcv_supply ,
X_shmt_supply ,
X_sched_prod,
X_dep_demand,
X_planned_purch,
X_plnd_prod,
X_ending_bal,
X_net_ss_reqmt,
X_sched_transfer_out,
X_sched_transfer_in,
X_plnd_transfer_out,
X_plnd_transfer_in);
SELECT order_ind
FROM ps_schd_hdr
WHERE schedule_id = V_schedule;
SELECT item_um, item_um2
FROM ic_item_mst
WHERE item_id = V_item_id; */
SELECT DISTINCT primary_uom_code,secondary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = V_item_id;
SELECT no_days, no_weeks, no_4weeks, no_13weeks
FROM ps_schd_hdr
WHERE schedule_id = V_schedule
AND delete_mark = 0;
SELECT matl_rep_id
FROM ps_matl_hdr
WHERE matl_rep_id = V_matl_rep_id;
SELECT gem5_matl_rep_id_s.NEXTVAL
FROM dual;
x_select VARCHAR2(32600);/* B3394924 sowmya */
x_select := ' SELECT gmd.material_requirement_date trans_date, '||
' DECODE(gbh.batch_type, 10,''FPO'',''PROD'') doc_type, '||
' DECODE(gmd.line_type, -1,-1,1) * DECODE(gmd.dtl_um, '||
' :p1, '||
' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
' NULL, '||
' gmd.organization_id, '||
' NULL, '||
' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
' gmd.dtl_um, '||
' :p2, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
/* ' DECODE(msi.dual_uom_control,0,0, '||
' DECODE(gmd.line_type, -1,-1,1) * DECODE(gmd.dtl_um, '||
' msi.secondary_uom_code, '||
' (gmd.wip_plan_qty - gmd.actual_qty), '||
' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
' NULL, '||
' gmd.organization_id, '||
' 38, '||
' (gmd.wip_plan_qty-gmd.actual_qty), '||
' gmd.dtl_um, '||
' msi.secondary_uom_code, '||
' NULL, '||
' NULL '||
' ) '||
' ) '||
' ) trans_qty2, '||
*/
' mp.organization_code '||
' FROM '||
' gme_batch_header gbh, '||
' gme_material_details gmd, '||
' mtl_parameters mp, '||
' mtl_system_items msi '||
' WHERE '||
' Gbh.batch_id = gmd.batch_id '||
' AND msi.inventory_item_id = gmd.inventory_item_id '||
' AND msi.organization_id = gmd.organization_id '||
' AND gmd.organization_id = mp.organization_id '||
' AND mp.process_enabled_flag = '|| '''Y''' ||
' AND gbh.batch_status IN (1,2) '||
' AND gmd.actual_qty < NVL(gmd.wip_plan_qty, gmd.plan_qty) '||
' AND msi.inventory_item_id = :p3 '||
' AND INSTR(:p4, TO_CHAR(gbh.organization_id)) <> 0' ;
x_select := x_select ||' UNION ALL ' ||
' SELECT '||
' mtl.requirement_date, '||
' '''||'OMSO'||''''||', '||
-- ' mtl.primary_uom_quantity * (-1) , '||
' DECODE(items.primary_uom_code,:p5,mtl.primary_uom_quantity * (-1), '|| -- akaruppa added
' (-1) * inv_convert.inv_um_convert(mtl.inventory_item_id, '||
' NULL, '||
' org.organization_id, '||
' NULL, '||
' mtl.primary_uom_quantity , '||
' items.primary_uom_code, '||
' :p6, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' org.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM '||
' mtl_demand_omoe mtl,'||
' mtl_system_items items,'||
' oe_order_headers_all hdr, '||
' oe_order_lines_all dtl, '||
' mtl_parameters org '||
' WHERE '||
' mtl.inventory_item_id = :p7 '|| -- akaruppa previously im.item_id
' AND INSTR(:p8, TO_CHAR(mtl.organization_id)) <> 0'|| -- akaruppa previously iwm.whse_code
' and items.organization_id = mtl.organization_id '||
' and items.inventory_item_id = mtl.inventory_item_id '||
' and NVL(mtl.completed_quantity,0) = 0 '||
' and mtl.open_flag = ' || '''Y''' ||
' and mtl.available_to_mrp = 1 '||
' and mtl.parent_demand_id is NULL '||
' and mtl.demand_source_type IN (2,8) '||
' and mtl.demand_id = dtl.line_id '||
' and dtl.header_id = hdr.header_id '||
' and dtl.ship_from_org_id = org.organization_id '||
' and org.process_enabled_flag = '|| '''Y''' ||
/* ' and ((TO_NUMBER(FND_PROFILE.VALUE(''GMP_EXCLUDE_INTERNAL_OMSO'')) = 1 ' ||
' and nvl(dtl.source_document_type_id, 0) <> 10 ' ||
' ) ' ||
' or TO_NUMBER(FND_PROFILE.VALUE(''GMP_EXCLUDE_INTERNAL_OMSO'')) = 0 ' ||
' ) ' ||
*/
' and NOT EXISTS '||
' (SELECT 1 '||
' FROM so_lines_all sl, '||
' so_lines_all slp, '||
' mtl_demand_omoe dem '||
' WHERE '||
' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id) '||
' and to_number(dem.demand_source_line) = sl.line_id(+) '||
' and dem.demand_source_type in (2,8) '||
' and sl.end_item_unit_number IS NULL '||
' and slp.end_item_unit_number IS NULL '||
' and dem.demand_id = mtl.demand_id '||
' and items.effectivity_control = 2) ' ;
x_select := x_select ||' UNION ALL ' ||
-- akaruppa changed query to obtain forecast data from Oracle Forecast
' SELECT '||
' dtl.forecast_date, '||
' '''||'FCST'||''''||', '||
-- ' dtl.current_forecast_quantity trans_qty, '||
' DECODE(msi.primary_uom_code,:p9, (-1) * dtl.current_forecast_quantity, '||
' (-1) * inv_convert.inv_um_convert(dtl.inventory_item_id, '||
' NULL, '||
' dtl.organization_id, '||
' NULL, '||
' dtl.current_forecast_quantity, '||
' msi.primary_uom_code, '||
' :p10, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mp.organization_code '||
' FROM '||
' ps_schd_for psf, '||
' mrp_forecast_designators mff, '||
' mrp_forecast_dates dtl, '||
' mtl_system_items msi, '||
' mtl_parameters mp '||
' WHERE dtl.inventory_item_id = :p11 '||
' AND psf.schedule_id = :p12 '||
' AND INSTR(:p13, TO_CHAR(psf.organization_id)) <> 0 '||
' AND psf.organization_id = mp.organization_id '||
' AND mp.process_enabled_flag = '|| '''Y''' ||
' AND psf.organization_id = msi.organization_id '||
' AND dtl.inventory_item_id = msi.inventory_item_id '||
' AND psf.organization_id = mff.organization_id '||
' AND psf.forecast_designator = mff.forecast_set '||
' AND mff.forecast_designator = dtl.forecast_designator '||
' AND mff.organization_id = dtl.organization_id '||
' AND dtl.forecast_date >= fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate)) '||
' UNION ALL ' ||
' SELECT po.expected_delivery_date, '||
' '''||'PORD'||''''||', '||
-- ' po.to_org_primary_quantity, '||
' DECODE(mitem.primary_uom_code,:p14,po.to_org_primary_quantity, '|| -- akaruppa added
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' :p15, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM MTL_PARAMETERS mtl, '||
' PO_PO_SUPPLY_VIEW po, '||
' MTL_SYSTEM_ITEMS mitem '||
' WHERE po.item_id = :p16 '|| -- akaruppa previously ic.item_id
' AND po.item_id = mitem.inventory_item_id '||
' AND po.to_organization_id = mitem.organization_id '||
' AND mtl.organization_id = po.to_organization_id '||
' AND mtl.process_enabled_flag = '|| '''Y''' ||
' AND NOT EXISTS '||
' ( SELECT 1 FROM oe_drop_ship_sources odss '||
' WHERE po.po_header_id = odss.po_header_id '||
' AND po.po_line_id = odss.po_line_id ) '||
' AND INSTR(:p17, TO_CHAR(po.to_organization_id)) <> 0 '|| -- akaruppa previously iwm.whse_code
' UNION ALL ' ||
' SELECT po.expected_delivery_date, '||
' '''||'PREQ'||''''||', '||
-- ' po.to_org_primary_quantity,'||
' DECODE(mitem.primary_uom_code,:p18,po.to_org_primary_quantity, '|| -- akaruppa added
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' :p19, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM MTL_PARAMETERS mtl,'||
' PO_REQ_SUPPLY_VIEW po,'||
' MTL_SYSTEM_ITEMS mitem '||
' WHERE po.item_id = :p20'|| -- akaruppa previously ic.item_id
' AND po.item_id = mitem.inventory_item_id '||
' AND po.to_organization_id = mitem.organization_id '||
' AND mtl.organization_id = po.to_organization_id '||
' AND mtl.process_enabled_flag = '|| '''Y''' ||
' AND NOT EXISTS '||
' ( SELECT 1 FROM oe_drop_ship_sources odss '||
' WHERE po.requisition_header_id = odss.requisition_header_id '||
' AND po.req_line_id = odss.requisition_line_id ) '||
' AND INSTR(:p21, TO_CHAR(po.to_organization_id)) <> 0 ' ; -- akaruppa previously iwm.whse_code
x_select := x_select || ' UNION ALL '||
' SELECT po.expected_delivery_date,'||
' '''||'PRCV'||''''||', '||
-- ' po.to_org_primary_quantity,'||
' DECODE(mitem.primary_uom_code,:p22,po.to_org_primary_quantity, '|| -- akaruppa added
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' :p23, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM MTL_PARAMETERS mtl,'||
' PO_RCV_SUPPLY_VIEW po,'||
' MTL_SYSTEM_ITEMS mitem '||
' WHERE po.item_id = :p24'|| -- akaruppa previously ic.item_id
' AND po.item_id = mitem.inventory_item_id '||
' AND po.to_organization_id = mitem.organization_id '||
' AND mtl.organization_id = po.to_organization_id '||
' AND mtl.process_enabled_flag = ' || '''Y''' || --||''''||'Y'||''' '||
' AND NOT EXISTS '||
' ( SELECT 1 FROM oe_drop_ship_sources odss '||
' WHERE po.po_header_id = odss.po_header_id '||
' AND po.po_line_id = odss.po_line_id ) '||
' AND INSTR(:p25, TO_CHAR(po.to_organization_id)) <> 0' || -- akaruppa previously iwm.whse_code
' UNION ALL '||
' SELECT po.expected_delivery_date,'||
' '''||'PRCV'||''''||', '||
-- ' po.to_org_primary_quantity,'||
' DECODE(mitem.primary_uom_code,:p26,po.to_org_primary_quantity, '|| -- akaruppa added
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' :p27, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM MTL_PARAMETERS mtl,'||
' PO_SHIP_RCV_SUPPLY_VIEW po, '||
' MTL_SYSTEM_ITEMS mitem '||
' WHERE po.item_id = :p28'|| -- akaruppa previously ic.item_id
' AND po.item_id = mitem.inventory_item_id '||
' AND po.to_organization_id = mitem.organization_id '||
' AND mtl.organization_id = po.to_organization_id '||
' AND mtl.process_enabled_flag = '|| '''Y''' ||
' AND INSTR(:p29, TO_CHAR(po.to_organization_id)) <> 0'; -- akaruppa previously iwm.whse_code
x_select := x_select || ' UNION ALL '||
' SELECT '|| /* + ordered */
' po.expected_delivery_date, '||
' '''||'SHMT'||''''||', '||
-- ' po.to_org_primary_quantity,'||
' DECODE(mitem.primary_uom_code,:p30,po.to_org_primary_quantity, '|| -- akaruppa added
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' :p31, '||
' NULL, '||
' NULL '||
' ) '||
' ) trans_qty, '||
' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
' FROM MTL_SYSTEM_ITEMS mitem,'||
' PO_SHIP_SUPPLY_VIEW po,'||
' MTL_PARAMETERS mtl '||
' WHERE po.item_id = :p32'|| -- akaruppa previously ic.item_id
' AND po.item_id = mitem.inventory_item_id '||
' AND po.to_organization_id = mitem.organization_id '||
' AND mtl.organization_id = po.to_organization_id '||
' AND mtl.process_enabled_flag = '|| '''Y''' ||
' AND INSTR(:p33, TO_CHAR(mtl.organization_id)) <> 0 ' || -- akaruppa previously iwm.whse_code
' ORDER BY 1 asc, 3 desc ';
OPEN Cur_trans_dtl FOR x_select USING
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id, V_schedule,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list;
OPEN Cur_trans_dtl FOR x_select USING
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_schedule,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list,
V_uom, V_uom, V_item_id,V_org_list;
/* ToDo : Need to ensure if we need to insert organization_id too?
V_matl_rep_id will be null when call to this procedure is made from
Bucketed Material Form and NOT Report. I think this part of code
will require to be removed. */
/* nsinghi MPSCONV End */
/*
INSERT INTO ps_matl_hdr (matl_rep_id, inventory_item_id)
VALUES (X_matl_rep_id, V_item_id);
DELETE
FROM ps_matl_dtl
WHERE matl_rep_id = V_matl_rep_id
AND item_id = V_item_id;
INSERT INTO ps_matl_dtl
(MATL_REP_ID,
/* nsinghi MPSCONV Start */
-- ITEM_ID,
INVENTORY_ITEM_ID,
-- WHSE_CODE,
ORGANIZATION_ID,
/* nsinghi MPSCONV End */
QTY_ON_HAND,
PERD_NAME,
PERD_END_DATE,
SALES_ORDERS,
FORE_CAST,
SCHED_INGRED,
FIRM_INGRED,
TOTAL_DEMAND,
PO_RECEIPTS,
PREQ_SUPPLY,
PRCV_SUPPLY,
SHMT_SUPPLY,
SCHED_PROD,
FIRM_PROD,
ENDING_BAL,
NET_SS_REQMT)
/* SCHED_TRANSFER_OUT,
SCHED_TRANSFER_IN ) */
VALUES
(X_matl_rep_id,
V_item_id,
/* nsinghi MPSCONV Start */
-- X_whse_code,
X_organization_id,
/* nsinghi MPSCONV End */
V_on_hand,
period_name_tab(X_j),
period_end_date_tab(X_j),
X_sales_orders,
X_forecast,
X_sched_ingred,
X_firm_ingred,
X_total_demand,
X_po_receipts,
X_preq_supply ,
X_prcv_supply ,
X_shmt_supply ,
X_sched_prod,
X_firm_prod,
X_ending_bal,
X_net_ss_reqmt);