[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_PI_ONHAND
Source
1 package body INV_DIAG_PI_ONHAND as
2 /* $Header: INVDA01B.pls 120.0.12000000.1 2007/06/22 00:33:51 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 l_phy_inv_id NUMBER;
27 l_org_id NUMBER;
28 l_item_id NUMBER;
29
30 BEGIN
31 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
32 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
33 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
34
35 l_phy_inv_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('PhyInvId',inputs);
36 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
37 l_item_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
38
39 -- accept input
40 sqltxt := ' SELECT org "OrganizationCode (Id)"' ||
41 ' ,item "Item (Id)",rev "Rev",' ||
42 ' sub "Subinv",' ||
43 ' loc "Locator id",' ||
44 ' onhand "Onhand Qty",' ||
45 ' qty_avail "Available Qty",' ||
46 ' Adj_qty "Adj Qty"' ||
47 ' from' ||
48 ' (select mp.organization_code|| '' (''||mpa.organization_id ||'')'' org, msi.concatenated_segments || '' (''||mpa.inventory_item_id ||'')'' item, ' ||
49 ' mpa.revision rev, mpa.subinventory_name sub, mpa.locator_id loc, mpa.lot_number lot,' ||
50 ' INV_DIAG_GRP.CHECK_ONHAND(mpa.inventory_item_id, mpa.organization_id, mpa.revision, mpa.subinventory_name, mpa.locator_id) onhand, ' ||
51 ' INV_DIAG_GRP.CHECK_AVAIL(mpa.inventory_item_id,mpa.organization_id,mpa.revision, mpa.subinventory_name,mpa.locator_id) qty_avail, ' ||
52 ' sum(adjustment_quantity) adj_qty' ||
53 ' from mtl_physical_adjustments mpa, mtl_system_items_kfv msi,mtl_parameters mp' ||
54 ' where mpa.approval_status is null and mpa.adjustment_quantity < 0 ' ||
55 ' and mpa.inventory_item_id = msi.inventory_item_id' ||
56 ' and mpa.organization_id = msi.organization_id' ||
57 ' and mp.organization_id = mpa.organization_id ' ;
58
59 IF l_phy_inv_id IS NOT NULL THEN
60 sqltxt := sqltxt || ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ;
61 END IF;
62
63 IF l_org_id IS NOT NULL THEN
64 sqltxt := sqltxt || ' AND mpa.organization_id = ' || l_org_id ;
65 END IF;
66
67 IF l_item_id IS NOT NULL THEN
68 sqltxt := sqltxt || ' AND mpa.inventory_item_id = ' || l_item_id;
69 END IF;
70
71 sqltxt := sqltxt || ' group by mp.organization_code|| '' (''||mpa.organization_id ||'')'', mpa.inventory_item_id, ' ||
72 ' msi.concatenated_segments, mpa.revision, mpa.subinventory_name, mpa.locator_id, mpa.lot_number, ' ||
73 ' INV_DIAG_GRP.CHECK_ONHAND(mpa.inventory_item_id, mpa.organization_id, mpa.revision, mpa.subinventory_name, mpa.locator_id), ' ||
74 ' INV_DIAG_GRP.CHECK_AVAIL(mpa.inventory_item_id,mpa.organization_id,mpa.revision, mpa.subinventory_name,mpa.locator_id) ' ||
75 ' order by mpa.inventory_item_id)' ||
76 ' where abs(adj_qty) > qty_avail ';
77
78 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Details of Physical Inventory Tags without onhand');
79 reportStr := 'The test completed as expected';
80 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
81 statusStr := 'SUCCESS';
82 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
83 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
84
85 EXCEPTION
86 when others then
87 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
88 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
89 statusStr := 'FAILURE';
90 errStr := sqlerrm ||' occurred in script Exception handled';
91 fixInfo := 'Unexpected Exception in INVDA01B.pls';
92 isFatal := 'FALSE';
93 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
94 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
95 END runTest;
96
97 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
98 BEGIN
99 name := 'Accuracy';
100 END getComponentName;
101
102 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
103 BEGIN
104 descStr := 'PI tags without onhand';
105 END getTestDesc;
106
107 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
108 BEGIN
109 name := 'PI tags without onhand';
110 END getTestName;
111
112 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
113 tempDependencies JTF_DIAG_DEPENDTBL;
114
115 BEGIN
116 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
117 END getDependencies;
118
119 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
120 BEGIN
121 str := 'FALSE';
122 END isDependencyPipelined;
123
124 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
125 tempOutput JTF_DIAG_OUTPUTTBL;
126 BEGIN
127 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
128 outputValues := tempOutput;
129 EXCEPTION
130 when others then
131 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
132 END getOutputValues;
133
134 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
135 tempInput JTF_DIAG_INPUTTBL;
136 BEGIN
137 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
138 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
139 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
140 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'PhyInvId','LOV-oracle.apps.inv.diag.lov.PhysInvLov');
141 defaultInputValues := tempInput;
142 EXCEPTION
143 when others then
144 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
145 END getDefaultTestParams;
146
147 Function getTestMode return INTEGER IS
148 BEGIN
149 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
150
151 END getTestMode;
152
153 END INV_DIAG_PI_ONHAND;