61:
62: BEGIN
63:
64: -- Sets the debug mode to be FILE
65: --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
66:
67: IF (l_Debug_Level <= 2) THEN
68: cln_debug_pub.Add('----- Entering RAISE_REPORT_GEN_EVENT API ------- ',2);
69: END IF;
64: -- Sets the debug mode to be FILE
65: --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
66:
67: IF (l_Debug_Level <= 2) THEN
68: cln_debug_pub.Add('----- Entering RAISE_REPORT_GEN_EVENT API ------- ',2);
69: END IF;
70:
71:
72: -- Initialize API return status to success
73: l_msg_data := 'Successfully called the CLN API to kick off the report generation for inventory';
74:
75: -- Parameters received
76: IF (l_Debug_Level <= 1) THEN
77: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
74:
75: -- Parameters received
76: IF (l_Debug_Level <= 1) THEN
77: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
75: -- Parameters received
76: IF (l_Debug_Level <= 1) THEN
77: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
76: IF (l_Debug_Level <= 1) THEN
77: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
77: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
78: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
79: cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
80: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
81: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
82: cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90: cln_debug_pub.Add('=================================================',1);
83: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90: cln_debug_pub.Add('=================================================',1);
91: END IF;
84: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90: cln_debug_pub.Add('=================================================',1);
91: END IF;
92:
85: cln_debug_pub.Add('Item Category - '||p_item_category,1);
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90: cln_debug_pub.Add('=================================================',1);
91: END IF;
92:
93:
86: cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87: cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88: cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89: cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90: cln_debug_pub.Add('=================================================',1);
91: END IF;
92:
93:
94:
92:
93:
94:
95: IF (l_Debug_Level <= 1) THEN
96: cln_debug_pub.Add('Setting Event Key....',1);
97: END IF;
98:
99: SELECT cln_generic_s.nextval INTO l_event_key FROM Dual;
100: IF (l_Debug_Level <= 1) THEN
97: END IF;
98:
99: SELECT cln_generic_s.nextval INTO l_event_key FROM Dual;
100: IF (l_Debug_Level <= 1) THEN
101: cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
102:
103: cln_debug_pub.Add('Getting Trading Partner Details Using Tp_Header_Id',1);
104: END IF;
105:
99: SELECT cln_generic_s.nextval INTO l_event_key FROM Dual;
100: IF (l_Debug_Level <= 1) THEN
101: cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
102:
103: cln_debug_pub.Add('Getting Trading Partner Details Using Tp_Header_Id',1);
104: END IF;
105:
106: BEGIN
107:
116: WHEN NO_DATA_FOUND THEN
117: FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
118: l_msg_data := FND_MESSAGE.GET;
119: IF (l_Debug_Level <= 1) THEN
120: cln_debug_pub.Add('Unable to find the set up details for the trading partner',1);
121: END IF;
122:
123: RAISE FND_API.G_EXC_ERROR;
124: WHEN TOO_MANY_ROWS THEN
124: WHEN TOO_MANY_ROWS THEN
125: FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_UNIQUE');
126: l_msg_data := FND_MESSAGE.GET;
127: IF (l_Debug_Level <= 1) THEN
128: cln_debug_pub.Add('More then one row found for the same trading partner set up',1);
129: END IF;
130:
131: RAISE FND_API.G_EXC_ERROR;
132: END;
130:
131: RAISE FND_API.G_EXC_ERROR;
132: END;
133: IF (l_Debug_Level <= 1) THEN
134: cln_debug_pub.Add('Trading Partner Details Found',1);
135: END IF;
136:
137:
138: IF (l_Debug_Level <= 1) THEN
135: END IF;
136:
137:
138: IF (l_Debug_Level <= 1) THEN
139: cln_debug_pub.Add('Defaulting XMLG Document ID with a running sequence',1);
140: END IF;
141:
142: SELECT cln_generic_s.nextval INTO l_xmlg_document_id FROM Dual;
143:
141:
142: SELECT cln_generic_s.nextval INTO l_xmlg_document_id FROM Dual;
143:
144: IF (l_Debug_Level <= 1) THEN
145: cln_debug_pub.Add('Defaulting Documnet No with sysdate',1);
146: END IF;
147:
148:
149: SELECT TO_CHAR(cln_sync_inv_doc_s.nextval) INTO l_doc_number FROM Dual;
148:
149: SELECT TO_CHAR(cln_sync_inv_doc_s.nextval) INTO l_doc_number FROM Dual;
150:
151: IF (l_Debug_Level <= 1) THEN
152: cln_debug_pub.Add('Checking for user input.....',1);
153: END IF;
154:
155: BEGIN
156: --SELECT inventory_item_id
188: WHEN NO_DATA_FOUND THEN
189: FND_MESSAGE.SET_NAME('CLN','CLN_CH_NO_ROW_SELECTED');
190: l_msg_data := FND_MESSAGE.GET;
191: IF (l_Debug_Level <= 1) THEN
192: cln_debug_pub.Add('No records found for the user input',1);
193: END IF;
194:
195: RAISE FND_API.G_EXC_ERROR;
196: WHEN TOO_MANY_ROWS THEN
194:
195: RAISE FND_API.G_EXC_ERROR;
196: WHEN TOO_MANY_ROWS THEN
197: IF (l_Debug_Level <= 1) THEN
198: cln_debug_pub.Add('More then one row found for the user input',1);
199: END IF;
200:
201: END;
202: IF (l_Debug_Level <= 1) THEN
199: END IF;
200:
201: END;
202: IF (l_Debug_Level <= 1) THEN
203: cln_debug_pub.Add('User input seems valid.....',1);
204: END IF;
205:
206:
207: l_cln_inv_parameters := wf_parameter_list_t();
205:
206:
207: l_cln_inv_parameters := wf_parameter_list_t();
208: IF (l_Debug_Level <= 1) THEN
209: cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
210: END IF;
211:
212: WF_EVENT.AddParameterToList('INV_ORGANIZATION',p_inv_org,l_cln_inv_parameters);
213: WF_EVENT.AddParameterToList('TP_HEADER_ID',p_inv_user,l_cln_inv_parameters);
232: WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'OUT',l_cln_inv_parameters);
233:
234:
235: IF (l_Debug_Level <= 1) THEN
236: cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
233:
234:
235: IF (l_Debug_Level <= 1) THEN
236: cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
234:
235: IF (l_Debug_Level <= 1) THEN
236: cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
235: IF (l_Debug_Level <= 1) THEN
236: cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
236: cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
237: cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
238: cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
239: cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
240: cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248: cln_debug_pub.Add('======================================= ',1);
241: cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248: cln_debug_pub.Add('======================================= ',1);
249: END IF;
242: cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248: cln_debug_pub.Add('======================================= ',1);
249: END IF;
250:
243: cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248: cln_debug_pub.Add('======================================= ',1);
249: END IF;
250:
251:
244: cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245: cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246: cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247: cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248: cln_debug_pub.Add('======================================= ',1);
249: END IF;
250:
251:
252: IF (l_Debug_Level <= 1) THEN
249: END IF;
250:
251:
252: IF (l_Debug_Level <= 1) THEN
253: cln_debug_pub.Add('Workflow event- oracle.apps.cln.inv.genreport', 1);
254: END IF;
255:
256: WF_EVENT.Raise('oracle.apps.cln.inv.genreport',l_event_key, NULL, l_cln_inv_parameters, NULL);
257:
259: x_errbuf := 'Successful';
260:
261: -- check the error message
262: IF (l_Debug_Level <= 1) THEN
263: cln_debug_pub.Add(l_msg_data,1);
264: END IF;
265:
266: IF (l_Debug_Level <= 2) THEN
267: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
263: cln_debug_pub.Add(l_msg_data,1);
264: END IF;
265:
266: IF (l_Debug_Level <= 2) THEN
267: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
268: END IF;
269:
270:
271: -- Exception Handling
274: WHEN FND_API.G_EXC_ERROR THEN
275: x_retcode := 2 ;
276: x_errbuf := l_msg_data;
277: IF (l_Debug_Level <= 5) THEN
278: cln_debug_pub.Add(l_msg_data,4);
279: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
280: END IF;
281:
282:
275: x_retcode := 2 ;
276: x_errbuf := l_msg_data;
277: IF (l_Debug_Level <= 5) THEN
278: cln_debug_pub.Add(l_msg_data,4);
279: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
280: END IF;
281:
282:
283: WHEN OTHERS THEN
289: FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
290: l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
291: x_errbuf := l_msg_data;
292: IF (l_Debug_Level <= 5) THEN
293: cln_debug_pub.Add(l_msg_data,6);
294: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
295: END IF;
296:
297:
290: l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
291: x_errbuf := l_msg_data;
292: IF (l_Debug_Level <= 5) THEN
293: cln_debug_pub.Add(l_msg_data,6);
294: cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
295: END IF;
296:
297:
298: END RAISE_REPORT_GEN_EVENT;
369:
370: BEGIN
371:
372: -- Sets the debug mode to be FILE
373: --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
374:
375: IF (l_Debug_Level <= 2) THEN
376: cln_debug_pub.Add('----- Entering GET_XML_TAG_VALUES API ------- ',2);
377: END IF;
372: -- Sets the debug mode to be FILE
373: --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
374:
375: IF (l_Debug_Level <= 2) THEN
376: cln_debug_pub.Add('----- Entering GET_XML_TAG_VALUES API ------- ',2);
377: END IF;
378:
379:
380: -- Initialize API return status to success
381: l_msg_data := 'successfully obtained quantity values for On Hand/ Available To use / Blocked / Allocated Items';
382:
383: -- Parameters received
384: IF (l_Debug_Level <= 1) THEN
385: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
382:
383: -- Parameters received
384: IF (l_Debug_Level <= 1) THEN
385: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
383: -- Parameters received
384: IF (l_Debug_Level <= 1) THEN
385: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
384: IF (l_Debug_Level <= 1) THEN
385: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
385: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
386: cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
387: cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
388: cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
389: cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
390: cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
391: cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
392: cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
393: cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
394: cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
395: cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
396: cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404: cln_debug_pub.Add('=================================================',1);
397: cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404: cln_debug_pub.Add('=================================================',1);
405: END IF;
398: cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404: cln_debug_pub.Add('=================================================',1);
405: END IF;
406:
399: cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404: cln_debug_pub.Add('=================================================',1);
405: END IF;
406:
407:
400: cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401: cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402: cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403: cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404: cln_debug_pub.Add('=================================================',1);
405: END IF;
406:
407:
408:
410: IF (p_item_revision IS NULL) THEN
411: l_is_revision_control := FALSE;
412:
413: IF (l_Debug_Level <= 1) THEN
414: cln_debug_pub.Add('Set the value of l_is_revision_control = FALSE',1);
415: END IF;
416: ELSE
417: l_is_revision_control := TRUE;
418:
416: ELSE
417: l_is_revision_control := TRUE;
418:
419: IF (l_Debug_Level <= 1) THEN
420: cln_debug_pub.Add('Set the value of l_is_revision_control = TRUE',1);
421: END IF;
422: END IF;
423: END IF;
424:
426: IF (p_lot_number IS NULL) THEN
427: l_is_lot_control := FALSE;
428:
429: IF (l_Debug_Level <= 1) THEN
430: cln_debug_pub.Add('Set the value of l_is_lot_control = FALSE',1);
431: END IF;
432: ELSE
433: l_is_lot_control := TRUE;
434:
432: ELSE
433: l_is_lot_control := TRUE;
434:
435: IF (l_Debug_Level <= 1) THEN
436: cln_debug_pub.Add('Set the value of l_is_lot_control = TRUE',1);
437: END IF;
438: END IF;
439: END IF;
440:
439: END IF;
440:
441: IF( p_tp_type = 'C') THEN
442: IF (l_Debug_Level <= 1) THEN
443: cln_debug_pub.Add('Check for the Customer type- C and assign the item name accordingly',1);
444: END IF;
445:
446: BEGIN
447: SELECT mci.customer_item_number
455: AND mci.customer_item_id(+) = mcix.customer_item_id;
456: EXCEPTION
457: WHEN NO_DATA_FOUND THEN
458: IF (l_Debug_Level <= 1) THEN
459: cln_debug_pub.Add('Customer Item Number Not Found ',1);
460: END IF;
461:
462: END;
463: IF (l_Debug_Level <= 1) THEN
460: END IF;
461:
462: END;
463: IF (l_Debug_Level <= 1) THEN
464: cln_debug_pub.Add('Item number defined for the customer is :'||x_customer_item_number,1);
465: END IF;
466:
467: END IF;
468:
468:
469:
470: -- Check for Profile value of Blocked Quantity
471: IF (l_Debug_Level <= 1) THEN
472: cln_debug_pub.Add('Check for the profile value of the Blocked quantity',1);
473: END IF;
474:
475: l_profile_blocked_qty_eq := FND_PROFILE.VALUE('CLN_DEF_BLOCKED_QTY');
476: IF (l_Debug_Level <= 1) THEN
473: END IF;
474:
475: l_profile_blocked_qty_eq := FND_PROFILE.VALUE('CLN_DEF_BLOCKED_QTY');
476: IF (l_Debug_Level <= 1) THEN
477: cln_debug_pub.Add('Blocked Quantity Eq set up in Profile value :'||l_profile_blocked_qty_eq,1);
478: END IF;
479:
480:
481: IF ( l_profile_blocked_qty_eq = 'NON_NETTABLE') THEN
496: EXCEPTION
497: WHEN NO_DATA_FOUND THEN
498: -- unreached code
499: IF (l_Debug_Level <= 1) THEN
500: cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
501: END IF;
502:
503: END;
504: IF (l_Debug_Level <= 1) THEN
501: END IF;
502:
503: END;
504: IF (l_Debug_Level <= 1) THEN
505: cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
506: END IF;
507:
508: ELSIF ( l_profile_blocked_qty_eq = 'NON_ATPABLE') THEN
509: l_tree_mode := 2;
524: EXCEPTION
525: WHEN NO_DATA_FOUND THEN
526: -- unreached code
527: IF (l_Debug_Level <= 1) THEN
528: cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
529: END IF;
530:
531: END;
532: IF (l_Debug_Level <= 1) THEN
529: END IF;
530:
531: END;
532: IF (l_Debug_Level <= 1) THEN
533: cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
534: END IF;
535:
536: ELSIF ( l_profile_blocked_qty_eq = 'NON_NETTABLE_OR_NON_ATPABLE') THEN
537: l_tree_mode := 2;
552: EXCEPTION
553: WHEN NO_DATA_FOUND THEN
554: -- unreached code
555: IF (l_Debug_Level <= 1) THEN
556: cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
557: END IF;
558:
559: END;
560: IF (l_Debug_Level <= 1) THEN
557: END IF;
558:
559: END;
560: IF (l_Debug_Level <= 1) THEN
561: cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
562: END IF;
563:
564: END IF;
565:
563:
564: END IF;
565:
566: IF (l_Debug_Level <= 1) THEN
567: cln_debug_pub.Add('Tree Mode : '||l_tree_mode,1);
568: cln_debug_pub.Add('OnHand Source : '||l_onhand_source,1);
569:
570: cln_debug_pub.Add('Call Inventory API .....',1);
571: END IF;
564: END IF;
565:
566: IF (l_Debug_Level <= 1) THEN
567: cln_debug_pub.Add('Tree Mode : '||l_tree_mode,1);
568: cln_debug_pub.Add('OnHand Source : '||l_onhand_source,1);
569:
570: cln_debug_pub.Add('Call Inventory API .....',1);
571: END IF;
572:
566: IF (l_Debug_Level <= 1) THEN
567: cln_debug_pub.Add('Tree Mode : '||l_tree_mode,1);
568: cln_debug_pub.Add('OnHand Source : '||l_onhand_source,1);
569:
570: cln_debug_pub.Add('Call Inventory API .....',1);
571: END IF;
572:
573:
574: inv_quantity_tree_pub.query_quantities
606: );
607:
608:
609: IF (l_Debug_Level <= 1) THEN
610: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
607:
608:
609: IF (l_Debug_Level <= 1) THEN
610: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
608:
609: IF (l_Debug_Level <= 1) THEN
610: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
609: IF (l_Debug_Level <= 1) THEN
610: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
610: cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
611: cln_debug_pub.Add('Return Status - '||x_return_status,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
612: cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620: cln_debug_pub.Add('=================================================',1);
613: cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620: cln_debug_pub.Add('=================================================',1);
621: END IF;
614: cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620: cln_debug_pub.Add('=================================================',1);
621: END IF;
622:
615: cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620: cln_debug_pub.Add('=================================================',1);
621: END IF;
622:
623:
616: cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617: cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618: cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619: cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620: cln_debug_pub.Add('=================================================',1);
621: END IF;
622:
623:
624: IF ( x_return_status <> 'S') THEN
624: IF ( x_return_status <> 'S') THEN
625: x_msg_data := l_msg_data;
626: l_msg_data :='Error in Inventory API. Detail error msg -> '||l_msg_data;
627: IF (l_Debug_Level <= 2) THEN
628: cln_debug_pub.Add(' Error : '||l_msg_data,2);
629: END IF;
630:
631: RAISE FND_API.G_EXC_ERROR;
632: END IF;
641: ELSE
642: x_quantity_allocated := l_quantity_reserved + l_quantity_suggested;
643: END IF;
644: IF (l_Debug_Level <= 1) THEN
645: cln_debug_pub.Add('Allocated Quantity : '||x_quantity_allocated,1);
646: cln_debug_pub.Add('Blocked Quantity : '||x_quantity_blocked,1);
647: END IF;
648:
649:
642: x_quantity_allocated := l_quantity_reserved + l_quantity_suggested;
643: END IF;
644: IF (l_Debug_Level <= 1) THEN
645: cln_debug_pub.Add('Allocated Quantity : '||x_quantity_allocated,1);
646: cln_debug_pub.Add('Blocked Quantity : '||x_quantity_blocked,1);
647: END IF;
648:
649:
650: IF (p_diposition_available = 'Available To Use') THEN
650: IF (p_diposition_available = 'Available To Use') THEN
651: x_quantity_on_hand := l_qty_on_hand - (l_quantity_reserved + l_quantity_suggested);-- qty on hand already takes out blocked
652: ELSE
653: IF (l_Debug_Level <= 1) THEN
654: cln_debug_pub.Add('Call Inventory API for calculating On Hand Qty : ',1);
655: cln_debug_pub.Add('Tree Mode : '||2,1);
656: cln_debug_pub.Add('OnHand Source : '||3,1);
657: END IF;
658:
651: x_quantity_on_hand := l_qty_on_hand - (l_quantity_reserved + l_quantity_suggested);-- qty on hand already takes out blocked
652: ELSE
653: IF (l_Debug_Level <= 1) THEN
654: cln_debug_pub.Add('Call Inventory API for calculating On Hand Qty : ',1);
655: cln_debug_pub.Add('Tree Mode : '||2,1);
656: cln_debug_pub.Add('OnHand Source : '||3,1);
657: END IF;
658:
659:
652: ELSE
653: IF (l_Debug_Level <= 1) THEN
654: cln_debug_pub.Add('Call Inventory API for calculating On Hand Qty : ',1);
655: cln_debug_pub.Add('Tree Mode : '||2,1);
656: cln_debug_pub.Add('OnHand Source : '||3,1);
657: END IF;
658:
659:
660: inv_quantity_tree_pub.query_quantities
694: IF ( x_return_status <> 'S') THEN
695: x_msg_data := l_msg_data;
696: l_msg_data :='Error in Inventory API. Detail error msg -> '||l_msg_data;
697: IF (l_Debug_Level <= 1) THEN
698: cln_debug_pub.Add(' Error : '||l_msg_data,1);
699: END IF;
700:
701: RAISE FND_API.G_EXC_ERROR;
702: END IF;
703: x_quantity_on_hand := l_qty_on_hand;
704: END IF;
705:
706: IF (l_Debug_Level <= 1) THEN
707: cln_debug_pub.Add('On Hand Quantity is = '||x_quantity_on_hand,1);
708: END IF;
709:
710:
711: FND_MESSAGE.SET_NAME('CLN','CLN_INV_REPORT_GENERATED');
710:
711: FND_MESSAGE.SET_NAME('CLN','CLN_INV_REPORT_GENERATED');
712: l_msg_data := FND_MESSAGE.GET;
713: IF (l_Debug_Level <= 1) THEN
714: cln_debug_pub.Add('Success Message : '||l_msg_data,1);
715: END IF;
716:
717: IF (l_Debug_Level <= 2) THEN
718: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
714: cln_debug_pub.Add('Success Message : '||l_msg_data,1);
715: END IF;
716:
717: IF (l_Debug_Level <= 2) THEN
718: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
719: END IF;
720:
721: x_return_status := 'S';
722:
724: EXCEPTION
725: WHEN FND_API.G_EXC_ERROR THEN
726: x_return_status := FND_API.G_RET_STS_ERROR ;
727: IF (l_Debug_Level <= 5) THEN
728: cln_debug_pub.Add(l_msg_data,4);
729: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
730: END IF;
731:
732:
725: WHEN FND_API.G_EXC_ERROR THEN
726: x_return_status := FND_API.G_RET_STS_ERROR ;
727: IF (l_Debug_Level <= 5) THEN
728: cln_debug_pub.Add(l_msg_data,4);
729: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
730: END IF;
731:
732:
733: WHEN OTHERS THEN
739: FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
740: l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
741: x_msg_data := l_msg_data;
742: IF (l_Debug_Level <= 5) THEN
743: cln_debug_pub.Add(l_msg_data,6);
744: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
745: END IF;
746:
747:
740: l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
741: x_msg_data := l_msg_data;
742: IF (l_Debug_Level <= 5) THEN
743: cln_debug_pub.Add(l_msg_data,6);
744: cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
745: END IF;
746:
747:
748: END GET_XML_TAG_VALUES;