[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_PLAIN_NO_STATUS
Source
1 package body INV_DIAG_OH_PLAIN_NO_STATUS as
2 /* $Header: INVDOH1B.pls 120.1 2011/03/23 13:24:29 rkatoori noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 null;
7 END init;
8
9 PROCEDURE cleanup IS
10 BEGIN
11 -- test writer could insert special cleanup code here
12 NULL;
13 END cleanup;
14
15 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
16 report OUT NOCOPY JTF_DIAG_REPORT,
17 reportClob OUT NOCOPY CLOB) IS
18 reportStr LONG; -- REPORT
19 sqltxt VARCHAR2(9999); -- SQL select statement
20 c_username VARCHAR2(50); -- accept input for username
21 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
22 errStr VARCHAR2(4000); -- error message
23 fixInfo VARCHAR2(4000); -- fix tip
24 isFatal VARCHAR2(50); -- TRUE or FALSE
25 dummy_num NUMBER;
26 row_limit NUMBER;
27 l_org_id NUMBER;
28
29 BEGIN
30 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
31 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
32 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
33
34 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
35
36 sqltxt :=' SELECT mp.organization_code' ||
37 ' || '' (''' ||
38 ' ||moqd.organization_id' ||
39 ' ||'')'' "Organization|Code (Id)" ,' ||
40 ' mif.item_number' ||
41 ' || '' (''' ||
42 ' ||moqd.inventory_item_id' ||
43 ' ||'')'' "Item (Id)" ,' ||
44 ' moqd.subinventory_code "Subinv",' ||
45 ' moqd.locator_id "Loc id" ,' ||
46 ' moqd.lot_number "Lot Number"' ||
47 ' FROM mtl_onhand_quantities_detail moqd,' ||
48 ' mtl_parameters mp ,' ||
49 ' mtl_item_flexfields mif' ||
50 ' WHERE moqd.organization_id = mp.organization_id' ||
51 ' AND moqd.organization_id = mif.organization_id' ||
52 ' AND moqd.inventory_item_id = mif.inventory_item_id';
53
54 IF l_org_id IS NOT NULL THEN
55 sqltxt := sqltxt || ' AND moqd.organization_id = ' || l_org_id ;
56 END IF;
57 sqltxt := sqltxt ||
58 ' AND mp.default_status_id IS NOT NULL' ||
59 ' AND mif.serial_number_control_code IN (1,6)' ||
60 ' AND moqd.status_id IS NULL' ||
61 ' GROUP BY mp.organization_code ,' ||
62 ' mif.item_number ,' ||
63 ' moqd.organization_id ,' ||
64 ' moqd.inventory_item_id,' ||
65 ' moqd.subinventory_code,' ||
66 ' moqd.locator_id ,' ||
67 ' moqd.lot_number ';
68
69 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand with status not populated for Onhand material status tracked organization for non serial controlled Item');
70
71 reportStr := 'The test completed as expected';
72 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
73 statusStr := 'SUCCESS';
74 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
75 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
76
77 EXCEPTION
78 when others then
79 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
80 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
81 statusStr := 'FAILURE';
82 errStr := sqlerrm ||' occurred in script Exception handled';
83 fixInfo := 'Unexpected Exception in INVDOH1B.pls';
84 isFatal := 'FALSE';
85 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
86 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
87 END runTest;
88
89 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
90 BEGIN
91 name := 'MATSTATUS';
92 END getComponentName;
93
94 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
95 BEGIN
96 descStr := 'Onhand with status not populated for Onhand material status tracked organization for non serial controlled Item';
97 END getTestDesc;
98
99 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
100 BEGIN
101 name := 'Onhand Plain Item Status Not populated';
102 END getTestName;
103
104
105 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
106 tempDependencies JTF_DIAG_DEPENDTBL;
107
108 BEGIN
109 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
110 END getDependencies;
111
112 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
113 BEGIN
114 str := 'FALSE';
115 END isDependencyPipelined;
116
117 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
118 tempOutput JTF_DIAG_OUTPUTTBL;
119 BEGIN
120 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
121 outputValues := tempOutput;
122 EXCEPTION
123 when others then
124 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
125 END getOutputValues;
126
127
128 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
129 tempInput JTF_DIAG_INPUTTBL;
130 BEGIN
131 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
132 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
133 defaultInputValues := tempInput;
134
135 EXCEPTION
136 when others then
137 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
138 END getDefaultTestParams;
139
140 Function getTestMode return INTEGER IS
141 BEGIN
142 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
143 END getTestMode;
144 END INV_DIAG_OH_PLAIN_NO_STATUS;