[Home] [Help]
387: CSI_COUNTERS_B CBS,
388: CSI_COUNTER_READINGS CCR1,
389: CSI_COUNTER_READINGS CCR2,
390: csi_item_instances cii,
391: wip_discrete_jobs wdj,
392: (
393: SELECT
394: *
395: FROM csi_counter_associations
497: msik.primary_uom_code as "UOM",
498: lu.meaning as "SUPPLYTYPE")) AssetBom
499: from bom_inventory_components bic,
500: mtl_system_items_kfv msik,
501: wip_discrete_jobs wdj,
502: csi_item_instances cii,
503: bom_bill_of_materials bbom,
504: mfg_lookups lu
505: where bic.effectivity_date <= sysdate
528: msik.primary_uom_code as "UOM",
529: lu.meaning as "SUPPLYTYPE")) AssetBom
530: from bom_inventory_components bic,
531: mtl_system_items_kfv msik,
532: wip_discrete_jobs wdj,
533: bom_bill_of_materials bbom,
534: mfg_lookups lu
535: where bic.effectivity_date <= sysdate
536: and (bic.disable_date >= sysdate or
585: for i in p_wip_entity_id.FIRST..p_wip_entity_id.LAST
586: loop
587:
588: select wdj.organization_id into l_org_id
589: from wip_discrete_jobs wdj
590: where wdj.wip_entity_id=p_wip_entity_id(i);
591:
592:
593: --begin changes for FP 7493388 (base bug 7005666)
592:
593: --begin changes for FP 7493388 (base bug 7005666)
594:
595: select wdj.ASSET_NUMBER, wdj.ASSET_GROUP_ID into l_serial_number ,l_inventory_id
596: from wip_discrete_jobs wdj
597: where wdj.wip_entity_id=p_wip_entity_id(i);
598:
599:
600: SELECT nvl(instance_number,'hghg') into l_instance_number
777: select we.wip_entity_name,
778: wdj.organization_id,
779: msi.concatenated_segments,
780: cii.instance_number,cii.serial_number into l_wip_entity_name,l_organization_id,l_asset_group,l_instance_number,l_serial_number
781: from wip_discrete_jobs wdj,
782: csi_item_instances cii,
783: mtl_system_items_b_kfv msi,
784: wip_entities we
785: where wdj.wip_entity_id=p_wip_entity_id(i)
790:
791: begin
792: select msi.concatenated_segments into l_asset_activity
793: from mtl_system_items_b_kfv msi,
794: wip_discrete_jobs wdj
795: where wdj.primary_item_id=msi.inventory_item_id and
796: wdj.organization_id=msi.organization_id and
797: wdj.wip_entity_id=p_wip_entity_id(i) ;
798: exception
862: eafc.comments as "COMMENTS")) into l_xmlTypeFailureData
863: from eam_asset_failures eaf,
864: eam_asset_failure_codes eafc,
865: eam_work_order_details ewod,
866: wip_discrete_jobs wdj,
867: eam_failure_set_associations easa,
868: eam_failure_sets efs
869: where wdj.wip_entity_id =eaf.source_id and
870: eaf.source_type=1 and
894:
895: --Adding Warranty Expiration Date
896: begin
897: select XMLConcat(XMLELEMENT("WARRANTYEXPDATE",fnd_date.date_to_displayDT(Convert_to_client_time(csi.SUPPLIER_WARRANTY_EXP_DATE))),l_xmlTemp3) into l_xmlTemp3
898: from csi_item_instances csi, wip_discrete_jobs wdj
899: where wdj.maintenance_object_id=csi.instance_id
900: and wdj.maintenance_object_type=3
901: and wdj.wip_entity_id=p_wip_entity_id(i);
902: exception
912: WHERE TRANSACTION_TYPE=1
913: AND TRANSACTION_ID=
914: (
915: SELECT MAX(TRANSACTION_ID)
916: FROM EAM_JOB_COMPLETION_TXNS EJT,WIP_DISCRETE_JOBS WDJ
917: WHERE EJT.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
918: AND EJT.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
919: AND WDJ.WIP_ENTITY_ID=p_wip_entity_id(i)
920: );
926:
927: --Adding Asset Group Description
928: begin
929: select XMLConcat(XMLELEMENT("ASSETGRPDESC",msi.description),l_xmlTemp3) into l_xmlTemp3
930: from mtl_system_items_kfv msi, wip_discrete_jobs wdj
931: where nvl(wdj.asset_group_id,wdj.rebuild_item_id)=msi.inventory_item_id
932: and wdj.organization_id=msi.organization_id
933: and wdj.wip_entity_id=p_wip_entity_id(i);
934: exception
938:
939: --Adding Area Info
940: begin
941: select XMLConcat(XMLFOREST(mel.location_codes as "AREA" ,mel.description as "AREADESC"),l_xmlTemp3) into l_xmlTemp3
942: from eam_org_maint_defaults eomd, mtl_eam_locations mel, wip_discrete_jobs wdj
943: where eomd.object_id = wdj.maintenance_object_id
944: and eomd.object_type = 50
945: and eomd.organization_id =wdj.organization_id
946: and wdj.wip_entity_id=p_wip_entity_id(i)