[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_NOLOT_ITMLOT
Source
1 package body INV_DIAG_OH_NOLOT_ITMLOT as
2 /* $Header: INVDO02B.pls 120.0.12000000.1 2007/06/22 00:58:56 musinha 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|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)"' ||
37 ' ,mif.item_number|| '' (''||moqd.inventory_item_id||'')'' "Item (Id)" ' ||
38 ' ,moqd.locator_id "Loc id",moqd.lot_number "Lot number",moqd.revision "Rev", moqd.subinventory_code "Subinv" ' ||
39 ' from mtl_onhand_quantities_detail moqd,' ||
40 ' mtl_parameters mp,mtl_item_flexfields mif' ||
41 ' where moqd.organization_id = mp.organization_id(+)' ||
42 ' and moqd.inventory_item_id = mif.inventory_item_id(+)' ||
43 ' and moqd.organization_id = mif.organization_id(+)';
44
45 IF l_org_id IS NOT NULL THEN
46 sqltxt := sqltxt || ' and moqd.organization_id = ' || l_org_id ;
47 END IF;
48 sqltxt := sqltxt ||
49 ' and moqd.lot_number is null' ||
50 ' and exists (select 1 from mtl_system_items_b msib' ||
51 ' where msib.inventory_item_id= moqd.inventory_item_id' ||
52 ' and msib.organization_id = moqd.organization_id' ||
53 ' and msib.lot_control_code=2)' ||
54 ' group by ' ||
55 ' mp.organization_code || '' ('' || moqd.organization_id || '')'',' ||
56 ' mif.item_number|| '' (''||moqd.inventory_item_id||'')'',' ||
57 ' moqd.inventory_item_id, moqd.subinventory_code, moqd.locator_id, moqd.lot_number, moqd.revision' ;
58
59
60
61 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand without Lot number for Lot controlled Item');
62
63 reportStr := 'The test completed as expected';
64 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
65 statusStr := 'SUCCESS';
66 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
67 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
68
69 EXCEPTION
70 when others then
71 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
72 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
73 statusStr := 'FAILURE';
74 errStr := sqlerrm ||' occurred in script Exception handled';
75 fixInfo := 'Unexpected Exception in INVDO02B.pls';
76 isFatal := 'FALSE';
77 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
78 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
79 END runTest;
80
81 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
82 BEGIN
83 name := 'Onhand';
84 END getComponentName;
85
86 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
87 BEGIN
88 descStr := 'Items is lot controlled and onhand without Lot';
89 END getTestDesc;
90
91 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
92 BEGIN
93 name := 'Onhand NoLot Item Lot';
94 END getTestName;
95
96
97 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
98 tempDependencies JTF_DIAG_DEPENDTBL;
99
100 BEGIN
101 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
102 END getDependencies;
103
104 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
105 BEGIN
106 str := 'FALSE';
107 END isDependencyPipelined;
108
109
110 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
111 tempOutput JTF_DIAG_OUTPUTTBL;
112 BEGIN
113 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
114 outputValues := tempOutput;
115 EXCEPTION
116 when others then
117 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
118 END getOutputValues;
119
120 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
121 tempInput JTF_DIAG_INPUTTBL;
122 BEGIN
123 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
124 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
125 defaultInputValues := tempInput;
126 EXCEPTION
127 when others then
128 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
129 END getDefaultTestParams;
130
131 Function getTestMode return INTEGER IS
132 BEGIN
133 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
134 END getTestMode;
135 END INV_DIAG_OH_NOLOT_ITMLOT;