[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_MO
Source
1 package body INV_DIAG_MO as
2 /* $Header: INVDM01B.pls 120.0.12000000.1 2007/06/22 00:53:18 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 num_rows NUMBER;
27 l_txn_id NUMBER;
28 l_org_id NUMBER;
29 l_acct_period_id NUMBER;
30 l_proc_flag varchar2(1);
31 BEGIN
32 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
33 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
34 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
35 -- accept input
36 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
37 l_acct_period_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('AcctId',inputs);
38 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ProcFlag',inputs);
39
40 -- l_txn_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('testout',inputs);
41
42 sqltxt :='SELECT DISTINCT period_name "Period|Name" '||
43 ' , oap.acct_period_id "Period|Id" '||
44 ' , mp.organization_code "Organization|Code" '||
45 ' , mmtt.organization_id "Organization|Id" '||
46 ' , TO_CHAR( period_start_date, ''DD-MON-YYYY'' ) "Start Date" '||
47 ' , TO_CHAR( period_close_date, ''DD-MON-YYYY'' ) "Close Date" '||
48 ' , TO_CHAR( schedule_close_date, ''DD-MON-YYYY'' ) "Scheduled |Close Date" '||
49 ' , open_flag "Open" '||
50 ' , description "Description" '||
51 ' , period_set_name "GL Period Set|Name" '||
52 ' , period_name "GL Period|Name" '||
53 ' , period_year "GL Period|Year" '||
54 ' FROM mtl_material_transactions_temp mmtt, mtl_parameters mp '||
55 ' , org_acct_periods oap '||
56 'WHERE NVL( mmtt.transaction_status,1 ) != 2 '||
57 'AND mmtt.organization_id=mp.organization_id(+) '||
58 'AND mmtt.acct_period_id=oap.acct_period_id(+)';
59 if l_org_id is not null then
60 sqltxt :=sqltxt||'AND mmtt.organization_id = '||l_org_id;
61 end if;
62
63 if l_acct_period_id is not null then
64 sqltxt :=sqltxt||'AND mmtt.acct_period_id = '||l_acct_period_id;
65 end if;
66
67 sqltxt :=sqltxt||' ORDER BY mp.organization_code, oap.acct_period_id';
68
69 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Period Information for Pending Txn in MMTT');
70
71 statusStr := 'SUCCESS';
72 isFatal := 'FALSE';
73
74 sqltxt :='SELECT organization_code || '' ('' ||mmtt.organization_id|| '')'' "Organization|Code (Id)" '||
75 ',period_name "Period|Name", mmtt.acct_period_id "Period|Id" '||
76 ',transaction_header_id "Txn|Header Id" '||
77 ',transaction_temp_id "Txn|Temp Id" '||
78 ',TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Txn|Date" '||
79 ',DECODE(transaction_mode,1,''Online'',2,''Concurrent'',3,''Background'',transaction_mode) ||'' ('' ||transaction_mode|| '')'' "Transaction|Mode" '||
80 ',DECODE(transaction_status,1,''Pending'',2,''Allocated'',3,''Pending'',NULL,''Pending'',transaction_status) ||'' ('' ||transaction_status|| '')'' "Transaction|Status" '||
81 ',process_flag "Process|Flag" '||
82 ',lock_flag "Lock|Flag" '||
83 ',error_code '||
84 ',error_explanation '||
85 ',TO_CHAR( mmtt.last_update_date, ''DD-MON-RR HH24:MI'') "Last Updated" '||
86 ',mif.item_number ||'' (''||mmtt.inventory_item_id||'')'' "Item (Id)" '||
87 ',item_description "Item Description" '||
88 ' ,revision "Rev" ,lot_number "Lot" ,serial_number "Serial|Number" '||
89 ',mmtt.cost_group_id "Cost|Group Id" ,mmtt.subinventory_code "Subinv" '||
90 ',mil.description ||'' (''||mmtt.locator_id||'') '' "Stock|Locator (Id)" '||
91 ',transfer_subinventory "Transfer|Subinv" ,transfer_to_location "Transfer|Location" '||
92 ',transaction_quantity "Txn Qty" ,primary_quantity "Primary|Qty" ,transaction_uom "Txn|UoM" '||
93 ',mtt.transaction_type_name ||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)",ml.meaning||'' (''||mmtt.transaction_action_id||'')'' "Txn Action Type (Id)" '||
94 'FROM mtl_material_transactions_temp mmtt ,mtl_transaction_types mtt '||
95 ',mtl_item_flexfields mif ,mfg_lookups ml ,mtl_item_locations_kfv mil ,mtl_parameters mp,org_acct_periods oap '||
96 ' WHERE NVL(transaction_status,1)!=2 '||
97 'AND mmtt.transaction_type_id=mtt.transaction_type_id '||
98 'AND mmtt.organization_id=mif.organization_id(+) '||
99 'AND mmtt.inventory_item_id=mif.inventory_item_id(+)'||
100 'AND mmtt.transaction_action_id=ml.lookup_code '||
101 'AND ml.lookup_type=''MTL_TRANSACTION_ACTION'' '||
102 'AND mmtt.locator_id=mil.inventory_location_id(+) '||
103 'AND mmtt.organization_id=mil.organization_id(+)'||
104 'AND mmtt.organization_id=mp.organization_id(+) '||
105 'AND mmtt.acct_period_id=oap.acct_period_id(+) '||
106 'AND mmtt.acct_period_id=oap.acct_period_id(+) ';
107
108 if l_org_id is not null then
109 sqltxt :=sqltxt||'AND mmtt.organization_id = '||l_org_id;
110 end if;
111
112 if l_acct_period_id is not null then
113 sqltxt :=sqltxt||'AND mmtt.acct_period_id = '||l_acct_period_id;
114 end if;
115
116 if l_proc_flag is not null then
117 sqltxt :=sqltxt||'AND process_flag= ' ||l_proc_flag;
118 end if;
119
120 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Error Txn in MMTT');
121
122 statusStr := 'SUCCESS';
123 isFatal := 'FALSE';
124
125 /**
126 else
127 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input parameters');
128 statusStr := 'FAILURE';
129 errStr := 'org_id null';
130 fixInfo := 'Org or OrdID input is required ';
131 isFatal := 'SUCCESS';
132 end if;
133 **/
134 -- construct report
135 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
136 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
137 END runTest;
138
139 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
140 BEGIN
141 name := 'Move Order Orphan Allocations';
142 END getComponentName;
143
144 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
145 BEGIN
146 descStr := 'Move Order Orphan Allocations';
147 END getTestDesc;
148
149 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
150 BEGIN
151 name := 'Move Order Orphan Allocations';
152 END getTestName;
153
154 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
155 tempDependencies JTF_DIAG_DEPENDTBL;
156
157 BEGIN
158 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
159 END getDependencies;
160
161 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
162 BEGIN
163 str := 'FALSE';
164 END isDependencyPipelined;
165
166
167 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
168 tempOutput JTF_DIAG_OUTPUTTBL;
169 BEGIN
170 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
171 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.addOutput(tempOutput,'testout', test_out);
172 outputValues := tempOutput;
173 EXCEPTION
174 when others then
175 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
176 END getOutputValues;
177
178
179 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
180 tempInput JTF_DIAG_INPUTTBL;
181 BEGIN
182 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
183 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
184 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
185 defaultInputValues := tempInput;
186 EXCEPTION
187 when others then
188 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
189 END getDefaultTestParams;
190
191 Function getTestMode return INTEGER IS
192 BEGIN
193 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
194
195 END getTestMode;
196
197 END INV_DIAG_MO;