<dataTemplate name="WOBB" description="Work Order Header and Body " version="1.0" defaultPackage="WO_PKG">
<parameters>
<parameter name ="p_Workorder_From" dataType="character"/>
</parameters>
<property name="fo-keep-empty-inline">false</property>
<dataQuery>
<sqlStatement name="Q_ReportHeader">
SELECT hp.party_name Company_Name,
Mp.organization_code Comp_Num,
DECODE (INSTR(mtl.segment1, wsg.schedule_group_name), 0,
pjm_project.all_task_idtonum (wdj.task_id)
|| ' '|| mtl.segment1
|| wsg.schedule_group_name,pjm_project.all_task_idtonum (wdj.task_id)
|| mtl.segment1) Item_Number,
wsg.schedule_group_name Running_Number,
mtl.description Item_Description,
we.wip_entity_name Work_Order_No,
mtl.segment1 Drawing_No,
to_char(wdj.scheduled_start_date,'DD-MM-YY HH24:MI') Planned_start_date,
NVL (pjm_project.all_task_idtoname(wdj.task_id),
SUBSTR (mtl.segment1, 0, INSTR (mtl.segment1, 'TP') - 1)
) Internal_Order_name,
to_char(wdj.scheduled_completion_date ,'DD-MM-YY HH24:MI') Planned_end_date,
mtl. wip_supply_locator_id || ' '|| mtl.wip_supply_subinventory Raw_material_warehouse,
wdj.completion_locator_id finish_goods_subinv,
wdj.start_quantity Quantity_ordered,
mtl.unit_weight || ' '|| mtl.weight_uom_code Weight,
wdj.QUANTITY_COMPLETED Quantity_completed,
DECODE(XSRS.PRINT_FLAG_WO_STATUS, 'Duplicate', 'Duplicate', 'Original') wo_status
FROM wip_discrete_jobs wdj,
Mtl_parameters mp,
mtl_system_items_b mtl,
xle_entity_profiles xep,
hz_parties hp,
wip_schedule_groups wsg,
wip_entities we,
hr_organization_information hoi,
XXDIS_SCO_REPORT_STATUS XSRS
WHERE hp.party_id = xep.party_id
AND wdj.organization_id = mtl.organization_id
AND wdj.primary_item_id = mtl.inventory_item_id
AND wdj.schedule_group_id = wsg.schedule_group_id
AND wdj.organization_id = wsg.organization_id
NVL(:p_Workorder_From,we.wip_entity_name)
</sqlStatement>
<sqlStatement name="Q_DATE">
SELECT to_char(sysdate,'DD-MM-YY HH24:MI') sys_date FROM DUAL
</sqlStatement>
<sqlStatement name="Q_DEPT_WC">
SELECT XXDPS_GBL_GENERIC_PKG.XXDPS_GBL_DEPT_FUNC(we.wip_entity_name) sled_pracovist_workcentre
FROM wip_entities we WHERE we.wip_entity_name=:p_Workorder_From
</sqlStatement>
<sqlStatement name="Q_RoutingHeader">
SELECT pjm_project.all_task_idtonum (x.parent_task_id)||mtl.segment1
|| wsg.schedule_group_name xx,
wdjo.start_quantity xxx,
mtl.description xxxx,
weo.wip_entity_name vo_parent_wo_number,
mfl.meaning stav_vo_parent_wo_status
,SUBSTR (xdis.bom_text, 1, 20) Quality_Plan_Identification
,SUBSTR (xdis.bom_text, 22, 24) Design_Contact
,SUBSTR (xdis.bom_text, 48, 14) Technilogy_Concatact
,NULL position
FROM wip_discrete_jobs wdjo,
wip_entities weo,
mtl_system_items_b mtl,
wip_schedule_groups wsg,
mfg_lookups mfl,
(SELECT pa.parent_task_id, wdj.task_id, wdj.project_id
FROM wip_discrete_jobs wdj, wip_entities we, pa_tasks pa
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.wip_entity_name = NVL(:p_Workorder_From, we.wip_entity_name)
AND wdj.task_id = pa.task_id
AND wdj.project_id = pa.project_id) x,
(SELECT xxdis_sco_generic_pkg.xxdis_sco_bom_text(:p_Workorder_From) bom_text FROM DUAL) xdis
WHERE wdjo.wip_entity_id = weo.wip_entity_id
AND wdjo.organization_id = weo.organization_id
AND x.parent_task_id(+) = wdjo.task_id
AND wdjo.project_id = x.project_id(+)
</sqlStatement>
<sqlStatement name="Q_RoutingEntryHeader">
SELECT DISTINCT wo.operation_seq_num operation_num,
bso.operation_code activity,
bso.operation_description activity_description,
bd.description workcenter, br.resource_code machine_num,
TO_CHAR (wo.first_unit_start_date,
'DD-MM-YY HH24:MI'
) planned_start_date,
TO_CHAR (wo.last_unit_completion_date,
'DD-MM-YY HH24:MI'
) planned_end_date,
DECODE (ca.activity,
'Run', bor.usage_rate_or_amount,
0
) preparation_time,
we.wip_entity_name work_order_no,
DECODE
(ca.activity,
'Prerun', bor.usage_rate_or_amount,
0
)
|| '/'
|| bor.assigned_units run_time_or_num_of_workers,
( ( DECODE (ca.activity,
'Run', bor.usage_rate_or_amount,
0
)
+ DECODE (ca.activity,
'Prerun', bor.usage_rate_or_amount,
0
)
)
* bor.assigned_units
/ 60
) total_time_in_hours,
xxdps_sco_total.grand_total grand_total
FROM wip_discrete_jobs wdj,
wip_entities we,
mtl_system_items msi,
bom_departments bd,
wip_operations wo,
bom_operation_sequences bos,
bom_standard_operations bso,
bom_operation_resources bor,
bom_resources br,
cst_activities ca,
(SELECT we.wip_entity_name entity_name,
SUM
(( ( DECODE (ca.activity,
'Run', bor.usage_rate_or_amount,
0
)
+ DECODE (ca.activity,
'Prerun', bor.usage_rate_or_amount,
0
)
)
* bor.assigned_units
/ 60
)
) grand_total
FROM wip_discrete_jobs wdj,
wip_entities we,
wip_operations wo,
bom_operation_sequences bos,
bom_standard_operations bso,
bom_operation_resources bor,
bom_resources br,
cst_activities ca
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND wo.operation_sequence_id = bos.operation_sequence_id
AND bos.standard_operation_id =
bso.standard_operation_id
AND bor.operation_sequence_id = wo.operation_sequence_id
AND bor.resource_id = br.resource_id
AND ca.activity_id(+) = bor.activity_id
AND we.wip_entity_name BETWEEN :p_workorder_from
AND :p_workorder_from
GROUP BY we.wip_entity_name) xxdps_sco_total
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.primary_item_id = msi.inventory_item_id
AND wdj.organization_id = msi.organization_id
NVL(:p_Workorder_From,we.wip_entity_name)
</sqlStatement>
</dataQuery>
<dataStructure>
<group name ="G_ReportHeader" source="Q_ReportHeader">
<element name="C_Company_Name" value="Company_Name"/>
<element name="C_CNum" value="Comp_Num"/>
<element name="C_Item_Number" value="Item_Number"/>
<element name="C_Running_Number" value="Running_Number"/>
<element name="C_Item_Description" value="Item_Description"/>
<element name="C_Work_Order_No" value="Work_Order_No"/>
<element name="C_Drawing_No" value="Drawing_No"/>
<element name="C_Internal_Order_name" value="Internal_Order_name"/>
<element name="C_Planned_start_date" value="Planned_start_date"/>
<element name="C_Planned_end_date" value="Planned_end_date"/>
<element name="C_Raw_material_warehouse" value="Raw_material_warehouse"/>
<element name="C_finish_goods_subinv" value="finish_goods_subinv"/>
<element name="C_Quantity_ordered" value="Quantity_ordered"/>
<element name="C_Weight" value="Weight"/>
<element name="C_Quantity_completed" value="Quantity_completed"/>
<element name="C_WO_STATUS" value="wo_status" />
</group>
<group name ="G_DATE" source="Q_DATE">
<element name="C_sys_date" value="sys_date"/>
</group>
<group name ="G_DEPT_XX" source="Q_DEPT_WC">
<element name="C_sled_pracovist_workcentre" value="sled_pracovist_workcentre"/>
</group>
<group name ="G_RoutingHeader" source="Q_RoutingHeader">
<element name="C_Item_Number" value="Item_Number"/>
<element name="C_Qty_ordered" value="Qty_ordered"/>
<element name="C_Parent_item_description" value="Parent_item_description"/>
<element name="C_vo_parent_wo_number" value="vo_parent_wo_number"/>
<element name="C_stav_vo_parent_wo_status" value="stav_vo_parent_wo_status"/>
<element name="C_Quality_Plan_Identification" value="Quality_Plan_Identification"/>
<element name="C_Design_Contact" value="Design_Contact"/>
<element name="C_Technilogy_Concatact" value="Technilogy_Concatact"/>
<element name="C_position" value="position"/>
</group>
<group name ="G_RoutingEntryHeader" source="Q_RoutingEntryHeader">
<element name="C_Operation_Num" value="Operation_Num"/>
<element name="C_Activity" value="Activity"/>
<element name="C_Activity_description" value="Activity_description"/>
<element name="C_Workcenter" value="Workcenter"/>
<element name="C_Machine_Num" value="Machine_Num"/>
<element name="C_Preparation_Time" value="Preparation_Time"/>
<element name="C_Run_time_or_Num_of_workers" value="Run_time_or_Num_of_workers"/>
<element name="C_Total_time_in_hours" value="Total_time_in_hours"/>
<element name="C_Work_Order_No" value="Work_Order_No"/>
<element name="C_Planned_Start_Date" value="Planned_Start_Date"/>
<element name="C_Planned_End_Date" value="Planned_End_Date"/>
<element name="C_grand_total" value="grand_total"/>
</group>
</dataStructure>
<dataTrigger name="afterReport" source="XX_PKG.xx_Print_lxx_func(:p_Workorder_From)"/>
</dataTemplate>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.