[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_QTY_ZERO
Source
1 package body INV_DIAG_OH_QTY_ZERO as
2 /* $Header: INVDO08B.pls 120.0.12000000.1 2007/06/22 01:07:27 musinha noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 -- test writer
7 null;
8 END init;
9
10 PROCEDURE cleanup IS
11 BEGIN
12 -- test writer could insert special cleanup code here
13 NULL;
14 END cleanup;
15
16 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
17 report OUT NOCOPY JTF_DIAG_REPORT,
18 reportClob OUT NOCOPY CLOB) IS
19
20 reportStr LONG;
21 counter NUMBER;
22 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
23 c_userid VARCHAR2(50);
24 statusStr VARCHAR2(50);
25 errStr VARCHAR2(4000);
26 fixInfo VARCHAR2(4000);
27 isFatal VARCHAR2(50);
28 dummy_num NUMBER;
29 sqltxt VARCHAR2 (9999);
30 l_org_id NUMBER;
31 l_item_id NUMBER;
32
33 CURSOR c_tnx (cp_org_id IN NUMBER,
34 cp_item_id IN NUMBER) IS
35 SELECT moqd.create_transaction_id
36 FROM mtl_onhand_quantities_detail moqd
37 WHERE moqd.subinventory_code is not null
38 AND ( moqd.primary_transaction_quantity = 0
39 OR moqd.transaction_quantity = 0 )
40 AND moqd.organization_id = NVL(cp_org_id,moqd.organization_id)
41 AND moqd.inventory_item_id = NVL(cp_item_id, moqd.inventory_item_id)
42 ORDER BY moqd.create_transaction_id;
43
44 BEGIN
45
46 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
47 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
48 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
49 --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
50
51 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
52 l_item_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
53
54
55 sqltxt := ' select mif.item_number||''(''||mif.inventory_item_id||'')'' "Item (Id)"' ||
56 ' , mp.organization_code|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)"' ||
57 ' , moqd.subinventory_code "Subinv"' ||
58 ' , moqd.locator_id "Stock Locator"' ||
59 ' , moqd.revision "Rev"' ||
60 ' , moqd.primary_transaction_quantity "Prim Qty"' ||
61 ' , moqd.create_transaction_id "Create txn_id"' ||
62 ' from mtl_onhand_quantities_detail moqd, mtl_parameters mp,' ||
63 ' mtl_item_flexfields mif' ||
64 ' where subinventory_code is not null ' ||
65 ' and moqd.organization_id = mp.organization_id' ||
66 ' and (primary_transaction_quantity = 0 ' ||
67 ' or transaction_quantity = 0 ) ' ||
68 ' and moqd.inventory_item_id = mif.inventory_item_id(+)' ||
69 ' and moqd.organization_id =mif.organization_id(+) ' ;
70 IF l_org_id IS NOT NULL THEN
71 sqltxt := sqltxt || ' and moqd.organization_id = ' || l_org_id ;
72 END IF;
73
74 IF l_item_id IS NOT NULL THEN
75 sqltxt := sqltxt || ' and moqd.inventory_item_id = ' || l_item_id ;
76 END IF;
77
78 sqltxt := sqltxt || ' ORDER BY moqd.create_transaction_id ' ;
79
80 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of onhand quantity with transaction quantity as zero');
81
82 FOR rec_tnx IN c_tnx (l_org_id,l_item_id) LOOP
83 sqltxt := ' SELECT mmt.transaction_id "Txn Id"' ||
84 ' , mp.organization_code|| '' (''||mmt.organization_id ||'')'' "Organization|Code (Id)"' ||
85 ' , mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)"' ||
86 ' , mmt.transaction_date "Txn Date"' ||
87 ' , mmt.transaction_quantity "Txn Qty"' ||
88 ' , mmt.primary_quantity "Prim Qty"' ||
89 ' , mmt.transaction_uom "Uom"' ||
90 ' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)"' ||
91 ' , ml.meaning || '' ('' ||mmt.transaction_action_id|| '')''' ||
92 ' "Txn Action (Id)"' ||
93 ' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)"' ||
94 ' , mmt.subinventory_code "Subinv"' ||
95 ' , mmt.locator_id "Stock Locator"' ||
96 ' , mmt.revision "Rev"' ||
97 ' , mmt.physical_adjustment_id "Physical Adj Id"' ||
98 ' , mmt.transaction_source_id "Txn Source Id"' ||
99 ' , mmt.transaction_source_name "Txn Source"' ||
100 ' FROM mtl_material_transactions mmt , mtl_parameters mp ' ||
101 ' , mtl_item_flexfields mif' ||
102 ' , mtl_transaction_types tt' ||
103 ' , mtl_txn_source_types st' ||
104 ' , mfg_lookups ml' ||
105 ' WHERE mmt.transaction_id = ' || rec_tnx.create_transaction_id ||
106 ' and mmt.organization_id = mp.organization_id' ||
107 ' AND mmt.inventory_item_id = mif.inventory_item_id(+)' ||
108 ' AND mmt.organization_id = mif.organization_id(+)' ||
109 ' AND mmt.transaction_type_id = tt.transaction_type_id(+)' ||
110 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+)' ||
111 ' AND mmt.transaction_action_id=ml.lookup_code' ||
112 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION''' ||
113 ' ORDER BY costed_flag, transaction_id ';
114
115 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Create Transaction Details for transaction_id ' || rec_tnx.create_transaction_id );
116 END LOOP;
117
118 reportStr := 'The test completed as expected';
119 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
120 statusStr := 'SUCCESS';
121 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
122 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
123
124 EXCEPTION
125 when others then
126 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
127 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
128 statusStr := 'FAILURE';
129 errStr := sqlerrm ||' occurred in script Exception handled';
130 fixInfo := 'Unexpected Exception in INVDO08B.pls';
131 isFatal := 'FALSE';
132 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
133 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
134 END runTest;
135
136
137 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
138 BEGIN
139 name := 'Onhand';
140 END getComponentName;
141
142 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
143 BEGIN
144 descStr := 'Onhand with transaction quantity as zero';
145 END getTestDesc;
146
147 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
148 BEGIN
149 name := 'Zero Onhand Quantity';
150 END getTestName;
151
152 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
153 tempDependencies JTF_DIAG_DEPENDTBL;
154
155 BEGIN
156 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
157 END getDependencies;
158
159 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
160 BEGIN
161 str := 'FALSE';
162 END isDependencyPipelined;
163
164
165 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
166 tempOutput JTF_DIAG_OUTPUTTBL;
167 BEGIN
168 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
169 outputValues := tempOutput;
170 EXCEPTION
171 when others then
172 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
173 END getOutputValues;
174
175
176 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
177 tempInput JTF_DIAG_INPUTTBL;
178 BEGIN
179 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
180 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
181 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
182 -- tempInput := JTF_DIAGNOSTIC_
183 defaultInputValues := tempInput;
184 EXCEPTION
185 when others then
186 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
187 END getDefaultTestParams;
188
189 Function getTestMode return INTEGER IS
190 BEGIN
191 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
192 END getTestMode;
193
194 END INV_DIAG_OH_QTY_ZERO;