[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_header_data;
insert_header_data;
DELETE
FROM ps_matl_hdr pmh
WHERE pmh.matl_rep_id = G_matl_rep_id
AND
((pmh.inventory_item_id NOT IN (SELECT pud1.inventory_item_id
FROM ps_ubkt_dtl pud1
WHERE pud1.matl_rep_id = G_matl_rep_id))
OR
(pmh.organization_id NOT IN (SELECT organization_id
FROM ps_ubkt_dtl pud2
WHERE pud2.inventory_item_id = pmh.inventory_item_id
AND pud2.matl_rep_id = G_matl_rep_id)));
| PROCEDURE NAME INSERT_HEADER_DATA |
| |
| DESCRIPTION Procedure to insert data into ps_matl_hdr |
| This Procedure fetches data for the Header Table by |
| building the Where condition based on the User and the|
| Planning Classes and then inserts into the Header |
| Table by creating a record group |
| |
| MODIFICATION HISTORY |
| 05/04/04 Rameshwar ----- created |
| |
+============================================================================*/
Procedure insert_header_data IS
x_select VARCHAR2(2000);
x_select := ' SELECT DISTINCT '||
' msi.inventory_item_id, '||
' msi.organization_id, ';
x_select := x_select || ' mca.category_id category_id';
x_select := x_select || ' -999 category_id ';
x_select := x_select || ' FROM ';
x_select := x_select || ' mtl_planners mpl, ';
x_select := x_select || ' hr_employees hem, ';
x_select := x_select || ' mtl_parameters mpa, ';
x_select := x_select || ' mtl_categories_kfv mca, ';
x_select := x_select ||
' mtl_system_items_kfv msi, '||
' mtl_item_categories mic, '||
' ps_schd_dtl psd, '||
' mtl_category_sets mcs '||
' WHERE '||
' mcs.category_set_id = to_char(:category_set_id) '||
' AND mcs.structure_id = to_char(:structure_id) '||
' AND mic.category_set_id = mcs.category_set_id '||
' AND psd.schedule_id = to_char(:schedule_id) '||
' AND psd.organization_id = msi.organization_id '||
' AND mic.inventory_item_id = msi.inventory_item_id '||
' AND mic.organization_id = msi.organization_id ';
x_select := x_select || ' AND mcs.structure_id = mca.structure_id '||
' AND mic.category_id = mca.category_id ';
x_select := x_select || ' AND mca.concatenated_segments >= :f_category ';
x_select := x_select || ' AND mca.concatenated_segments <= :t_category ';
x_select := x_select || ' AND mpl.planner_code = msi.planner_code '||
' AND mpl.organization_id = msi.organization_id ';
x_select := x_select || ' AND msi.planner_code >= :f_planner ';
x_select := x_select || ' AND msi.planner_code <= :t_planner ';
x_select := x_select || ' AND hem.employee_id = msi.buyer_id ';
x_select := x_select || ' AND hem.full_name >= :f_buyer ';
x_select := x_select || ' AND hem.full_name <= :t_buyer ';
x_select := x_select || ' AND mpa.organization_id = msi.organization_id ';
x_select := x_select || ' AND mpa.organization_code >= :f_org ';
x_select := x_select || ' AND mpa.organization_code <= :t_org ';
x_select := x_select || ' AND msi.concatenated_segments >= :f_item ';
x_select := x_select || ' AND msi.concatenated_segments <= :t_item ';
dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
SELECT gmp_matl_rep_id_s.NEXTVAL INTO X_rep_id FROM dual;
INSERT INTO ps_matl_hdr (matl_rep_id,inventory_item_id,organization_id,category_id)
VALUES(X_rep_id,X_item_id,X_org_id,X_category_id);
END insert_header_data;
SELECT 1
FROM FND_DUAL
WHERE EXISTS (SELECT matl_rep_id
FROM ps_ubkt_dtl
WHERE matl_rep_id = G_matl_rep_id) ;
SELECT order_ind
FROM ps_schd_hdr
WHERE schedule_id = G_schedule_id;
SELECT batch_no
FROM gme_batch_header
WHERE batch_type = 10
AND batch_id = G_doc_id
AND organization_id = V_organization_id
AND delete_mark = 0;
SELECT batch_no
FROM gme_batch_header
WHERE batch_type = 0
AND batch_id = G_doc_id
AND organization_id = V_organization_id
AND delete_mark = 0;
SELECT order_no
FROM op_ordr_hdr
WHERE order_id = G_doc_id
AND orgn_code = G_orgn_code;
SELECT DISTINCT oh.order_number
FROM oe_order_headers_all oh,
oe_order_lines_all ol
WHERE oh.header_id = ol.header_id
AND inv_salesorder.get_salesorder_for_oeheader(ol.header_id) = G_doc_id
AND ol.open_flag = 'Y'
AND ol.visible_demand_flag = 'Y' /*B4905079 - Flag to ensure that available_to_mrp = 1 */
AND decode(ol.source_document_type_id, 10, 8, decode(ol.line_category_code, 'ORDER',2,12)) IN (2,8);
SELECT DISTINCT oh.order_number
FROM oe_order_headers_all oh,
oe_order_lines_all ol
WHERE oh.header_id = ol.header_id
AND inv_salesorder.get_salesorder_for_oeheader(ol.header_id) = G_doc_id
AND ol.open_flag = 'Y'
AND ol.visible_demand_flag = 'Y' /*B4905079 - Flag to ensure that available_to_mrp = 1 */
AND decode(ol.source_document_type_id, 10, 8, decode(ol.line_category_code, 'ORDER',2,12)) IN (2,8)
AND nvl(ol.source_document_type_id, 0) <> 10 ;
SELECT po.po_number
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_PO_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
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 po.po_header_id = G_doc_id ;
SELECT po.requisition_number
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_REQ_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
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 po.requisition_header_id = G_doc_id ;
SELECT ph.segment1
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_HEADERS_ALL ph,
PO_RCV_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
AND po.po_header_id = ph.po_header_id
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 po.po_header_id = G_doc_id
AND G_doc_type = 'PRCV'
UNION ALL
SELECT rsh.receipt_num
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
RCV_SHIPMENT_HEADERS rsh,
PO_SHIP_RCV_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.shipment_header_id = rsh.shipment_header_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
AND po.shipment_header_id = G_doc_id ;
SELECT rsh.receipt_num
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
RCV_SHIPMENT_HEADERS rsh,
PO_SHIP_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.shipment_header_id = rsh.shipment_header_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
AND po.shipment_header_id = G_doc_id ;
SELECT transfer_no
FROM ic_xfer_mst
WHERE transfer_id = G_doc_id ;
X_select1 VARCHAR2(4000);
X_select VARCHAR2(25000);
x_select := x_select || ' SELECT gmd.material_requirement_date trans_date, '||
' DECODE(gbh.batch_type, 10,'||''''||'FPO'||''''||','||''''||'PROD'||''''||') doc_type, gbh.batch_id doc_id,'||
' DECODE(gmd.line_type, -1,-1,1) * '||
' DECODE(gmd.dtl_um, '||
' msi.primary_uom_code, '||
' 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, '||
' msi.primary_uom_code, '||
' 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, '||
' 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, '||
' msi.secondary_uom_code, '||
' NULL, '||
' NULL '||
' ))) trans_qty2, '||
' gmd.material_detail_id line_id, mp.organization_code inv_org_code'||
' FROM '||
' gme_batch_header gbh, '||
' gme_material_details gmd, '||
' mtl_parameters mp, '||
' hr_organization_units hou, '||
' 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 = TO_CHAR(:item_id) '||
' AND hou.organization_id = mp.organization_id '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' AND gmd.material_requirement_date >= nvl(:start_date, gmd.material_requirement_date - 1) '||
' AND gmd.material_requirement_date <= nvl(:end_date, gmd.material_requirement_date + 1) '||
' AND gbh.organization_id = TO_CHAR(:organization_id) ';
x_select := x_select ||
' UNION ALL '||
' SELECT ' ||
' mtl.requirement_date trans_date, ' ||
''''||'OMSO'||''''||' doc_type, mtl.demand_source_header_id doc_id, '||
' mtl.primary_uom_quantity * (-1) trans_qty, '||
' DECODE(items.dual_uom_control,0,0, '||
' (-1) * inv_convert.inv_um_convert(mtl.inventory_item_id, '||
' NULL, '||
' org.organization_id, '||
' NULL, '||
' mtl.primary_uom_quantity , '||
' items.primary_uom_code, '||
' items.secondary_uom_code, '||
' NULL, '||
' NULL '||
' )) trans_qty2, '||
' dtl.line_id line_id, org.organization_code inv_org_code '||
'FROM '||
' mtl_demand_omoe mtl, '||
' mtl_system_items items, '||
' oe_order_headers_all hdr, '||
' oe_order_lines_all dtl, '||
' hr_organization_units hou, '||
' mtl_parameters org '||
' WHERE '||
' mtl.inventory_item_id = TO_CHAR(:item_id) '||
' AND hou.organization_id = org.organization_id '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' AND mtl.organization_id = TO_CHAR(:organization_id) '||
' 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 mtl.requirement_date >= nvl(:start_date, mtl.requirement_date - 1) '||
' AND mtl.requirement_date <= nvl(:end_date, mtl.requirement_date + 1) '||
' 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 '||
' SELECT '||
' dtl.forecast_date trans_date, '||
' '||''''||'FCST'||''''||' doc_type, NULL doc_id, '||
' (-1) * dtl.current_forecast_quantity trans_qty, '||
' DECODE(msi.dual_uom_control,0,0, '||
' (-1) * inv_convert.inv_um_convert(dtl.inventory_item_id, '||
' NULL, '||
' dtl.organization_id, '||
' NULL, '||
' dtl.current_forecast_quantity, '||
' msi.primary_uom_code, '||
' msi.secondary_uom_code, '||
' NULL, '||
' NULL '||
' )) trans_qty2, '||
' 0 line_id, mp.organization_code inv_org_code '||
' FROM '||
' ps_schd_for psf, '||
' mrp_forecast_designators mff, '||
' mrp_forecast_dates dtl, '||
' mtl_system_items msi, '||
' hr_organization_units hou, '||
' mtl_parameters mp '||
' WHERE dtl.inventory_item_id = TO_CHAR(:item_id) '||
' AND psf.schedule_id = TO_CHAR(:schedule_id) '||
' AND psf.organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mp.organization_id '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' 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 >= nvl(:start_date, dtl.forecast_date - 1) '||
' AND dtl.forecast_date <= nvl(:end_date, dtl.forecast_date + 1) '||
' AND dtl.forecast_date >= fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate)) '||
' UNION ALL '||
' SELECT po.expected_delivery_date trans_date, '||''''||'PORD'||''''||' doc_type, '||
' po.po_header_id doc_id, '||
' po.to_org_primary_quantity trans_qty,'||
' DECODE(mitem.dual_uom_control,0,0, '||
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' mitem.secondary_uom_code, '||
' NULL, '||
' NULL)) trans_qty2, '||
' po.po_line_location_id line_id, mtl.organization_code inv_org_code '||
' FROM MTL_PARAMETERS mtl, '||
' hr_organization_units hou, '||
' po_po_supply_view po, mtl_system_items mitem '||
' WHERE po.item_id = TO_CHAR(: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 po.to_organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mtl.organization_id '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
' 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 ) '||
' UNION ALL '||
' SELECT po.expected_delivery_date, '||''''||'PREQ'||''''||' , '||
' po.requisition_header_id, '||
' po.to_org_primary_quantity,'||
' DECODE(mitem.dual_uom_control,0,0, '||
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' mitem.secondary_uom_code, '||
' NULL, '||
' NULL )) trans_qty2, '||
' po.req_line_id, mtl.organization_code '||
' FROM MTL_PARAMETERS mtl, '||
' hr_organization_units hou, '||
' po_req_supply_view po, mtl_system_items mitem '||
' WHERE po.item_id = TO_CHAR(: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 po.to_organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mtl.organization_id '||
' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' 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 ) '||
' UNION ALL'||
' SELECT po.expected_delivery_date trans_date, '||''''||'PRCV'||''''||' doc_type, '||
' po.po_header_id doc_id, '||
' po.to_org_primary_quantity trans_qty,'||
' DECODE(mitem.dual_uom_control,0,0, '||
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' mitem.secondary_uom_code, '||
' NULL, '||
' NULL)) trans_qty2, '||
' po.po_line_id line_id, mtl.organization_code inv_org_code '||
' FROM MTL_PARAMETERS mtl, '||
' hr_organization_units hou, '||
' po_rcv_supply_view po, mtl_system_items mitem '||
' WHERE po.item_id = TO_CHAR(: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 po.to_organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mtl.organization_id '||
' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' 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 ) '||
' UNION ALL'||
' SELECT po.expected_delivery_date trans_date, '||''''||'PRCV'||''''||' doc_type ,'||
' po.shipment_header_id doc_id, '||
' po.to_org_primary_quantity trans_qty,'||
' DECODE(mitem.dual_uom_control,0,0, '||
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' mitem.secondary_uom_code, '||
' NULL, '||
' NULL)) trans_qty2, '||
' po.shipment_line_id line_id, mtl.organization_code inv_org_code '||
' FROM MTL_PARAMETERS mtl, '||
' hr_organization_units hou, '||
' po_ship_rcv_supply_view po, mtl_system_items mitem '||
' WHERE po.item_id = TO_CHAR(: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 po.to_organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mtl.organization_id '||
' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' UNION ALL'||
' SELECT po.expected_delivery_date trans_date, '||''''||'SHMT'||''''||' doc_type,'||
' po.shipment_header_id doc_id, '||
' po.to_org_primary_quantity trans_qty,'||
' DECODE(mitem.dual_uom_control,0,0, '||
' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
' NULL, '||
' mitem.organization_id, '||
' NULL, '||
' po.to_org_primary_quantity, '||
' mitem.primary_uom_code, '||
' mitem.secondary_uom_code, '||
' NULL, '||
' NULL)) trans_qty2, '||
' po.shipment_line_id line_id, mtl.organization_code inv_org_code '||
' FROM MTL_PARAMETERS mtl, '||
' hr_organization_units hou, '||
' po_ship_supply_view po, mtl_system_items mitem '||
' WHERE po.item_id = TO_CHAR(: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 mtl.organization_id = TO_CHAR(:organization_id) '||
' AND hou.organization_id = mtl.organization_id '||
' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
' ORDER BY 1 ASC, 4 DESC';
dbms_sql.parse(X_doc,X_select,dbms_sql.NATIVE);
SELECT SYSDATE INTO X_date FROM dual;
INSERT INTO ps_ubkt_dtl(matl_rep_id,
-- item_id,
inventory_item_id,
-- planning_class,
-- whse_code,
organization_id,
start_balance,
past_due,
trans_date,
doc_type,
-- orgn_code,
doc_no,
line_id,
trans_qty,
balance,
critical_ind,
cust_vend)
Values( G_matl_rep_id,
V_item_id,
-- V_planning_class,
-- G_doc_tab(X_i).whse_code,
V_organization_id,
G_start_balance,
X_pastdue,
G_doc_tab(X_i).trans_date,
G_doc_type,
-- G_doc_tab(X_i).orgn_code,
X_doc_no,
G_doc_tab(X_i).line_id,
G_doc_tab(X_i).trans_qty,
balance1,
G_c_ind,
G_cust_vend);
SELECT SYSDATE INTO X_date FROM dual;
INSERT INTO ps_ubkt_dtl(matl_rep_id,
-- item_id,
inventory_item_id,
-- planning_class,
-- whse_code,
organization_id,
start_balance,
past_due,
trans_date,
doc_type,
--- orgn_code,
doc_no,
line_id,
trans_qty,
balance,
critical_ind,
cust_vend)
Values( G_matl_rep_id,
V_item_id,
-- V_planning_class,
-- G_doc_tab(X_i).whse_code,
V_organization_id,
G_start_balance,
X_pastdue,
G_doc_tab(X_i).trans_date,
G_doc_type,
-- G_doc_tab(X_i).orgn_code,
X_doc_no,
G_doc_tab(X_i).line_id,
G_doc_tab(X_i).trans_qty,
balance1,
G_c_ind,
G_cust_vend);
G_doc_tab.delete;
DELETE FROM ps_matl_hdr
WHERE inventory_item_id = V_item_id
AND matl_rep_id = G_matl_rep_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_select Is Open');
SELECT NVL(nonnet_ind,0)
FROM ps_schd_hdr
WHERE schedule_id = V_schedule_id;
X_select1 VARCHAR2(2000);
X_select1 :=
' SELECT NVL(SUM(s1.safety_stock_quantity), 0) total_ss'||
' FROM mtl_safety_stocks s1 '||
' WHERE s1.organization_id = to_char(:org_id)'||
' AND s1.inventory_item_id = to_char(:item_id)'||
' AND (s1.effectivity_date <= SYSDATE '||
' AND s1.effectivity_date >= ( '||
' SELECT NVL(MAX(s2.effectivity_date), SYSDATE) '||
' FROM mtl_safety_stocks s2 '||
' WHERE s2.organization_id = s1.organization_id'||
' AND s2.inventory_item_id = to_char(:item_id)'||
' AND s2.effectivity_date <= SYSDATE)) ';
dbms_sql.parse(X_doc, X_select1,dbms_sql.NATIVE);
FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_select Is Open');
SELECT distinct cs.cust_no
FROM op_ordr_hdr op, op_ordr_dtl od, op_cust_mst cs
WHERE op.order_id = G_doc_id
AND op.order_id = od.order_id
AND od.line_id = G_tranline_id
AND od.shipcust_id = cs.cust_id;
SELECT UNIQUE pv.segment1
FROM MTL_PARAMETERS mtl,
PO_VENDORS pv,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_PO_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND pv.vendor_id = po.vendor_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
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 po.po_line_id = G_tranline_id ;
SELECT UNIQUE pv.segment1
FROM MTL_PARAMETERS mtl,
PO_VENDORS pv,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_RCV_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND pv.vendor_id = po.vendor_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
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 po.po_line_id = G_tranline_id
AND G_doc_type = 'PRCV'
UNION ALL
SELECT UNIQUE pv.segment1
FROM MTL_PARAMETERS mtl,
PO_VENDORS pv,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
RCV_SHIPMENT_HEADERS rsh,
PO_SHIP_RCV_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND pv.vendor_id = rsh.vendor_id
AND po.shipment_header_id = rsh.shipment_header_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
AND po.shipment_line_id = G_tranline_id ;
SELECT UNIQUE pv.segment1
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
PO_VENDORS pv,
RCV_SHIPMENT_HEADERS rsh,
PO_SHIP_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND pv.vendor_id(+) = rsh.vendor_id
AND po.shipment_header_id = rsh.shipment_header_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
AND po.shipment_line_id = G_tranline_id ;
SELECT SUBSTRB(prl.suggested_vendor_name,1,40)
FROM MTL_PARAMETERS mtl,
MTL_SYSTEM_ITEMS mitem,
-- IC_ITEM_MST ic,
-- IC_WHSE_MST iwm,
PO_REQUISITION_LINES_ALL prl,
PO_REQ_SUPPLY_VIEW po
WHERE po.item_id = mitem.inventory_item_id
AND po.req_line_id = prl.requisition_line_id
AND po.to_organization_id = mitem.organization_id
-- AND mitem.segment1 = ic.item_no
-- AND po.to_organization_id = iwm.mtl_organization_id
AND mtl.organization_id = po.to_organization_id
AND mtl.process_enabled_flag = 'Y'
AND mitem.inventory_item_flag = 'Y'
-- AND iwm.delete_mark = 0
-- AND ic.noninv_ind = 0
-- AND ic.experimental_ind = 0
-- AND ic.delete_mark = 0
-- AND iwm.orgn_code = G_orgn_code
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 po.req_line_id = G_tranline_id ;
SELECT DISTINCT sold_to_org.customer_number
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
oe_sold_to_orgs_v sold_to_org,
mtl_demand_omoe mtl
WHERE oh.header_id = ol.header_id
AND ol.line_id = mtl.demand_id
AND oh.sold_to_org_id = sold_to_org.organization_id(+)
AND mtl.demand_source_header_id = G_doc_id
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) ;
SELECT DISTINCT sold_to_org.customer_number
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
oe_sold_to_orgs_v sold_to_org,
mtl_demand_omoe mtl
WHERE oh.header_id = ol.header_id
AND ol.line_id = mtl.demand_id
AND oh.sold_to_org_id = sold_to_org.organization_id(+)
AND mtl.demand_source_header_id = G_doc_id
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 nvl(ol.source_document_type_id, 0) <> 10 ;
x_stmt := ' SELECT ' ||
' gmpmpact.organization_code( '||G_orgnanization_id||') master_org, ' ||
' gmpmpact.schedule( '||G_schedule_id||') schedule, ' ||
' gmpmpact.category_set( '||G_category_set_id||') category_set, ' ||
''''||G_fcategory||''''||' fcategory, ' ||
''''||G_tcategory||''''||' tcategory, ' ||
''''||G_fbuyer||''''||' fbuyer, ' ||
''''||G_tbuyer||''''||' tbuyer, ' ||
''''||G_fplanner||''''||' fplanner, ' ||
''''||G_tplanner||''''||' tplanner, ' ||
''''||G_forg||''''||' forg, ' ||
''''||G_torg||''''||' torg, ' ||
''''||G_fitem||''''||' fitem, ' ||
''''||G_titem||''''||' titem, ' ||
''''||G_ftrans_date||''''||' fdate, ' ||
''''||G_ttrans_date||''''||' tdate, ' ||
' CURSOR( ' ||
' SELECT ' ||
' gmpmpact.item_name(pmh.inventory_item_id, pmh.organization_id) item_name, ' ||
' gmpmpact.organization_code (pmh.organization_id) organization_code, ' ||
' gmpmpact.planner_code (pmh.inventory_item_id, pmh.organization_id) planner_code, ' ||
' gmpmpact.buyer_name (pmh.inventory_item_id, pmh.organization_id) buyer_name, ' ||
' gmpmpact.onhand_qty (pmh.inventory_item_id, pmh.organization_id) onhand_qty, ' ||
' gmpmpact.unit_of_measure(pmh.inventory_item_id, pmh.organization_id) primary_uom_code, ' ||
' gmpmpact.category(pmh.category_id) category, ' ||
' CURSOR( ' ||
' SELECT pud.line_id line_id, ' ||
' pud.matl_rep_id matl_rep_id, ' ||
' pud.doc_type doc_type, ' ||
' pud.doc_no doc_no, ' ||
' pud.start_balance start_balance, ' ||
' pud.past_due past_due, ' ||
' pud.trans_date trans_date, ' ||
' pud.trans_qty trans_qty, ' ||
' pud.balance balance, ' ||
' pud.critical_ind critical_ind, ' ||
' pud.cust_vend cust_vend, ' ||
' pud.inventory_item_id inventory_item_id, ' ||
' gmpmpact.organization_code (pud.organization_id) organization_code ' ||
' FROM ps_ubkt_dtl pud ' ||
' WHERE pud.inventory_item_id = pmh.inventory_item_id ' ||
' AND pud.organization_id = pmh.organization_id ' ||
' AND pud.matl_rep_id = pmh.matl_rep_id ' ||
' ORDER BY pud.inventory_item_id, pud.organization_id, pud.trans_date, pud.doc_type ' ||
' ) DETAIL ' ||
' FROM ps_matl_hdr pmh ' ||
' WHERE pmh.matl_rep_id = ' ||G_matl_rep_id||
' ORDER BY pmh.inventory_item_id, pmh.organization_id ' ||
' ) HEADER ' ||
' FROM DUAL ';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_unbucketed_xml_temp(ubckt_matl_xml_id, xml_file) VALUES(x_seq_num, result);
SELECT schedule INTO v_schedule_name
FROM ps_schd_hdr
WHERE schedule_id = p_schedule_id;
SELECT category_set_name INTO v_category_set_name
FROM mtl_category_sets
WHERE category_set_id = p_category_set_id;
SELECT concatenated_segments INTO v_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT organization_code INTO v_org_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT planner_code INTO v_planner_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT he.full_name INTO v_buyer_name
FROM mtl_system_items msi, hr_employees he
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND msi.buyer_id = he.employee_id;
SELECT primary_uom_code INTO v_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT concatenated_segments INTO v_category
FROM mtl_categories_kfv
WHERE category_id = p_category_id;
DELETE FROM gmp_unbucketed_xml_temp WHERE ubckt_matl_xml_id = p_sequence_num;
SELECT xml_file INTO l_file
FROM gmp_unbucketed_xml_temp
WHERE ubckt_matl_xml_id = p_sequence_num;