[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RSV_WDD_STG_MCH
Source
1 PACKAGE BODY INV_DIAG_RSV_WDD_STG_MCH AS
2 /* $Header: INVDP03B.pls 120.0.12000000.1 2007/06/22 01:14:56 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
49 sqltxt := ' select' ||
50 ' mp.organization_code || '' ('' ||mr.organization_id|| '')'' "Organization Code (Id)"' ||
51 ' ,mif.item_number||'' (''||mif.inventory_item_id||'')'' "Item (Id)"' ||
52 ' ,mr.reservation_id "Rsv ID"' ||
53 ' ,TO_CHAR( requirement_date, ''DD-MON-RR'' ) "Requirement Date"' ||
54 ' , reservation_quantity' ||
55 ' , primary_reservation_quantity' ||
56 ' , detailed_quantity' ||
57 ' , demand_source_type_id' ||
58 ' , demand_source_name' ||
59 ' , demand_source_header_id' ||
60 ' , demand_source_line_id' ||
61 ' , demand_source_delivery' ||
62 ' , revision' ||
63 ' , subinventory_code' ||
64 ' , locator_id' ||
65 ' , lot_number "Lot Number"' ||
66 ' , serial_number "Serial Number"' ||
67 ' , lpn_id' ||
68 ' , TO_CHAR( mr.creation_date, ''DD-MON-RR'' ) "Creation DateE"' ||
69 ' , TO_CHAR( mr.last_update_date, ''DD-MON-RR'' ) "Last UPDATE Date"' ||
70 ' from mtl_reservations mr,' ||
71 ' mtl_parameters mp,' ||
72 ' mtl_item_flexfields mif' ||
73 ' WHERE mr.organization_id = mp.organization_id (+)' ||
74 ' AND mr.inventory_item_id = mif.inventory_item_id (+)' ||
75 ' AND mr.organization_id = mif.organization_id (+)' ||
76 ' AND mr.supply_source_type_id=13' ||
77 ' and mr.demand_source_type_id in (2,8)' ||
78 ' and nvl(mr.staged_flag,''N'') =''N''' ||
79 ' and mr.demand_source_line_id in (select wdd1.source_line_id from' ||
80 ' wsh_delivery_details wdd1' ||
81 ' where wdd1.source_line_id=mr.demand_source_line_id' ||
82 ' and wdd1.source_code=''OE''' ||
83 ' and wdd1.released_status =''S''' ||
84 ' and not exists (select 1 from' ||
85 ' wsh_delivery_details wdd2' ||
86 ' where wdd2.source_line_id=wdd1.source_line_id' ||
87 ' and wdd2.source_code=''OE''' ||
88 ' and wdd2.released_status <> ''S''))' ||
89 ' and mr.reservation_id not in (select reservation_id from' ||
90 ' mtl_material_transactions_temp mmtt' ||
91 ' where mmtt.trx_source_line_id=mr.demand_source_line_id' ||
92 ' and mr.demand_source_type_id=mmtt.transaction_source_type_id' ||
93 ' and mmtt.reservation_id is not null) ';
94
95 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Unstaged Reservations');
96
97 reportStr := 'The test completed as expected';
98 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
99 statusStr := 'SUCCESS';
100 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
101 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
106 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
107 statusStr := 'FAILURE';
108 errStr := sqlerrm ||' occurred in script Exception handled';
109 fixInfo := 'Unexpected Exception in INVDP03B.pls';
110 isFatal := 'FALSE';
111 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
112 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
113 END runTest;
114
115 ------------------------------------------------------------
116 -- procedure to report name back to framework
117 ------------------------------------------------------------
118 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
119 BEGIN
120 name := 'Pick Release and Reservation';
121 END getComponentName;
122
123 ------------------------------------------------------------
124 -- procedure to report test description back to framework
125 ------------------------------------------------------------
126 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
127 BEGIN
128 descStr := 'Details of unstaged reservation with staged delivery';
129 END getTestDesc;
130
131 ------------------------------------------------------------
132 -- procedure to report test name back to framework
133 ------------------------------------------------------------
134 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
135 BEGIN
136 name := 'Unstaged Reservation and Staged Delivery';
137 END getTestName;
138
139 ------------------------------------------------------------
140 -- procedure to provide the default parameters for the test case.
141 -- please note the paramters have to be registered through the UI
142 -- before basic tests can be run.
143 --
144 ------------------------------------------------------------
145 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
146 tempInput JTF_DIAG_INPUTTBL;
147 BEGIN
148 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
149 EXCEPTION
150 when others then
151 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
152 END getDefaultTestParams;
153
154 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
155 tempDependencies JTF_DIAG_DEPENDTBL;
156
157 BEGIN
158 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
159 END getDependencies;
160
161 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
162 BEGIN
163 str := 'FALSE';
164 END isDependencyPipelined;
165
166 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
167 tempOutput JTF_DIAG_OUTPUTTBL;
168 BEGIN
169 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
170 outputValues := tempOutput;
171 EXCEPTION
172 when others then
173 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
174 END getOutputValues;
175
176 Function getTestMode return INTEGER IS
177 BEGIN
178 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
179
180 END getTestMode;
181 END INV_DIAG_RSV_WDD_STG_MCH;