[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_WDD_STAGED
Source
1 PACKAGE BODY INV_DIAG_WDD_STAGED AS
2 /* $Header: INVDP04B.pls 120.0.12000000.1 2007/06/22 01:16:52 musinha noship $ */
3
4 ------------------------------------------------------------
5 -- procedure to initialize test datastructures
6 -- executed prior to test run leave body as null otherwize
7 ------------------------------------------------------------
8 PROCEDURE init IS
9 BEGIN
10 -- test writer could insert special setup code here
11 null;
12 END init;
13
14 ------------------------------------------------------------
15 -- procedure to cleanup any test datastructures that were setup in the init
16 -- procedure call executes after test run leave body as null otherwize
17 ------------------------------------------------------------
18 PROCEDURE cleanup IS
19 BEGIN
20 -- test writer could insert special cleanup code here
21 NULL;
22 END cleanup;
23
24 ------------------------------------------------------------
25 -- procedure to execute the PLSQL test
26 -- the inputs needed for the test are passed in and a report object and CLOB are
27 -- returned.
28 -- note the way that support API writes to the report CLOB.
29 ------------------------------------------------------------
30 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
31 report OUT NOCOPY JTF_DIAG_REPORT,
32 reportClob OUT NOCOPY CLOB) IS
33 reportStr LONG;
34 counter NUMBER;
35 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
36 c_userid VARCHAR2(50);
37 statusStr VARCHAR2(50);
38 errStr VARCHAR2(4000);
39 fixInfo VARCHAR2(4000);
40 isFatal VARCHAR2(50);
41 dummy_num NUMBER;
42 sqltxt VARCHAR2 (2000);
43 BEGIN
44 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
45 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
46 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
47 --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
48 sqltxt := ' select' ||
49 ' mp.organization_code || '' ('' ||mr.organization_id|| '')'' "Organization Code (Id)"' ||
50 ' ,mif.item_number||'' (''||mif.inventory_item_id||'')'' "Item (Id)"' ||
51 ' ,mr.reservation_id "Rsv ID"' ||
52 ' ,TO_CHAR( requirement_date, ''DD-MON-RR'' ) "Requirement Date"' ||
53 ' , reservation_quantity' ||
54 ' , primary_reservation_quantity' ||
55 ' , detailed_quantity' ||
56 ' , demand_source_type_id' ||
57 ' , demand_source_name' ||
58 ' , demand_source_header_id' ||
59 ' , demand_source_line_id' ||
60 ' , demand_source_delivery' ||
61 ' , revision' ||
62 ' , subinventory_code' ||
63 ' , locator_id' ||
64 ' , lot_number "Lot Number"' ||
65 ' , serial_number "Serial Number"' ||
66 ' , lpn_id' ||
67 ' , TO_CHAR( mr.creation_date, ''DD-MON-RR'' ) "Creation DateE"' ||
68 ' , TO_CHAR( mr.last_update_date, ''DD-MON-RR'' ) "Last UPDATE Date"' ||
69 ' from mtl_reservations mr,' ||
70 ' mtl_parameters mp,' ||
71 ' mtl_item_flexfields mif ' ||
72 ' where nvl(mr.staged_flag,''N'') <> ''Y''' ||
73 ' and mr.demand_source_type_id in (2,8)' ||
74 ' and mr.supply_source_type_id =13' ||
75 ' AND mr.organization_id = mp.organization_id (+)' ||
76 ' AND mr.inventory_item_id = mif.inventory_item_id (+)' ||
77 ' AND mr.organization_id = mif.organization_id (+)' ||
78 ' and mr.demand_source_line_id in (select wdd1.source_line_id from' ||
79 ' wsh_delivery_details wdd1' ||
80 ' where wdd1.source_line_id=mr.demand_source_line_id' ||
81 ' and wdd1.source_code=''OE''' ||
82 ' and wdd1.released_status in (''Y'',''C'')' ||
83 ' and not exists (select 1 from' ||
84 ' wsh_delivery_details wdd2' ||
85 ' where wdd2.source_line_id=wdd1.source_line_id' ||
86 ' and wdd2.source_code=''OE''' ||
87 ' and wdd2.released_status in (''R'',''S''))) ';
88
89 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of staged / shipped (and not inv interfaced) serials not marked');
90
91 reportStr := 'The test completed as expected';
92 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
93 statusStr := 'SUCCESS';
94 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
95 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
100 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
101 statusStr := 'FAILURE';
102 errStr := sqlerrm ||' occurred in script Exception handled';
103 fixInfo := 'Unexpected Exception in INVDP04B.pls';
104 isFatal := 'FALSE';
105 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
106 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
107 END runTest;
108
109 ------------------------------------------------------------
110 -- procedure to report name back to framework
111 ------------------------------------------------------------
112 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
113 BEGIN
114 name := 'Pick Release and Reservation';
115 END getComponentName;
116
117 ------------------------------------------------------------
118 -- procedure to report test description back to framework
119 ------------------------------------------------------------
120 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
121 BEGIN
122 descStr := 'Details of Staged delivery details';
123 END getTestDesc;
124
125 ------------------------------------------------------------
126 -- procedure to report test name back to framework
127 ------------------------------------------------------------
128 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
129 BEGIN
130 name := 'Details of Staged delivery details';
131 END getTestName;
132
133 ------------------------------------------------------------
134 -- procedure to provide the default parameters for the test case.
135 -- please note the paramters have to be registered through the UI
136 -- before basic tests can be run.
137 --
138 ------------------------------------------------------------
139 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
140 tempInput JTF_DIAG_INPUTTBL;
141 BEGIN
142 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
143 EXCEPTION
144 when others then
145 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
146 END getDefaultTestParams;
147 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
148 tempDependencies JTF_DIAG_DEPENDTBL;
149
150 BEGIN
151 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
152 END getDependencies;
153
154 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
155 BEGIN
156 str := 'FALSE';
157 END isDependencyPipelined;
158
159 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
160 tempOutput JTF_DIAG_OUTPUTTBL;
161 BEGIN
162 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
163 outputValues := tempOutput;
164 EXCEPTION
165 when others then
166 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
167 END getOutputValues;
168
169 Function getTestMode return INTEGER IS
170 BEGIN
171 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
172
173 END getTestMode;
174
175 END INV_DIAG_WDD_STAGED;