[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert_items;
FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After insert_items ');
PROCEDURE insert_items IS
x_select VARCHAR2(2000);
x_select := ' INSERT INTO gmp_pdr_items_gtmp ( '||
' organization_code, '||
' item_name, '||
' category_name, '||
' planner_code, '||
' buyer_name, '||
' abc_class_name, '||
' inventory_item_id, '||
' organization_id, '||
' base_item_id, '||
' standard_cost, '||
' calculate_atp, '||
' wip_supply_type, '||
' bom_item_type '||
' ) '||
' SELECT DISTINCT '||
' msi.organization_code, '||
' msi.item_name, '||
' mic.category_name, '||
' msi.planner_code, '||
' msi.buyer_name, '||
' msi.abc_class_name, '||
' msi.inventory_item_id, '||
' msi.organization_id, '||
' msi.base_item_id, '||
' msi.standard_cost, '||
' msi.calculate_atp, '||
' msi.wip_supply_type, '||
' msi.bom_item_type '||
' FROM '||
' msc_system_items msi '||
' , msc_item_categories mic ';
x_select := x_select || ' , msc_item_exceptions mie';
x_select := x_select || ' WHERE '||
' msi.sr_instance_id = :inst_id '||
' AND msi.plan_id = :plan_id '||
' AND mic.inventory_item_id = msi.inventory_item_id '||
' AND mic.organization_id = msi.organization_id '||
' AND mic.sr_instance_id = msi.sr_instance_id '||
' AND mic.category_set_id = :cat_set_id ' ;
x_select := x_select || ' AND msi.organization_id = :plan_org ';
x_select := x_select || ' AND msi.organization_id IN (SELECT organization_id FROM msc_plan_organizations '||
' WHERE plan_id = :plan_id AND '||
' sr_instance_id = :inst_id) ';
x_select := x_select || ' AND mic.category_name >= :category_low ';
x_select := x_select || ' AND mic.category_name <= :category_high ';
x_select := x_select || ' AND msi.inventory_item_id = mie.inventory_item_id '||
' AND msi.organization_id = mie.organization_id '||
' AND msi.sr_instance_id = mie.sr_instance_id '||
' AND mie.exception_type = :exception_type ';
x_select := x_select || ' AND msi.planner_code >= :planner_low ';
x_select := x_select || ' AND msi.planner_code <= :planner_high ';
x_select := x_select || ' AND msi.buyer_name >= :buyer_low ';
x_select := x_select || ' AND msi.buyer_name <= :buyer_high ';
x_select := x_select || ' AND msi.abc_class_name >= :abc_class_low ';
x_select := x_select || ' AND msi.abc_class_name <= :abc_class_high ';
x_select := x_select || ' AND msi.item_name >= :item_name_low ';
x_select := x_select || ' AND msi.item_name <= :item_name_high ';
x_select := x_select || ' ORDER BY :first_sort ';
x_select := x_select || ' , :second_sort ';
x_select := x_select || ' , :third_sort ';
x_select := x_select || ' ORDER BY msi.inventory_item_id, msi.organization_id ';
dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
END insert_items;
G_horiz_plan_stmt := ' SELECT '||
' organization_id , '||
' inventory_item_id , '||
' bucket_date , '||
' quantity1 , '||
' quantity2 , '||
' quantity3 , '||
' quantity4 , '||
' quantity5 , '||
' quantity6 , '||
' quantity7 , '||
' quantity8 , '||
' quantity9 , '||
' quantity10 , '||
' quantity11 , '||
' quantity12 , '||
' quantity13 , '||
' quantity14 , '||
' quantity15 , '||
' quantity16 , '||
' quantity17 , '||
' quantity18 , '||
' quantity19 , '||
' quantity20 , '||
' quantity21 , '||
' quantity22 , '||
' quantity23 , '||
' quantity24 , '||
' quantity25 , '||
' quantity26 , '||
' quantity27 , '||
' quantity28 , '||
' quantity29 , '||
' quantity30 , '||
' quantity31 , '||
' quantity32 , '||
' quantity33 , '||
' quantity34 , '||
' quantity35 , '||
' quantity36 , '||
' quantity37 , '||
' quantity38 , '||
' quantity39 , '||
' quantity40 , '||
' quantity41 , '||
' quantity42 , '||
' quantity43 , '||
' quantity44 , '||
' quantity45 '||
' FROM '||
' ( SELECT '||
' ghp.organization_id , '||
' ghp.inventory_item_id , '||
' ghp.bucket_date , '||
' ghp.quantity1 , '||
' ghp.quantity2 , '||
' ghp.quantity3 , '||
' ghp.quantity4 , '||
' ghp.quantity5 , '||
' ghp.quantity6 , '||
' ghp.quantity7 , '||
' ghp.quantity8 , '||
' ghp.quantity9 , '||
' ghp.quantity10 , '||
' ghp.quantity11 , '||
' ghp.quantity12 , '||
' ghp.quantity13 , '||
' ghp.quantity14 , '||
' ghp.quantity15 , '||
' ghp.quantity16 , '||
' ghp.quantity17 , '||
' ghp.quantity18 , '||
' ghp.quantity19 , '||
' ghp.quantity20 , '||
' ghp.quantity21 , '||
' ghp.quantity22 , '||
' ghp.quantity23 , '||
' ghp.quantity24 , '||
' ghp.quantity25 , '||
' ghp.quantity26 , '||
' ghp.quantity27 , '||
' ghp.quantity28 , '||
' ghp.quantity29 , '||
' ghp.quantity30 , '||
' ghp.quantity31 , '||
' ghp.quantity32 , '||
' ghp.quantity33 , '||
' ghp.quantity34 , '||
' ghp.quantity35 , '||
' ghp.quantity36 , '||
' ghp.quantity37 , '||
' ghp.quantity38 , '||
' ghp.quantity39 , '||
' ghp.quantity40 , '||
' ghp.quantity41 , '||
' ghp.quantity42 , '||
' ghp.quantity43 , '||
' ghp.quantity44 , '||
' ghp.quantity45 '||
' FROM '||
' gmp_horizontal_pdr_gtmp ghp ) ';
' SELECT '||
' organization_code, '|| /* header show */
' item_name, '|| /* header show */
' category_name, '|| /* header show */
' planner_code, '|| /* header show */
' buyer_name, '|| /* header show */
' abc_class_name, '|| /* header show */
' planning_group, '||
' order_type, '||/* detail show */
' order_number, '||/* detail show */
' activity_date, '||/* detail show */
' quantity_rate, '||/* detail show */
' old_schd_date, '||
' order_placement_date, '||
' new_schedule_date, '||
' new_doc_date, '||
' new_wip_start_date, '||
' implement_as_id, '||
' firm_date, '||
' firm_qty, '||
' wip_qty, '||
' compression_days, '||
' using_assembly_item_name, '||
' designator, '||
' source_org, '||
' supplier_name, '||
' plan_name, '||
' plan_id, '||
' organization_id, '||
' sr_instance_id, '||
' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
' inventory_item_id, '||
' supplier_id, '||
' implement_as, '||
' implemented_qty '||
' FROM '||
'( SELECT '||
' gpi.organization_code organization_code, '|| /* header show */
' gpi.item_name item_name, '|| /* header show */
' gpi.category_name category_name, '|| /* header show */
' gpi.planner_code planner_code, '|| /* header show */
' gpi.buyer_name buyer_name, '|| /* header show */
' gpi.abc_class_name abc_class_name, '|| /* header show */
' sup.planning_group planning_group, '||
' l1.meaning order_type, '||/* detail show */
' DECODE (sup.order_type, '||
' 5, TO_CHAR (sup.transaction_id), '||
' sup.order_number '||
' ) order_number, '||/* detail show */
' cal.calendar_date activity_date, '||/* detail show */
' NVL (sup.daily_rate, sup.new_order_quantity) quantity_rate, '||/* detail show */
' sup.old_schedule_date old_schd_date, '||
' sup.new_order_placement_date order_placement_date, '||
' sup.new_schedule_date new_schedule_date, '||
' sup.new_dock_date new_doc_date, '||
' sup.new_wip_start_date new_wip_start_date, '||
' sup.implement_as implement_as_id, '||
' sup.firm_date firm_date, '||
' sup.firm_quantity firm_qty, '||
' NVL (sup.daily_rate, sup.new_order_quantity) - NVL (sup.quantity_in_process, 0) wip_qty, '||
' sup.schedule_compress_days compression_days, '||
' TO_CHAR (NULL) using_assembly_item_name, '||
' msc_get_name.designator (sup.schedule_designator_id) designator, '||
-- ' msc_get_name.designator (sup.schedule_designator_id), /* Since this column is selected twice, so removing one instance. */
' msc_get_name.org_code (sup.source_organization_id, '||
' sup.source_sr_instance_id '||
' ) source_org, '||
' msc_get_name.supplier (DECODE (sup.plan_id, '||
' -1, sup.supplier_id, '||
' DECODE (sup.order_type, '||
' 1, sup.supplier_id, '||
' 2, sup.supplier_id, '||
' sup.source_supplier_id '||
' ) '||
' ) '||
' ) supplier_name, '||
' mp.compile_designator plan_name, '||
' sup.plan_id plan_id, '||
' sup.organization_id organization_id, '||
' sup.sr_instance_id sr_instance_id, '||
' '||G_cat_set_id||' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
' sup.inventory_item_id inventory_item_id, '||
' sup.supplier_id supplier_id, '||
' DECODE (sup.implement_as, '||
' NULL, NULL, '||
' msc_get_name.lookup_meaning ('||''''||'MRP_WORKBENCH_IMPLEMENT_AS'||''''||', '||
' sup.implement_as '||
' ) '||
' ) implement_as, '||
' DECODE (sup.disposition_status_type, '||
' 2, 0.0, '||
' NVL (sup.daily_rate, sup.new_order_quantity) '||
' ) implemented_qty '||
' FROM '||
-- msc_item_categories mic,
-- ' msc_system_items msi,
' gmp_pdr_items_gtmp gpi, '||
' msc_supplies sup, '||
' msc_trading_partners mtp, '||
' msc_calendar_dates cal, '||
' mfg_lookups l1, '||
' msc_plans mp '||
' WHERE cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
' AND NVL (TRUNC (sup.last_unit_completion_date), '||
' TRUNC (sup.new_schedule_date) '||
' ) '||
' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
' AND cal.calendar_code = mtp.calendar_code '||
' AND cal.sr_instance_id = mtp.sr_instance_id '||
' AND mtp.sr_tp_id = sup.organization_id '||
' AND mtp.sr_instance_id = sup.sr_instance_id '||
' AND mtp.partner_type = 3 '||
' AND sup.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
' AND sup.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
' AND sup.organization_id = gpi.organization_id '||
' AND sup.inventory_item_id = gpi.inventory_item_id '||
' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
' AND l1.lookup_code = sup.order_type '||
' AND mp.plan_id = sup.plan_id '||
' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) '||
/* Global Var */
' UNION ALL '||
' SELECT '||
' gpi.organization_code organization_code, '|| /* header show */
' gpi.item_name item_name, '|| /* header show */
' gpi.category_name category_name, '|| /* header show */
' gpi.planner_code planner_code, '|| /* header show */
' gpi.buyer_name buyer_name, '|| /* header show */
' gpi.abc_class_name abc_class_name, '|| /* header show */
' dem.planning_group planning_group, '||
' l1.meaning order_type, '||/* detail show */
' NVL (dem.order_number, '||
' DECODE (dem.origination_type, '||
' 29, msc_get_name.scenario_designator (dem.forecast_set_id, '||
' dem.plan_id, '||
' dem.organization_id, '||
' dem.sr_instance_id '||
' ), '||
' msc_get_name.designator (dem.schedule_designator_id) '||
' ) '||
' ) order_number , '||/* detail show */
' cal.calendar_date activity_date , '||/* detail show */
' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) quantity_rate, '||/* detail show */
' dem.old_demand_date old_schd_date, '||
' TO_DATE (NULL) order_placement_date, '||
' dem.using_assembly_demand_date new_schedule_date, '||
' TO_DATE (NULL) new_doc_date, '||
' TO_DATE (NULL) new_wip_start_date, '||
' TO_NUMBER (NULL)implement_as_id, '||
' dem.firm_date firm_date, '||
' dem.firm_quantity firm_qty, '||
' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) - TO_NUMBER (NULL) wip_qty, '||
' TO_NUMBER (NULL) compression_days, '||
' msc_get_name.item_name (dem.using_assembly_item_id, NULL, NULL, NULL) using_assembly_item_name, '||
' DECODE (dem.schedule_designator_id, '||
' NULL, NULL, '||
' DECODE (dem.origination_type, '||
' 29, msc_get_name.forecastsetname (dem.forecast_set_id, '||
' dem.plan_id, '||
' dem.organization_id, '||
' dem.sr_instance_id '||
' ), '||
' msc_get_name.designator (dem.schedule_designator_id) '||
' ) '||
' ) designator, '||
/* DECODE (dem.schedule_designator_id,
NULL, NULL,
DECODE (dem.origination_type,
29, msc_get_name.forecastsetname (dem.forecast_set_id,
dem.plan_id,
dem.organization_id,
dem.sr_instance_id
),
msc_get_name.designator (dem.schedule_designator_id)
)
), */ /* Since this column is selected twice, so removing one instance. */
' msc_get_name.org_code (dem.source_organization_id, '||
' dem.source_org_instance_id '||
' ) source_org, '||
' NULL supplier_name, '||
' mp.compile_designator plan_name, '||
' dem.plan_id plan_id, '||
' dem.organization_id organization_id, '||
' dem.sr_instance_id sr_instance_id, '||
' '||G_cat_set_id||' cat_set_id, '||-- mic.category_set_id, /* Global Var */
' dem.inventory_item_id inventory_item_id, '||
' TO_NUMBER (NULL) supplier_id, '||
' TO_CHAR (NULL) implement_as, '||
' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) implemented_qty '||
' FROM '||
-- msc_item_categories mic,
-- ' msc_system_items msi,
' gmp_pdr_items_gtmp gpi, '||
' msc_demands dem, '||
' msc_trading_partners mtp, '||
' msc_calendar_dates cal, '||
' mfg_lookups l1, '||
' msc_plans mp '||
' WHERE cal.calendar_date BETWEEN TRUNC (dem.using_assembly_demand_date) '||
' AND NVL (TRUNC (dem.assembly_demand_comp_date), '||
' TRUNC (dem.using_assembly_demand_date) '||
' ) '||
' AND DECODE (dem.assembly_demand_comp_date, NULL, 1, cal.seq_num) IS NOT NULL '||
' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
' AND cal.calendar_code = mtp.calendar_code '||
' AND cal.sr_instance_id = mtp.sr_instance_id '||
' AND mtp.sr_tp_id = dem.organization_id '||
' AND mtp.sr_instance_id = dem.sr_instance_id '||
' AND mtp.partner_type = 3 '||
' AND dem.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
' AND dem.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
' AND dem.organization_id = gpi.organization_id '||
' AND dem.inventory_item_id = gpi.inventory_item_id '||
' AND NVL (dem.daily_demand_rate, dem.using_requirement_quantity) <> 0 '||
' AND l1.lookup_type = '||''''||'MSC_DEMAND_ORIGINATION'||''''||
' AND l1.lookup_code = dem.origination_type '||
' AND mp.plan_id = dem.plan_id '||
' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) )';
' SELECT '||
' organization_code, '||
' item_name, '||
' category_name, '|| /* header show */
' planner_code, '|| /* header show */
' buyer_name, '|| /* header show */
' abc_class_name, '|| /* header show */
' exception_id, '||
' inventory_item_id, '||
' organization_id, '||
' exception_type, '||
' exception_type_text, '||
' due_date, '||
' quantity, '||
' from_date, '||
' to_date, '||
' lot_number, '||
' department_line_code'||
' FROM '||
'( SELECT '||
' med.organization_code organization_code, '||
' gpi.item_name item_name, '||
' gpi.category_name category_name, '|| /* header show */
' gpi.planner_code planner_code, '|| /* header show */
' gpi.buyer_name buyer_name, '|| /* header show */
' gpi.abc_class_name abc_class_name, '|| /* header show */
' med.exception_id exception_id, '||
' gpi.inventory_item_id inventory_item_id, '||
' gpi.organization_id organization_id, '||
' med.exception_type exception_type, '||
' med.exception_type_text exception_type_text, '||
' med.due_date due_date, '||
' med.quantity quantity, '||
' med.from_date from_date, '||
' med.to_date to_date, '||
' med.lot_number lot_number, '||
' med.department_line_code department_line_code'||
' FROM msc_exception_details_v med, '||
' gmp_pdr_items_gtmp gpi '||
' WHERE med.plan_id = '||G_plan_id||
-- Bug: 7257708 Vpedarla changed the below two lines
-- ' WHERE med.exception_type IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,20,24,26,30) '||
-- ' AND TRUNC (med.to_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (med.to_date))) '||
' AND TRUNC (nvl(med.to_date,sysdate)) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (nvl(med.to_date,sysdate)))) '||
/* Global Var */
' AND med.sr_instance_id = '||G_inst_id||
' AND med.inventory_item_id = gpi.inventory_item_id '||
' AND nvl(med.category_set_id, '||G_cat_set_id ||') = '|| G_cat_set_id ||
' AND med.organization_id = gpi.organization_id )' ;
' SELECT '||
' inventory_item_id, '||
' organization_id, '||
' item_action, '||
' order_type, '||
' order_number, '||
' activity_date, '||
' old_schedule_date, '||
' new_order_placement_date, '||
' new_schedule_date, '||
' new_dock_date, '||
' new_wip_start_date, '||
' schedule_compress_days '||
' FROM '||
' ( SELECT '||
' gpi.inventory_item_id, '||
' gpi.organization_id, '||
' DECODE '||
' ( '||
' msc_get_name.action '||
' ( '||
' '||''''||'MSC_SUPPLIES'||''''||' , '||
' gpi.bom_item_type, '||
' gpi.base_item_id, '||
' gpi.wip_supply_type, '||
' sup.order_type, '||
' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
' sup.disposition_status_type, '||
' sup.new_schedule_date, '||
' sup.old_schedule_date, '||
' sup.implemented_quantity, '||
' sup.quantity_in_process, '||
' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
' ), '||
' '||''''||'None'||''''||' , DECODE '||
' ( '||
' SIGN (sup.new_schedule_date - sup.old_schedule_date), '||
' 1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,3), '||
' -1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,2), '||
' '||''''||'None'||''''||
' ), '||
' msc_get_name.action '||
' ( '||
' '||''''||'MSC_SUPPLIES'||''''||' , '||
' gpi.bom_item_type, '||
' gpi.base_item_id, '||
' gpi.wip_supply_type, '||
' sup.order_type, '||
' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
' sup.disposition_status_type, '||
' sup.new_schedule_date, '||
' sup.old_schedule_date, '||
' sup.implemented_quantity, '||
' sup.quantity_in_process, '||
' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
' ) '||
' ) item_action, '||
' l1.meaning order_type, '||
' DECODE (sup.order_type,5, TO_CHAR (sup.transaction_id),sup.order_number) order_number, '||
' cal.calendar_date activity_date, '||
' sup.old_schedule_date old_schedule_date, '||
' sup.new_order_placement_date new_order_placement_date, '||
' sup.new_schedule_date new_schedule_date, '||
' sup.new_dock_date new_dock_date, '||
' sup.new_wip_start_date new_wip_start_date, '||
' sup.schedule_compress_days schedule_compress_days '||
' FROM '||
' gmp_pdr_items_gtmp gpi, '||
' msc_supplies sup, '||
' msc_calendar_dates cal, '||
' msc_trading_partners mtp, '||
' mfg_lookups l1 '||
' WHERE '||
' cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
' AND NVL (TRUNC (sup.last_unit_completion_date), '||
' TRUNC (sup.new_schedule_date) '||
' ) '||
' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
' AND cal.calendar_code = mtp.calendar_code '||
' AND cal.sr_instance_id = mtp.sr_instance_id '||
' AND mtp.sr_tp_id = sup.organization_id '||
' AND mtp.sr_instance_id = sup.sr_instance_id '||
' AND mtp.partner_type = 3 '||
' AND sup.plan_id = '||G_plan_id|| -- gpi.plan_id /* Global Var */
' AND sup.sr_instance_id = '||G_inst_id|| -- gpi.sr_instance_id /* Global Var */
' AND sup.organization_id = gpi.organization_id '||
' AND sup.inventory_item_id = gpi.inventory_item_id '||
' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
' AND l1.lookup_code = sup.order_type '||
' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) ) ';
G_header_stmt := ' SELECT ' ||
' msc_get_name.org_code ('||G_org_id||', '||G_inst_id||' ) master_org, '||
' msc_get_name.instance_code ('||G_inst_id||' ) instance_code, '||
-- ''''||G_plan_name||''''||' plan_name, ' ||
' gmp_plng_dtl_report_pkg.plan_name plan_name, '||
' gmp_plng_dtl_report_pkg.plan_org ('||G_plan_org||' ) plan_org, '||
G_day_bucket||' day_bucket, '||
G_week_bucket||' week_bucket, '||
G_period_bucket||' period_bucket, ';
' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_fsort||' ) first_sort, ';
' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_ssort||' ) second_sort, ';
' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_tsort||' ) third_sort, ';
G_common_pdr_stmt := G_common_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name item_name, gpi.organization_code organization_code, '||
' gpi.category_name category_name, gpi.buyer_name buyer_name, gpi.planner_code planner_code, gpi.abc_class_name abc_class_name';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,1,x_seq_num );
G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name item_name, gpi.organization_code organization_code, '||
' gpi.category_name category_name, gpi.buyer_name buyer_name, gpi.planner_code planner_code, gpi.abc_class_name abc_class_name ';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,2,x_seq_num );
G_vert_pdr_stmt := G_vert_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,3,x_seq_num );
G_excep_pdr_stmt := G_excep_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,4,x_seq_num );
G_act_pdr_stmt := G_act_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,5,x_seq_num );
SELECT compile_designator
INTO plan_name
FROM msc_plans
WHERE plan_id = G_plan_id;
SELECT organization_code
INTO org_code
FROM gmp_plan_organization_v
WHERE organization_id = org_id;
SELECT organization_code
INTO org_code
FROM gmp_plan_organization_v
WHERE organization_id = org_id
AND plan_id = G_plan_id
AND sr_instance_id = G_inst_id;
SELECT category_set_name
INTO cat_set_name
FROM msc_category_sets
WHERE category_set_id = cat_set_id
AND sr_instance_id = G_inst_id;
SELECT meaning
INTO meaning_text
FROM fnd_lookup_values
WHERE language = userenv('LANG')
AND lookup_type = l_lookup_type
AND TO_NUMBER(lookup_code) = l_lookup_code;
DELETE FROM gmp_pdr_xml_temp WHERE pdr_xml_id = p_sequence_num;
SELECT xml_file INTO l_file
FROM gmp_pdr_xml_temp
WHERE pdr_xml_id = p_sequence_num;