[Home] [Help]
93: csc.name,
94: csc.service_code_id,
95: cii.quantity
96: BULK COLLECT INTO l_post_wipml_tbl
97: FROM csd_recall_lines crl,
98: wip_entities we,
99: wip_discrete_jobs wdj,
100: csd_service_codes_tl csc,
101: mtl_system_items_b msi,
390: cr.repair_number,
391: cs.incident_number,
392: cr.quantity,
393: msi.concatenated_segments
394: FROM csd_recall_lines crl,
395: csi_item_instances cii,
396: csd_repairs cr,
397: cs_incidents_all_b cs,
398: mtl_system_items_kfv msi
407:
408: -- print the header.
409: SELECT recall_number
410: INTO l_recall_number
411: FROM csd_recall_lines crl,
412: csd_recall_headers_b crh
413: WHERE crl.processing_group_id = p_group_id
414: AND crl.recall_id = crh.recall_id
415: AND rownum < 2;
526: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
527: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Update the recall lines as processing');
528: END IF;
529:
530: SELECT csd_recall_lines_group_s1.NEXTVAL INTO l_group_id FROM dual;
531:
532: -- update the selected recall line id's as processing flag = 'Y';
533: UPDATE CSD_RECALL_LINES crl SET processing_flag = 'Y', processing_group_id = l_group_id
534: WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
529:
530: SELECT csd_recall_lines_group_s1.NEXTVAL INTO l_group_id FROM dual;
531:
532: -- update the selected recall line id's as processing flag = 'Y';
533: UPDATE CSD_RECALL_LINES crl SET processing_flag = 'Y', processing_group_id = l_group_id
534: WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
535: AND nvl(crl.processing_flag,'N') = 'N';
536:
537: COMMIT;
557: THEN
558: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in launching CP');
559: END IF;
560:
561: UPDATE CSD_RECALL_LINES crl SET processing_flag = 'N'
562: WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
563: AND nvl(crl.processing_flag,'N') = 'Y'
564: AND crl.processing_group_id = l_group_id;
565:
587: /* Procedure Name: process_recall_work. */
588: /* Description: This is the concurrent wrapper to process a set of recall lines. */
589: /* Generates SR, RO and WIP jobs based on the params being passed. Logs */
590: /* all the error messages to error log, and will generate a report of all */
591: /* all the successful recall lines. Updates the csd_recall_lines table */
592: /* with the SR id, RO line id and wip entity id when done with the */
593: /* processing. Once done, will reset the processing_flag to N */
594: /* -- History: 24/03/2010, Created by Sudheer Bhat. */
595: /****************************************************************************************/
605: p_wip_inv_org_id IN NUMBER )
606: IS
607:
608: lc_api_name CONSTANT VARCHAR2(60) := 'CSD.PLSQL.CP.CSD_RECALLS_PVT.CSD_RECALLS_PVT';
609: l_csd_recall_lines_tbl csd_recall_lines_tbl;
610: l_create_ro_flag BOOLEAN := FALSE;
611: l_create_job_flag BOOLEAN := FALSE;
612: l_index NUMBER;
613: l_service_request_rec csd_process_pvt.service_request_rec := csd_process_util.sr_rec;
652: crl.repair_line_id,
653: crl.wip_entity_id,
654: cii.unit_of_measure,
655: cii.quantity
656: FROM csd_recall_lines crl,csi_item_instances cii
657: WHERE crl.processing_group_id = p_group_id
658: AND crl.processing_flag = 'Y'
659: AND crl.instance_id = cii.instance_id;
660:
682: g_upgrade_item_id := TO_NUMBER(SUBSTR(l_temp,INSTR(l_temp,',')+1));
683:
684: process_post_wip_massload(l_rec_group_id,l_req_group_id);
685:
686: UPDATE csd_recall_lines SET processing_flag = 'N'
687: WHERE processing_group_id = l_rec_group_id;
688: -- write the output.
689: write_to_conc_out(p_group_id);
690: RETURN;
696: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin PROCESS_RECALL_WORK API');
697: END IF;
698:
699: OPEN eligible_lines;
700: FETCH eligible_lines BULK COLLECT INTO l_csd_recall_lines_tbl;
701: CLOSE eligible_lines;
702: -- processing logic.
703: -- for all the recall lines, determine what all needs to be created.
704: -- till RO creation, we have to go row by row.
704: -- till RO creation, we have to go row by row.
705: -- for the wip job creation, we will insert all the eligible records into the interface table
706: -- and call wip mass load just once.
707:
708: FOR l_index IN 1 ..l_csd_recall_lines_tbl.COUNT
709: LOOP
710: IF l_csd_recall_lines_tbl(l_index).incident_id IS NULL
711: THEN
712: -- get the bill to site use id and ship to site use id.
706: -- and call wip mass load just once.
707:
708: FOR l_index IN 1 ..l_csd_recall_lines_tbl.COUNT
709: LOOP
710: IF l_csd_recall_lines_tbl(l_index).incident_id IS NULL
711: THEN
712: -- get the bill to site use id and ship to site use id.
713: IF g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
714: THEN
709: LOOP
710: IF l_csd_recall_lines_tbl(l_index).incident_id IS NULL
711: THEN
712: -- get the bill to site use id and ship to site use id.
713: IF g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
714: THEN
715: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
716: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting the bill to and ship to site from cache');
717: END IF;
715: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
716: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting the bill to and ship to site from cache');
717: END IF;
718: l_service_request_rec.bill_to_site_use_id :=
719: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
720: l_service_request_rec.ship_to_site_use_id :=
721: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
722: l_service_request_rec.caller_type :=
723: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
717: END IF;
718: l_service_request_rec.bill_to_site_use_id :=
719: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
720: l_service_request_rec.ship_to_site_use_id :=
721: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
722: l_service_request_rec.caller_type :=
723: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
724: ELSE
725: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
719: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
720: l_service_request_rec.ship_to_site_use_id :=
721: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
722: l_service_request_rec.caller_type :=
723: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
724: ELSE
725: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
726: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting bill to and ship to site.');
727: END IF;
726: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting bill to and ship to site.');
727: END IF;
728: SELECT hpu.party_site_use_id,
729: hpu1.party_site_use_id
730: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
731: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
732: FROM hz_party_sites hps,
733: hz_party_site_uses hpu,
734: hz_party_sites hps1,
727: END IF;
728: SELECT hpu.party_site_use_id,
729: hpu1.party_site_use_id
730: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
731: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
732: FROM hz_party_sites hps,
733: hz_party_site_uses hpu,
734: hz_party_sites hps1,
735: hz_party_site_uses hpu1
732: FROM hz_party_sites hps,
733: hz_party_site_uses hpu,
734: hz_party_sites hps1,
735: hz_party_site_uses hpu1
736: WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
737: AND hps.party_site_id = hpu.party_site_id
738: AND hpu.site_use_type = 'BILL_TO'
739: AND hpu.primary_per_type = 'Y'
740: AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
736: WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
737: AND hps.party_site_id = hpu.party_site_id
738: AND hpu.site_use_type = 'BILL_TO'
739: AND hpu.primary_per_type = 'Y'
740: AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
741: AND hps1.party_site_id = hpu1.party_site_id
742: AND hpu1.site_use_type = 'SHIP_TO'
743: AND hpu1.primary_per_type = 'Y'
744: AND rownum < 2;
743: AND hpu1.primary_per_type = 'Y'
744: AND rownum < 2;
745:
746: l_service_request_rec.bill_to_site_use_id :=
747: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
748: l_service_request_rec.ship_to_site_use_id :=
749: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
750:
751: SELECT party_type
745:
746: l_service_request_rec.bill_to_site_use_id :=
747: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
748: l_service_request_rec.ship_to_site_use_id :=
749: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
750:
751: SELECT party_type
752: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type
753: FROM hz_parties
748: l_service_request_rec.ship_to_site_use_id :=
749: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
750:
751: SELECT party_type
752: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type
753: FROM hz_parties
754: WHERE party_id = l_csd_recall_lines_tbl(l_index).owner_party_id;
755:
756: l_service_request_rec.caller_type :=
750:
751: SELECT party_type
752: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type
753: FROM hz_parties
754: WHERE party_id = l_csd_recall_lines_tbl(l_index).owner_party_id;
755:
756: l_service_request_rec.caller_type :=
757: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
758: END IF;
753: FROM hz_parties
754: WHERE party_id = l_csd_recall_lines_tbl(l_index).owner_party_id;
755:
756: l_service_request_rec.caller_type :=
757: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
758: END IF;
759:
760: -- Get other SR defaults.
761: l_service_request_rec.type_id := p_sr_type_id;
761: l_service_request_rec.type_id := p_sr_type_id;
762: l_service_request_rec.resource_type := g_sr_owner_type;
763: l_service_request_rec.sr_creation_channel := 'PHONE';
764: l_service_request_rec.request_date := sysdate;
765: l_service_request_rec.customer_id := l_csd_recall_lines_tbl(l_index).owner_party_id;
766: l_service_request_rec.account_id := l_csd_recall_lines_tbl(l_index).owner_account_id;
767: l_service_request_rec.customer_number := null;
768: l_service_request_rec.customer_product_id := null;
769: l_service_request_rec.cp_ref_number := null;
762: l_service_request_rec.resource_type := g_sr_owner_type;
763: l_service_request_rec.sr_creation_channel := 'PHONE';
764: l_service_request_rec.request_date := sysdate;
765: l_service_request_rec.customer_id := l_csd_recall_lines_tbl(l_index).owner_party_id;
766: l_service_request_rec.account_id := l_csd_recall_lines_tbl(l_index).owner_account_id;
767: l_service_request_rec.customer_number := null;
768: l_service_request_rec.customer_product_id := null;
769: l_service_request_rec.cp_ref_number := null;
770: l_service_request_rec.inv_item_revision := null;
845: csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
846: p_msg_data => x_msg_data);
847: RAISE fnd_api.g_exc_error;
848: END IF;
849: l_csd_recall_lines_tbl(l_index).incident_id := x_incident_id;
850:
851: UPDATE csd_recall_lines SET incident_id = x_incident_id
852: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
853: END IF;
847: RAISE fnd_api.g_exc_error;
848: END IF;
849: l_csd_recall_lines_tbl(l_index).incident_id := x_incident_id;
850:
851: UPDATE csd_recall_lines SET incident_id = x_incident_id
852: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
853: END IF;
854:
855: -- check if RO creation is required.
848: END IF;
849: l_csd_recall_lines_tbl(l_index).incident_id := x_incident_id;
850:
851: UPDATE csd_recall_lines SET incident_id = x_incident_id
852: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
853: END IF;
854:
855: -- check if RO creation is required.
856: IF l_csd_recall_lines_tbl(l_index).repair_line_id IS NULL AND p_ro_type_id IS NOT NULL
852: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
853: END IF;
854:
855: -- check if RO creation is required.
856: IF l_csd_recall_lines_tbl(l_index).repair_line_id IS NULL AND p_ro_type_id IS NOT NULL
857: THEN
858: -- get the repair attributes.
859: -- get the Pricelist.
860: -- prepare the rec and create RO.
873: -- get the contract
874: fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
875:
876: -- bug#9808614. if bill to and ship to are not yet cached, do so.
877: IF NOT g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
878: THEN
879: SELECT hpu.party_site_use_id,
880: hpu1.party_site_use_id
881: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
877: IF NOT g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
878: THEN
879: SELECT hpu.party_site_use_id,
880: hpu1.party_site_use_id
881: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
882: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
883: FROM hz_party_sites hps,
884: hz_party_site_uses hpu,
885: hz_party_sites hps1,
878: THEN
879: SELECT hpu.party_site_use_id,
880: hpu1.party_site_use_id
881: INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
882: g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
883: FROM hz_party_sites hps,
884: hz_party_site_uses hpu,
885: hz_party_sites hps1,
886: hz_party_site_uses hpu1
883: FROM hz_party_sites hps,
884: hz_party_site_uses hpu,
885: hz_party_sites hps1,
886: hz_party_site_uses hpu1
887: WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
888: AND hps.party_site_id = hpu.party_site_id
889: AND hpu.site_use_type = 'BILL_TO'
890: AND hpu.primary_per_type = 'Y'
891: AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
887: WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
888: AND hps.party_site_id = hpu.party_site_id
889: AND hpu.site_use_type = 'BILL_TO'
890: AND hpu.primary_per_type = 'Y'
891: AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
892: AND hps1.party_site_id = hpu1.party_site_id
893: AND hpu1.site_use_type = 'SHIP_TO'
894: AND hpu1.primary_per_type = 'Y'
895: AND rownum < 2;
904: p_init_msg_list => fnd_api.g_false,
905: p_commit => fnd_api.g_false,
906: p_contract_number => null,
907: p_service_line_id => null,
908: p_customer_id => l_csd_recall_lines_tbl(l_index).owner_party_id ,
909: p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910: p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911: p_system_id => null,
912: p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
905: p_commit => fnd_api.g_false,
906: p_contract_number => null,
907: p_service_line_id => null,
908: p_customer_id => l_csd_recall_lines_tbl(l_index).owner_party_id ,
909: p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910: p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911: p_system_id => null,
912: p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
913: p_customer_product_id => l_csd_recall_lines_tbl(l_index).instance_id,
906: p_contract_number => null,
907: p_service_line_id => null,
908: p_customer_id => l_csd_recall_lines_tbl(l_index).owner_party_id ,
909: p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910: p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911: p_system_id => null,
912: p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
913: p_customer_product_id => l_csd_recall_lines_tbl(l_index).instance_id,
914: p_request_date => trunc(sysdate),
908: p_customer_id => l_csd_recall_lines_tbl(l_index).owner_party_id ,
909: p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910: p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911: p_system_id => null,
912: p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
913: p_customer_product_id => l_csd_recall_lines_tbl(l_index).instance_id,
914: p_request_date => trunc(sysdate),
915: p_validate_flag => 'Y',
916: p_business_process_id => g_business_process_id,
909: p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910: p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911: p_system_id => null,
912: p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
913: p_customer_product_id => l_csd_recall_lines_tbl(l_index).instance_id,
914: p_request_date => trunc(sysdate),
915: p_validate_flag => 'Y',
916: p_business_process_id => g_business_process_id,
917: p_severity_id => g_sr_severity,
959: l_repln_rec.resource_group := fnd_api.g_miss_num;
960: l_repln_rec.ro_priority_code := fnd_api.g_miss_char;
961: l_repln_rec.resource_id := fnd_api.g_miss_num;
962:
963: l_repln_rec.incident_id := NVL(x_incident_id,l_csd_recall_lines_tbl(l_index).incident_id);
964: l_repln_rec.customer_product_id := l_csd_recall_lines_tbl(l_index).instance_id;
965: l_repln_rec.auto_process_rma := g_auto_process_rma;
966: l_repln_rec.approval_required_flag := 'Y';
967: l_repln_rec.repair_type_id := p_ro_type_id;
960: l_repln_rec.ro_priority_code := fnd_api.g_miss_char;
961: l_repln_rec.resource_id := fnd_api.g_miss_num;
962:
963: l_repln_rec.incident_id := NVL(x_incident_id,l_csd_recall_lines_tbl(l_index).incident_id);
964: l_repln_rec.customer_product_id := l_csd_recall_lines_tbl(l_index).instance_id;
965: l_repln_rec.auto_process_rma := g_auto_process_rma;
966: l_repln_rec.approval_required_flag := 'Y';
967: l_repln_rec.repair_type_id := p_ro_type_id;
968: l_repln_rec.repair_group_id := null;
967: l_repln_rec.repair_type_id := p_ro_type_id;
968: l_repln_rec.repair_group_id := null;
969: l_repln_rec.repair_mode := g_repair_mode;
970: l_repln_rec.status := 'O';
971: l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972: l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973: l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974: l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975: l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
968: l_repln_rec.repair_group_id := null;
969: l_repln_rec.repair_mode := g_repair_mode;
970: l_repln_rec.status := 'O';
971: l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972: l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973: l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974: l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975: l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
976:
969: l_repln_rec.repair_mode := g_repair_mode;
970: l_repln_rec.status := 'O';
971: l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972: l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973: l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974: l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975: l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
976:
977: -- we dont try to default the resolve by date. The create repair order public has an
970: l_repln_rec.status := 'O';
971: l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972: l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973: l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974: l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975: l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
976:
977: -- we dont try to default the resolve by date. The create repair order public has an
978: -- issue when resolve by date is not passed.
971: l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972: l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973: l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974: l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975: l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
976:
977: -- we dont try to default the resolve by date. The create repair order public has an
978: -- issue when resolve by date is not passed.
979: l_repln_rec.resolve_by_date := null;
1006: p_msg_data => x_msg_data);
1007: RAISE fnd_api.g_exc_error;
1008: END IF;
1009:
1010: l_csd_recall_lines_tbl(l_index).repair_line_id := x_repair_line_id;
1011:
1012: UPDATE csd_recall_lines SET repair_line_id = x_repair_line_id
1013: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
1014: END IF;
1008: END IF;
1009:
1010: l_csd_recall_lines_tbl(l_index).repair_line_id := x_repair_line_id;
1011:
1012: UPDATE csd_recall_lines SET repair_line_id = x_repair_line_id
1013: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
1014: END IF;
1015:
1016: -- default logistics lines.
1009:
1010: l_csd_recall_lines_tbl(l_index).repair_line_id := x_repair_line_id;
1011:
1012: UPDATE csd_recall_lines SET repair_line_id = x_repair_line_id
1013: WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
1014: END IF;
1015:
1016: -- default logistics lines.
1017: csd_process_pvt.create_default_prod_txn
1026: -- we wont check for the status of the logistics lines here. Even if the auto creation fails, we create all
1027: -- other possible entities. The logistics lines can be manually entered in later.
1028: -- WIP job creation
1029:
1030: IF l_csd_recall_lines_tbl(l_index).repair_line_id IS NOT NULL AND p_service_code_id IS NOT NULL
1031: THEN
1032: IF l_job_status = 0 THEN
1033: SELECT wip_job_status
1034: INTO l_job_status
1069: --l_job_header_index := l_job_header_index + 1;
1070: IF g_bill_id.COUNT = 0
1071: THEN
1072: l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
1073: l_job_header_tbl(l_job_header_index).source_line_id := l_csd_recall_lines_tbl(l_index).repair_line_id;
1074: l_job_header_tbl(l_job_header_index).organization_id := NVL(p_wip_inv_org_id,fnd_profile.value('csd_def_rep_inv_org'));
1075: l_job_header_tbl(l_job_header_index).status_type := NVL(l_job_status,1);
1076: l_job_header_tbl(l_job_header_index).load_type := 4;
1077: l_job_header_tbl(l_job_header_index).process_phase := 2;
1080: l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
1081: l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1082: l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1083: l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1084: l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1085: l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1086: l_job_header_tbl(l_job_header_index).first_unit_start_date := sysdate;
1087: l_job_header_tbl(l_job_header_index).last_unit_completion_date := sysdate + 1;
1088:
1081: l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1082: l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1083: l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1084: l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1085: l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1086: l_job_header_tbl(l_job_header_index).first_unit_start_date := sysdate;
1087: l_job_header_tbl(l_job_header_index).last_unit_completion_date := sysdate + 1;
1088:
1089: CSD_WIP_JOB_PVT.generate_job_name(g_job_prefix,l_job_header_tbl(l_job_header_index).organization_id,x_job_name);
1100: ELSE
1101: FOR l_job_index IN 1 ..g_bill_id.COUNT
1102: LOOP
1103: l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
1104: l_job_header_tbl(l_job_header_index).source_line_id := l_csd_recall_lines_tbl(l_index).repair_line_id;
1105: l_job_header_tbl(l_job_header_index).organization_id := NVL(p_wip_inv_org_id,fnd_profile.value('csd_def_rep_inv_org'));
1106: l_job_header_tbl(l_job_header_index).status_type := NVL(l_job_status,1);
1107: l_job_header_tbl(l_job_header_index).load_type := 4;
1108: l_job_header_tbl(l_job_header_index).process_phase := 2;
1111: l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
1112: l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1113: l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1114: l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1115: l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1116: l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1117: l_job_header_tbl(l_job_header_index).routing_reference_id := g_routing_id(l_job_index);
1118: l_job_header_tbl(l_job_header_index).bom_reference_id := g_bill_id(l_job_index);
1119: l_job_header_tbl(l_job_header_index).alternate_routing_designator := g_alt_routing(l_job_index);
1112: l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1113: l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1114: l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1115: l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1116: l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1117: l_job_header_tbl(l_job_header_index).routing_reference_id := g_routing_id(l_job_index);
1118: l_job_header_tbl(l_job_header_index).bom_reference_id := g_bill_id(l_job_index);
1119: l_job_header_tbl(l_job_header_index).alternate_routing_designator := g_alt_routing(l_job_index);
1120: l_job_header_tbl(l_job_header_index).alternate_bom_designator := g_alt_bill(l_job_index);
1140:
1141: IF l_job_header_tbl.COUNT = 0
1142: THEN
1143: -- clean up the processing_flag value. No more processing to be done as part of this program.
1144: UPDATE csd_recall_lines SET processing_flag = 'N'
1145: WHERE processing_group_id = p_group_id;
1146:
1147: -- write the output to concurrent program's out file.
1148: -- if WIP mass load is launched, we will defer it till mass load is completed.
1177: errbuf := x_msg_data;
1178: retcode := 2;
1179: ROLLBACK TO PROCESS_RECALL_WORK;
1180: -- clean up the processing flag.
1181: UPDATE csd_recall_lines SET processing_flag = 'N'
1182: WHERE processing_group_id = p_group_id;
1183: COMMIT;
1184: WHEN OTHERS THEN
1185: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1188: ROLLBACK;
1189: errbuf := x_msg_data;
1190: retcode := 2;
1191: -- clean up the processing flag.
1192: UPDATE csd_recall_lines SET processing_flag = 'N'
1193: WHERE processing_group_id = p_group_id;
1194: COMMIT;
1195: -- write the error msg to fnd_log.out stream.
1196: csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
1266: SELECT crh.recall_number,SUM(cii.quantity)
1267: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1268: FROM csd_recall_metrics crm,
1269: csd_recall_headers_b crh,
1270: csd_recall_lines crl,
1271: csi_item_instances cii
1272: WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1273: AND crm.recall_number = crh.recall_number
1274: AND crh.recall_id = crl.recall_id
1286: SELECT crh.recall_number, COUNT(DISTINCT crl.owner_party_id)
1287: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1288: FROM csd_recall_metrics crm,
1289: csd_recall_headers_b crh,
1290: csd_recall_lines crl
1291: WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1292: AND crm.recall_number = crh.recall_number
1293: AND crh.recall_id = crl.recall_id
1294: GROUP BY crh.recall_number;
1305: SUM(wpb.tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in)
1306: BULK COLLECT INTO l_recall_numbers, l_accumulated_cost
1307: FROM csd_recall_metrics crm,
1308: csd_recall_headers_b crh,
1309: csd_recall_lines crl,
1310: csd_repair_job_xref crj,
1311: wip_period_balances wpb
1312: WHERE crm.metric_id IN
1313: (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1332: (SELECT crh.recall_number,
1333: SUM(crj.quantity) quantity
1334: FROM csd_recall_metrics crm,
1335: csd_recall_headers_b crh,
1336: csd_recall_lines crl,
1337: csd_repair_job_xref crj
1338: WHERE crm.metric_id IN
1339: (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)) )
1340: AND crm.recall_number = crh.recall_number
1357: SELECT crh.recall_number,
1358: SUM(cii.quantity) quantity
1359: FROM csd_recall_metrics crm,
1360: csd_recall_headers_b crh,
1361: csd_recall_lines crl,
1362: csi_item_instances cii,
1363: cs_incidents_all_b sr,
1364: cs_incident_statuses_b cis
1365: WHERE crm.metric_id IN
1388: l_recall_numbers_temp := l_recall_numbers;
1389: SELECT crh.recall_number, SUM(crj.quantity)
1390: BULK COLLECT INTO l_recall_numbers,l_wip_jobs_with_costs
1391: FROM csd_recall_headers_b crh,
1392: csd_recall_lines crl,
1393: csd_repair_job_xref crj
1394: WHERE crh.recall_number IN
1395: ( SELECT * FROM TABLE(CAST(l_recall_numbers_temp AS JTF_VARCHAR2_TABLE_100)))
1396: AND crh.recall_id = crl.recall_id
1415: SUM(cii.quantity)
1416: BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1417: FROM csd_recall_metrics crm,
1418: csd_recall_headers_b crh,
1419: csd_recall_lines crl,
1420: cs_incidents_all_b sr,
1421: cs_incident_statuses_b cis,
1422: csi_item_instances cii
1423: WHERE crm.metric_id IN
1459: SELECT crh.recall_number,
1460: COUNT(crl.owner_party_id) party_id
1461: FROM csd_recall_metrics crm,
1462: csd_recall_headers_b crh,
1463: csd_recall_lines crl,
1464: cs_incidents_all_b sr,
1465: cs_incident_statuses_b cis
1466: WHERE crm.metric_id IN
1467: (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1472: AND cis.status_code = 'CLOSED'
1473: AND NOT EXISTS
1474: (SELECT 'exists'
1475: FROM csd_recall_headers_b crh1,
1476: csd_recall_lines crl1,
1477: cs_incidents_all_b sr1,
1478: cs_incident_statuses_b cis1,
1479: csd_recall_metrics crm1
1480: WHERE crm1.metric_id IN