[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_OH_LOT_ITMNOLOT
Source
1 package body INV_DIAG_OH_LOT_ITMNOLOT as
2 /* $Header: INVDO01B.pls 120.0.12000000.1 2007/06/22 00:57:36 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 not 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=1)' ||
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 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand with Lot number for Item Not Lot Controlled');
61
62 reportStr := 'The test completed as expected';
63 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
64 statusStr := 'SUCCESS';
65 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
66 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
67
68 EXCEPTION
69 when others then
70 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
71 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
72 statusStr := 'FAILURE';
73 errStr := sqlerrm ||' occurred in script Exception handled';
74 fixInfo := 'Unexpected Exception in INVDO01B.pls';
75 isFatal := 'FALSE';
76 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
77 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
78 END runTest;
79
80 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
81 BEGIN
82 name := 'Onhand';
83 END getComponentName;
84
85 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
86 BEGIN
87 descStr := 'Onhand with lot number when item is not lot controlled';
88 END getTestDesc;
89
90 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
91 BEGIN
92 name := 'Onhand Lot Item NoLot';
93 END getTestName;
94
95
96 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
97 tempDependencies JTF_DIAG_DEPENDTBL;
98
99 BEGIN
100 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
101 END getDependencies;
102
103 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
104 BEGIN
105 str := 'FALSE';
106 END isDependencyPipelined;
107
108 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
109 tempOutput JTF_DIAG_OUTPUTTBL;
110 BEGIN
111 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
112 outputValues := tempOutput;
113 EXCEPTION
114 when others then
115 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
116 END getOutputValues;
117
118
119 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
120 tempInput JTF_DIAG_INPUTTBL;
121 BEGIN
122 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
123 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
124 defaultInputValues := tempInput;
125
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_LOT_ITMNOLOT;