[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_MO_CAN_SO
Source
1 package body INV_DIAG_MO_CAN_SO as
2 /* $Header: INVDM02B.pls 120.0.12000000.1 2007/06/22 00:54:42 musinha noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 -- test writer
7 null;
8 END init;
9
10 PROCEDURE cleanup IS
11 BEGIN
12 -- test writer could insert special cleanup code here
13 NULL;
14 END cleanup;
15
16 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
17 report OUT NOCOPY JTF_DIAG_REPORT,
18 reportClob OUT NOCOPY CLOB) IS
19
20 reportStr LONG;
21 counter NUMBER;
22 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
23 c_userid VARCHAR2(50);
24 statusStr VARCHAR2(50);
25 errStr VARCHAR2(4000);
26 fixInfo VARCHAR2(4000);
27 isFatal VARCHAR2(50);
28 dummy_num NUMBER;
29 sqltxt VARCHAR2 (9999);
30 l_line_id NUMBER;
31 l_org_id NUMBER;
32
33 BEGIN
34
35 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
36 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
37 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
38 --JTF_DIAGNOSTIC_COREAPI.line_out('this also writes to the clob');
39
40 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
41 l_line_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('LineId',inputs);
42
43 IF l_org_id IS NULL THEN
44 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,'Please execute the report with organization information');
45 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Organization Identifier');
46 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter correct organization Identifier');
47 statusStr := 'FAILURE';
48 errStr := 'Invalid Organization Identifier';
49 fixInfo := 'Please enter or choose Organization identifier';
50 isFatal := 'FALSE';
51 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
52 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
53 RETURN;
54 END IF;
55
56 FND_CLIENT_INFO.SET_ORG_CONTEXT('' || l_org_id || '');
57
58 sqltxt := ' select ' ||
59 ' mp.organization_code|| '' (''||mtr.organization_id ||'')'' "Organization|Code (Id)"' ||
60 ' ,mif.item_number|| '' (''||mtr.inventory_item_id||'')'' "Item (Id)" ,' ||
61 ' mtr.request_number "MO Number",' ||
62 ' move_order_type "MO Type",' ||
63 ' mtr.quantity_detailed "Qty Detailed",' ||
64 ' mmtt_sum "Sum MMTT qty" ' ||
65 ' from (SELECT move_order_line_id, sum(abs(transaction_quantity)) mmtt_sum' ||
66 ' FROM mtl_material_transactions_temp ' ||
67 ' group BY move_order_line_id) mmtt , ' ||
68 ' ( select mtrl.organization_id, mtrl.inventory_item_id ,request_number,move_order_type, mtrh.header_id, line_id ,quantity_detailed from' ||
69 ' mtl_txn_Request_headers mtrh, mtl_Txn_Request_lines mtrl' ||
70 ' WHERE mtrh.header_id = mtrl.header_id' ||
71 ' AND mtrl.organization_id = ' || l_org_id ;
72 IF l_line_id IS NOT NULL THEN
73 sqltxt := sqltxt || ' and mtrl.line_id = '|| l_line_id || ') mtr,' ;
74 ELSE
75 sqltxt := sqltxt || ' ) mtr,' ;
76 END IF;
77 sqltxt := sqltxt ||
78 ' mtl_parameters mp,mtl_item_flexfields mif' ||
79 ' where mmtt.move_order_line_id = mtr.line_id' ||
80 ' and mtr.organization_id = mp.organization_id(+)' ||
81 ' and mtr.inventory_item_id = mif.inventory_item_id(+)' ||
82 ' and mtr.organization_id = mif.organization_id(+) ';
83
84 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Details of Move Order');
85
86
87 reportStr := 'The test completed as expected';
88 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
89 statusStr := 'SUCCESS';
90 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
91 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
92
93 EXCEPTION
94 when others then
95 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
96 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
97 statusStr := 'FAILURE';
98 errStr := sqlerrm ||' occurred in script Exception handled';
99 fixInfo := 'Unexpected Exception in INVDM02B.pls';
100 isFatal := 'FALSE';
101 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
102 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
103 END runTest;
104
105
106 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
107 BEGIN
108 name := 'Move Orders';
109 END getComponentName;
110
111 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
112 BEGIN
113 descStr := 'Allocation exists for cancelled Sales Orders';
114 END getTestDesc;
115
116 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
117 BEGIN
118 name := 'Allocations for canceled Sales Order';
119 END getTestName;
120
121 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
122 tempDependencies JTF_DIAG_DEPENDTBL;
123
124 BEGIN
125 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
126 END getDependencies;
127
128 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
129 BEGIN
130 str := 'FALSE';
131 END isDependencyPipelined;
132
133 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
134 tempOutput JTF_DIAG_OUTPUTTBL;
135 BEGIN
136 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
137 outputValues := tempOutput;
138 EXCEPTION
139 when others then
140 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
141 END getOutputValues;
142
143 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
144 tempInput JTF_DIAG_INPUTTBL;
145 BEGIN
146 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
147 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
148 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'MoNumber','');
149 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'LineId','LOV-oracle.apps.inv.diag.lov.MOLineLov');
150 -- tempInput := JTF_DIAGNOSTIC_
151 defaultInputValues := tempInput;
152 EXCEPTION
153 when others then
154 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
155 END getDefaultTestParams;
156
157 Function getTestMode return INTEGER IS
158 BEGIN
159 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
160 END getTestMode;
161
162 END INV_DIAG_MO_CAN_SO;