1 PACKAGE CSD_RECALLS_PVT AUTHID CURRENT_USER AS
2 /* $Header: csdvrcls.pls 120.1 2010/07/14 12:08:31 subhat noship $ */
3 -- Start of Comments
4 -- Package name : CSD_RECALLS_PVT
5 -- Purpose : This package will contain all the procedure and functions used by the Recalls.
6 -- Usage of this package is strictly confined to Oracle Depot Repair Development.
7 --
8 -- History : 24/03/2010, Created by Sudheer Bhat
9 -- NOTE :
10 -- End of Comments
11
12 g_pkg_name constant varchar2(30) := 'CSD_RECALLS_PVT';
13
14 -- Record to hold all the recall line attributes (saved attributes).
15
16 TYPE csd_recall_lines_rec IS RECORD (
17 RECALL_LINE_ID NUMBER,
18 INSTANCE_ID NUMBER,
19 OWNER_ACCOUNT_ID NUMBER,
20 OWNER_PARTY_ID NUMBER,
21 INVENTORY_ITEM_ID NUMBER,
22 REVISION VARCHAR2(3),
23 SERIAL_NUMBER VARCHAR2(30),
24 LOT_NUMBER VARCHAR2(15),
25 INCIDENT_ID NUMBER,
26 REPAIR_LINE_ID NUMBER,
27 WIP_ENTITY_ID NUMBER,
28 UOM_CODE VARCHAR2(5),
29 QUANTITY NUMBER
30 );
31
32 -- Table type of the above record.
33 TYPE csd_recall_lines_tbl IS TABLE OF csd_recall_lines_rec INDEX BY BINARY_INTEGER;
34
35 -- default primary ship to and bill to addresses.
36 TYPE csd_shipto_billto_rec IS RECORD (
37 BILL_TO_SITE_USE_ID NUMBER,
38 SHIP_TO_SITE_USE_ID NUMBER,
39 CALLER_TYPE VARCHAR2(30)
40 );
41
42 TYPE csd_shipto_billto_tbl IS TABLE OF csd_shipto_billto_rec INDEX BY BINARY_INTEGER;
43
44 TYPE job_header_tbl IS TABLE OF wip_job_schedule_interface%ROWTYPE INDEX BY BINARY_INTEGER;
45
46 TYPE post_wipml_rec IS RECORD (
47 REPAIR_LINE_ID NUMBER,
48 WIP_ENTITY_ID NUMBER,
49 ORGANIZATION_ID NUMBER,
50 QUANTITY NUMBER,
51 INVENTORY_ITEM_ID NUMBER,
52 RECALL_INVENTORY_ID NUMBER,
53 SERIAL_NUMBER VARCHAR2(30),
54 UOM_CODE VARCHAR2(3),
55 JOB_NAME VARCHAR2(240),
56 SERVICE_CODE VARCHAR2(80),
57 SERVICE_CODE_ID NUMBER,
58 TRANSACTION_QTY NUMBER
59 );
60 TYPE post_wipml_tbl IS TABLE OF post_wipml_rec INDEX BY BINARY_INTEGER;
61
62 /****************************************************************************************/
63 /* Procedure Name: Generate_Recall_Work. */
64 /* Description: Receives a set of recall lines for which the recall work needs to */
65 /* be generated along with SR, RO and WIP params if any. Prepares these */
66 /* recall lines for concurrent processing and launches the CP to create */
67 /* recall work.Returns the concurrent program Id to the caller if success */
68 /* else an appropriate error message is returned. */
69 /*-- History: 24/03/2010, Created by Sudheer Bhat. */
70 /****************************************************************************************/
71
72 PROCEDURE GENERATE_RECALL_WORK (p_api_version IN NUMBER,
73 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
74 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
75 p_recall_line_ids IN JTF_NUMBER_TABLE,
76 p_sr_type_id IN NUMBER,
77 p_ro_type_id IN NUMBER DEFAULT NULL,
78 p_service_code_id IN NUMBER DEFAULT NULL,
79 p_wip_accounting_class IN VARCHAR2 DEFAULT NULL,
80 p_upgrade_item_id IN VARCHAR2 DEFAULT NULL,
81 p_wip_inv_org_id IN NUMBER DEFAULT NULL,
82 p_recall_number IN VARCHAR2,
83 x_request_id OUT NOCOPY NUMBER,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 x_return_status OUT NOCOPY VARCHAR2);
87
88 /****************************************************************************************/
89 /* Procedure Name: process_recall_work. */
90 /* Description: This is the concurrent wrapper to process a set of recall lines. */
91 /* Generates SR, RO and WIP jobs based on the params being passed. Logs */
92 /* all the error messages to error log, and will generate a report of all */
93 /* all the successful recall lines. Updates the csd_recall_lines table */
94 /* with the SR id, RO line id and wip entity id when done with the */
95 /* processing. Once done, will reset the processing_flag to N */
96 /* -- History: 24/03/2010, Created by Sudheer Bhat. */
97 /****************************************************************************************/
98
99 PROCEDURE PROCESS_RECALL_WORK (errbuf OUT NOCOPY VARCHAR2,
100 retcode OUT NOCOPY VARCHAR2,
101 p_group_id IN NUMBER,
102 p_sr_type_id IN NUMBER,
103 p_ro_type_id IN NUMBER DEFAULT NULL,
104 p_service_code_id IN NUMBER DEFAULT NULL,
105 p_wip_accounting_class IN VARCHAR2 DEFAULT NULL,
106 p_upgrade_item_id IN NUMBER,
107 p_wip_inv_org_id IN NUMBER );
108
109 /****************************************************************************************/
110 /* Procedure Name: refresh_recall_metrics */
111 /* Description: Refreshes the recall metrics for the recall number if passed, else */
112 /* refreshes the metrics for all the open recalls. This program runs as */
113 /* concurrent program. */
114 /* -- History: 30/03/2010, Created by Sudheer Bhat. */
115 /****************************************************************************************/
116 PROCEDURE REFRESH_RECALL_METRICS(errbuf OUT NOCOPY VARCHAR2,
117 retcode OUT NOCOPY VARCHAR2,
118 p_recall_number IN VARCHAR2 DEFAULT NULL );
119
120 END CSD_RECALLS_PVT;