[Home] [Help]
PACKAGE BODY: APPS.PO_MOD_SYNC_PROCESS_PVT
Source
1 PACKAGE BODY PO_MOD_SYNC_PROCESS_PVT AS
2 /* $Header: PO_MOD_SYNC_PROCESS_PVT.plb 120.20.12020000.5 2013/04/18 04:37:36 amalick ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_MOD_SYNC_PROCESS_PVT');
6 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DRAFT_MERGE_PKG';
8 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9
10 g_wf_item_type po_headers_all.wf_item_type%TYPE;
11 g_wf_item_key po_headers_all.wf_item_key%TYPE;
12 g_debug_stmt CONSTANT BOOLEAN := (PO_DEBUG.is_debug_stmt_on And (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)); /* In Bug# 5028960, Modified to Avoid File.sql.46 error*/
13 g_debug_unexp CONSTANT BOOLEAN := (PO_DEBUG.is_debug_unexp_on AND (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)); /* In Bug# 5028960, Modified to Avoid File.sql.46 error*/
14
15 G_MOD_SYNC_LOCK CONSTANT VARCHAR2(20) := 'MOD_SYNC';
16
17 PROCEDURE sync_mod_updatable_attrs
18 ( p_draft_id IN NUMBER
19 ,x_return_status OUT NOCOPY VARCHAR2
20 );
21
22 PROCEDURE sync_lines
23 ( p_draft_id IN NUMBER
24 ,p_uda_enabled_flag IN VARCHAR2
25 ,x_return_status OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE sync_shipments
29 ( p_draft_id IN NUMBER
30 ,p_uda_enabled_flag VARCHAR2
31 ,x_return_status OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE sync_distributions
35 (
36 p_draft_id IN NUMBER
37 ,x_return_status OUT NOCOPY VARCHAR2
38 );
39
40 PROCEDURE sync_headers_ext
41 (
42 p_draft_id IN NUMBER
43 ,x_return_status OUT NOCOPY VARCHAR2
44 );
45
46 PROCEDURE sync_lines_ext
47 (
48 p_draft_id IN NUMBER
49 ,x_return_status OUT NOCOPY VARCHAR2
50 ,p_po_line_s_no_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
51 ,p_po_line_s_no_p_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
52 );
53
54 PROCEDURE sync_shipments_ext
55 (
56 p_draft_id IN NUMBER
57 ,x_return_status OUT NOCOPY VARCHAR2
58 ,p_po_line_loc_s_no_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
59 );
60
61 PROCEDURE sync_uda_address
62 (
63 p_draft_id IN NUMBER
64 ,p_ignore_rev_number IN VARCHAR2 DEFAULT 'N'
65 ,x_return_status OUT NOCOPY VARCHAR2
66 );
67
68
69 procedure log_debug(text varchar2)
70 is
71 begin
72
73 IF (g_wf_item_type is not null and
74 g_wf_item_key is not null and
75 g_po_wf_debug = 'Y') THEN
76 PO_WF_DEBUG_PKG.insert_debug(g_wf_item_type,g_wf_item_key,text);
77 END IF;
78 IF g_wf_item_type is null and
79 g_wf_item_key is null and
80 g_debug_stmt THEN
81 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
82 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,d_pkg_name, text);
83 END IF;
84 END IF;
85
86 end;
87
88 procedure system_reject_mod(p_draft_id IN number,
89 x_return_status OUT NOCOPY varchar2)
90 is
91 l_document_id number;
92 l_document_type varchar2(25);
93 l_document_subtype varchar2(25);
94 l_preparer_id number;
95 begin
96 -- TBD need call po_draf_merge procedure
97 log_debug('system_reject_mod : Begin ');
98 SELECT po_header_id,
99 decode(type_lookup_code,
100 'STANDARD', 'PO',
101 'BLANKET', 'PA',
102 'CONTRACT', 'PA', null),
103 type_lookup_code,
104 created_by
105 INTO
106 l_document_id,
107 l_document_type,
108 l_document_subtype,
109 l_preparer_id
110 FROM po_headers_merge_v
111 WHERE draft_id = p_draft_id;
112
113 log_debug('system_reject_mod : call PO_DOCUMENT_ACTION_UTIL.change_doc_auth_stat');
114 PO_DOCUMENT_ACTION_UTIL.change_doc_auth_state(
115 p_document_id => l_document_id
116 , p_document_type => l_document_type
117 , p_document_subtype => l_document_subtype
118 , p_draft_id => p_draft_id -- CLM Apprvl
119 , p_action => 'REJECT'
120 , p_fwd_to_id => NULL
121 , p_offline_code => NULL
122 , p_approval_path_id => NULL
123 , p_note => NULL
124 , p_new_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED
125 , p_notify_action => 'REJECTED_BY_APPROVER'
126 , p_notify_employee => l_preparer_id
127 , x_return_status => x_return_status
128 );
129 log_debug('system_reject_mod : PO_DOCUMENT_ACTION_UTIL.change_doc_auth_stat'||
130 ' return_status '|| x_return_status);
131
132 exception
133 when others then
134 raise;
135 end;
136
137 procedure get_errors(p_results in po_validation_results_type,
138 x_results in out NOCOPY varchar2)
139 is
140 l_results varchar2(2000);
141 l_message_len number := 0;
142 l_new_message varchar2(1000);
143 l_new_message_len number := 0;
144 begin
145 log_debug('get_errors : Start ');
146 if p_results is null then
147 return;
148 end if;
149 for i in 1..p_results.validation_id.count loop
150 if p_results.message_name(i) is not null then
151 log_debug('message '||p_results.message_name(i));
152 fnd_message.set_name(p_results.message_application(i),
153 p_results.message_name(i));
154 if p_results.token1_name(i) is not null then
155 fnd_message.set_token(p_results.token1_name(i),
156 p_results.token1_value(i));
157 end if;
158 if p_results.token2_name(i) is not null then
159 fnd_message.set_token(p_results.token2_name(i),
160 p_results.token2_value(i));
161 end if;
162 if p_results.token3_name(i) is not null then
163 fnd_message.set_token(p_results.token3_name(i),
164 p_results.token3_value(i));
165 end if;
166 if p_results.token4_name(i) is not null then
167 fnd_message.set_token(p_results.token4_name(i),
168 p_results.token4_value(i));
169 end if;
170 if p_results.token5_name(i) is not null then
171 fnd_message.set_token(p_results.token5_name(i),
172 p_results.token5_value(i));
173 end if;
174 if p_results.token6_name(i) is not null then
175 fnd_message.set_token(p_results.token6_name(i),
176 p_results.token6_value(i));
177 end if;
178 l_new_message := fnd_message.get;
179 log_debug('new message '|| l_new_message);
180 l_new_message_len := length(l_new_message);
181 if ((l_message_len + l_new_message_len) < 2000) then
182 if l_results is null then
183 l_results := l_new_message;
184 else
185 l_results := l_results||' '||l_new_message;
186 end if;
187 else
188 x_results := l_results;
189 return;
190 end if;
191 l_message_len := length(l_results);
192 end if;
193 end loop;
194 x_results := l_results;
195 log_debug('get_errors : End : x_results '||x_results);
196 end;
197
198 procedure sync_with_award_process(p_draft_id IN NUMBER,
199 p_document_type IN VARCHAR2,
200 p_document_id IN NUMBER,
201 p_reject_if_error IN VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_results OUT NOCOPY VARCHAR2) is
204 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
205 l_document_id NUMBER;
206
207 l_headers PO_HEADERS_VAL_TYPE := PO_HEADERS_VAL_TYPE();
208 l_lines PO_LINES_VAL_TYPE := PO_LINES_VAL_TYPE();
209 l_shipments PO_LINE_LOCATIONS_VAL_TYPE := PO_LINE_LOCATIONS_VAL_TYPE();
210 l_distributions PO_DISTRIBUTIONS_VAL_TYPE := PO_DISTRIBUTIONS_VAL_TYPE();
211 l_price_adjs PO_PRICE_ADJS_VAL_TYPE := PO_PRICE_ADJS_VAL_TYPE();
212 l_price_diff PO_PRICE_DIFF_VAL_TYPE := PO_PRICE_DIFF_VAL_TYPE();
213 l_result_type VARCHAR2(30);
214 l_result_text VARCHAR2(2000);
215 l_results po_validation_results_type;
216 cursor c_changed_shipments is
217 select
218 /*1*/ pld.ACCRUE_ON_RECEIPT_FLAG
219 /*2*/ ,pld.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
220 /*3*/ ,pld.AMOUNT
221 /*4*/ ,pld.AMOUNT_ACCEPTED
222 /*5*/ ,pll.AMOUNT_BILLED -- to update draft
223 /*6*/ ,pld.AMOUNT_CANCELLED
224 /*7*/ ,pld.AMOUNT_FINANCED
225 /*8*/ ,pll.AMOUNT_RECEIVED -- to update draft
226 /*9*/ ,pld.AMOUNT_RECOUPED
227 /*10*/ ,pld.AMOUNT_REJECTED
228 /*1*/ --AMOUNT_SHIPPED
229 /*11*/ ,pld.APPROVED_DATE
230 /*12*/ ,pld.APPROVED_FLAG
231 /*13*/ ,pld.ATTRIBUTE_CATEGORY
232 /*14*/ ,pld.ATTRIBUTE1
233 /*15*/ ,pld.ATTRIBUTE10
234 /*16*/ ,pld.ATTRIBUTE11
235 /*17*/ ,pld.ATTRIBUTE12
236 /*18*/ ,pld.ATTRIBUTE13
237 /*19*/ ,pld.ATTRIBUTE14
238 /*20*/ ,pld.ATTRIBUTE15
239 /*21*/ ,pld.ATTRIBUTE2
240 /*22*/ ,pld.ATTRIBUTE3
241 /*23*/ ,pld.ATTRIBUTE4
242 /*24*/ ,pld.ATTRIBUTE5
243 /*25*/ ,pld.ATTRIBUTE6
244 /*26*/ ,pld.ATTRIBUTE7
245 /*27*/ ,pld.ATTRIBUTE8
246 /*28*/ ,pld.ATTRIBUTE9
247 /*29*/ ,pld.BID_PAYMENT_ID
248 /*30*/ ,pld.CALCULATE_TAX_FLAG
249 /*31*/ ,pld.CANCEL_DATE
250 /*32*/ ,pld.CANCEL_FLAG
251 /*33*/ ,pld.CANCEL_REASON
252 /*34*/ ,pld.CANCELLED_BY
253 /*35*/ ,pld.CHANGE_ACCEPTED_FLAG
254 /*36*/ ,pld.CHANGE_PROMISED_DATE_REASON
255 /*1*/ --CHANGE_STATUS
256 /*37*/ ,pld.CLOSED_BY
257 /*38*/ ,pld.CLOSED_CODE
258 /*39*/ ,pld.CLOSED_DATE
259 /*40*/ ,pld.CLOSED_FLAG
260 /*41*/ ,pld.CLOSED_FOR_INVOICE_DATE
261 /*42*/ ,pld.CLOSED_FOR_RECEIVING_DATE
262 /*43*/ ,pld.CLOSED_REASON
263 /*44*/ ,pld.CONSIGNED_FLAG
264 /*1*/ --CONTROL_ACTION
265 /*45*/ ,pld.COUNTRY_OF_ORIGIN_CODE
266 /*46*/ ,pld.CREATED_BY
267 /*47*/ ,pld.CREATION_DATE
268 /*48*/ ,pld.DAYS_EARLY_RECEIPT_ALLOWED
269 /*49*/ ,pld.DAYS_LATE_RECEIPT_ALLOWED
270 /*50*/ ,pld.DELETE_FLAG
271 /*51*/ ,pld.DESCRIPTION
272 /*52*/ ,pld.DRAFT_ID
273 /*53*/ ,pld.DROP_SHIP_FLAG
274 /*54*/ ,pld.ENCUMBER_NOW
275 /*55*/ ,pld.ENCUMBERED_DATE
276 /*56*/ ,pld.ENCUMBERED_FLAG
277 /*57*/ ,pld.END_DATE
278 /*58*/ ,pld.ENFORCE_SHIP_TO_LOCATION_CODE
279 /*59*/ ,pld.ESTIMATED_TAX_AMOUNT
280 /*60*/ ,pld.FINAL_MATCH_FLAG
281 /*61*/ ,pld.FIRM_DATE
282 /*62*/ , null
283 /*63*/ ,pld.FIRM_STATUS_LOOKUP_CODE
284 /*64*/ ,pld.FOB_LOOKUP_CODE
285 /*65*/ , null
286 /*66*/ ,pld.FREIGHT_TERMS_LOOKUP_CODE
287 /*67*/ , null
288 /*68*/ ,pld.FROM_HEADER_ID
289 /*69*/ ,pld.FROM_LINE_ID
290 /*70*/ ,pld.FROM_LINE_LOCATION_ID
291 /*71*/ ,pld.GLOBAL_ATTRIBUTE_CATEGORY
292 /*72*/ ,pld.GLOBAL_ATTRIBUTE1
293 /*73*/ ,pld.GLOBAL_ATTRIBUTE10
294 /*74*/ ,pld.GLOBAL_ATTRIBUTE11
295 /*75*/ ,pld.GLOBAL_ATTRIBUTE12
296 /*76*/ ,pld.GLOBAL_ATTRIBUTE13
297 /*77*/ ,pld.GLOBAL_ATTRIBUTE14
298 /*78*/ ,pld.GLOBAL_ATTRIBUTE15
299 /*79*/ ,pld.GLOBAL_ATTRIBUTE16
300 /*80*/ ,pld.GLOBAL_ATTRIBUTE17
301 /*81*/ ,pld.GLOBAL_ATTRIBUTE18
302 /*82*/ ,pld.GLOBAL_ATTRIBUTE19
303 /*83*/ ,pld.GLOBAL_ATTRIBUTE2
304 /*84*/ ,pld.GLOBAL_ATTRIBUTE20
305 /*85*/ ,pld.GLOBAL_ATTRIBUTE3
306 /*86*/ ,pld.GLOBAL_ATTRIBUTE4
307 /*87*/ ,pld.GLOBAL_ATTRIBUTE5
308 /*88*/ ,pld.GLOBAL_ATTRIBUTE6
309 /*89*/ ,pld.GLOBAL_ATTRIBUTE7
310 /*90*/ ,pld.GLOBAL_ATTRIBUTE8
311 /*91*/ ,pld.GLOBAL_ATTRIBUTE9
312 /*92*/ ,pld.GOVERNMENT_CONTEXT
313 /*93*/ , null
314 /*94*/ , null
315 /*95*/ , null
316 /*96*/ ,pld.INSPECTION_REQUIRED_FLAG
317 /*97*/ , null
318 /*98*/ ,pld.INVOICE_CLOSE_TOLERANCE
319 /*99*/ , null
320 /*100*/ , null
321 /*101*/ , null
322 /*102*/ ,pld.LAST_ACCEPT_DATE
323 /*103*/ ,pld.LAST_UPDATE_DATE
324 /*104*/ ,pld.LAST_UPDATE_LOGIN
325 /*105*/ ,pld.LAST_UPDATED_BY
326 /*106*/ ,pld.LEAD_TIME
327 /*107*/ ,pld.LEAD_TIME_UNIT
328 /*108*/ , null
329 /*109*/ , null
330 /*110*/ ,pld.LINE_LOCATION_ID
331 /*111*/ , null
332 /*112*/ , null
333 /*113*/ ,pld.MANUAL_PRICE_CHANGE_FLAG
334 /*114*/ ,pld.MATCH_OPTION
335 /*115*/ ,pld.MATCHING_BASIS
336 /*116*/ ,pld.NEED_BY_DATE
337 /*117*/ ,pld.NOTE_TO_RECEIVER
338 /*118*/ , null
339 /*119*/ ,pld.ORG_ID
340 /*1*/ --OUTSOURCED_ASSEMBLY
341 /*1*/ --OWNER_ROLE
342 /*1*/ --OWNER_USER_ID
343 /*120*/ ,pld.PAYMENT_TYPE
344 /*121*/ ,pld.PO_HEADER_ID
345 /*122*/ ,pld.PO_LINE_ID
346 /*123*/ ,pld.PO_RELEASE_ID
347 /*124*/ ,pld.PREFERRED_GRADE
348 /*125*/ ,pld.PRICE_DISCOUNT
349 /*126*/ ,pld.PRICE_OVERRIDE
350 /*127*/ ,pld.PROGRAM_APPLICATION_ID
351 /*128*/ ,pld.PROGRAM_ID
352 /*129*/ ,pld.PROGRAM_UPDATE_DATE
353 /*130*/ ,pld.PROMISED_DATE
354 /*131*/ , null
355 /*132*/ ,pld.QTY_RCV_EXCEPTION_CODE
356 /*133*/ ,pld.QTY_RCV_TOLERANCE
357 /*134*/ ,pld.QUANTITY
358 /*135*/ ,pld.QUANTITY_ACCEPTED
359 /*136*/ ,pll.QUANTITY_BILLED -- to update draft
360 /*137*/ ,pld.QUANTITY_CANCELLED
361 /*138*/ ,pld.QUANTITY_FINANCED
362 /*139*/ ,pll.QUANTITY_RECEIVED -- to update draft
363 /*140*/ ,pld.QUANTITY_RECOUPED
364 /*141*/ ,pld.QUANTITY_REJECTED
365 /*142*/ ,pld.QUANTITY_SHIPPED
366 /*143*/ ,pld.RECEIPT_DAYS_EXCEPTION_CODE
367 /*144*/ ,pld.RECEIPT_REQUIRED_FLAG
368 /*145*/ ,pld.RECEIVE_CLOSE_TOLERANCE
369 /*146*/ ,pld.RECEIVING_ROUTING_ID
370 /*147*/ ,pld.REQUEST_ID
371 /*148*/ ,pld.RETAINAGE_RELEASED_AMOUNT
372 /*149*/ ,pld.RETAINAGE_WITHHELD_AMOUNT
373 /*150*/ ,pld.RETROACTIVE_DATE
374 /*151*/ ,pld.SALES_ORDER_UPDATE_DATE
375 /*152*/ ,pld.SECONDARY_QUANTITY
376 /*153*/ ,pld.SECONDARY_QUANTITY_ACCEPTED
377 /*154*/ ,pld.SECONDARY_QUANTITY_CANCELLED
378 /*155*/ ,pld.SECONDARY_QUANTITY_RECEIVED
379 /*156*/ ,pld.SECONDARY_QUANTITY_REJECTED
380 /*157*/ ,pld.SECONDARY_QUANTITY_SHIPPED
381 /*158*/ ,pld.SECONDARY_UNIT_OF_MEASURE
382 /*159*/ ,pld.SHIP_TO_LOCATION_ID
383 /*160*/ ,pld.SHIP_TO_ORGANIZATION_ID
384 /*161*/ ,pld.SHIP_VIA_LOOKUP_CODE
385 /*162*/ ,pld.SHIPMENT_CLOSED_DATE
386 /*163*/ ,pld.SHIPMENT_NUM
387 /*164*/ ,pld.SHIPMENT_TYPE
388 /*165*/ ,pld.SOURCE_SHIPMENT_ID
389 /*166*/ ,pld.START_DATE
390 /*1*/ --STATUS
391 /*167*/ ,pld.SUPPLIER_ORDER_LINE_NUMBER
392 /*1*/ --TAX_ATTRIBUTE_UPDATE_CODE
393 /*168*/ ,pld.TAX_CODE_ID
394 /*169*/ ,pld.TAX_NAME
395 /*170*/ ,pld.TAX_USER_OVERRIDE_FLAG
396 /*171*/ ,pld.TAXABLE_FLAG
397 /*172*/ ,pld.TERMS_ID
398 /*173*/ , null
399 /*174*/ ,pld.TRANSACTION_FLOW_HEADER_ID
400 /*175*/ ,pld.UNENCUMBERED_QUANTITY
401 /*176*/ ,pld.UNIT_MEAS_LOOKUP_CODE
402 /*177*/ ,pld.UNIT_OF_MEASURE_CLASS
403 /*178*/ ,pld.USSGL_TRANSACTION_CODE
404 /*179*/ ,pld.VALUE_BASIS
405 /*180*/ ,pld.VMI_FLAG
406 /*181*/ ,pld.WORK_APPROVER_ID
407 /*pll.line_location_id,
408 pll.quantity_received,
409 pll.quantity_billed,
410 pll.amount_received,
411 pll.amount_billed*/
412 from po_line_locations_all pll, po_line_locations_draft_all pld
413 where pld.line_location_id = pll.line_location_id
414 and pld.draft_id = p_draft_id
415 and (pld.quantity_received <> pll.quantity_received or
416 pld.quantity_billed <> pll.quantity_billed or
417 pld.amount_received <> pll.amount_received or
418 pld.amount_billed <> pll.amount_billed);
419
420 cursor c_changed_distributions is
421 select
422 /*1*/ pdd.ACCRUAL_ACCOUNT_ID
423 /*2*/ ,pdd.ACCRUE_ON_RECEIPT_FLAG
424 /*3*/ ,pdd.ACCRUED_FLAG
425 /*4*/ ,pd.AMOUNT_BILLED --updated to draft as part of sync
426 /*5*/ ,pdd.AMOUNT_CANCELLED
427 /*6*/ ,pdd.AMOUNT_DELIVERED
428 /*7*/ ,pdd.AMOUNT_FINANCED
429 /*8*/ ,pdd.AMOUNT_ORDERED
430 /*9*/ ,pdd.AMOUNT_RECOUPED
431 /*10*/ ,pdd.AMOUNT_TO_ENCUMBER
432 /*11*/ ,pdd.ATTRIBUTE_CATEGORY
433 /*12*/ ,pdd.ATTRIBUTE1
434 /*13*/ ,pdd.ATTRIBUTE10
435 /*14*/ ,pdd.ATTRIBUTE11
436 /*15*/ ,pdd.ATTRIBUTE12
437 /*16*/ ,pdd.ATTRIBUTE13
438 /*17*/ ,pdd.ATTRIBUTE14
439 /*18*/ ,pdd.ATTRIBUTE15
440 /*19*/ ,pdd.ATTRIBUTE2
441 /*20*/ ,pdd.ATTRIBUTE3
442 /*21*/ ,pdd.ATTRIBUTE4
443 /*22*/ ,pdd.ATTRIBUTE5
444 /*23*/ ,pdd.ATTRIBUTE6
445 /*24*/ ,pdd.ATTRIBUTE7
446 /*25*/ ,pdd.ATTRIBUTE8
447 /*26*/ ,pdd.ATTRIBUTE9
448 /*27*/ ,pdd.AWARD_ID
449 /*28*/ , null
450 /*29*/ ,pdd.BOM_RESOURCE_ID
451 /*30*/ ,pdd.BUDGET_ACCOUNT_ID
452 /*31*/ ,pdd.CHANGE_ACCEPTED_FLAG
453 /*1*/ --CHANGE_STATUS
454 /*32*/ ,pdd.CODE_COMBINATION_ID
455 /*33*/ ,pdd.CREATED_BY
456 /*34*/ ,pdd.CREATION_DATE
457 /*35*/ ,pdd.DELETE_FLAG
458 /*36*/ ,pdd.DELIVER_TO_LOCATION_ID
459 /*37*/ ,pdd.DELIVER_TO_PERSON_ID
460 /*38*/ ,pdd.DEST_CHARGE_ACCOUNT_ID
461 /*39*/ ,pdd.DEST_VARIANCE_ACCOUNT_ID
462 /*40*/ ,pdd.DESTINATION_CONTEXT
463 /*41*/ ,pdd.DESTINATION_ORGANIZATION_ID
464 /*42*/ ,pdd.DESTINATION_SUBINVENTORY
465 /*43*/ ,pdd.DESTINATION_TYPE_CODE
466 /*44*/ ,pdd.DISTRIBUTION_NUM
467 /*45*/ ,pdd.DISTRIBUTION_TYPE
468 /*46*/ ,pdd.DRAFT_ID
469 /*47*/ ,pdd.ENCUMBERED_AMOUNT
470 /*48*/ ,pdd.ENCUMBERED_FLAG
471 /*49*/ ,pdd.END_ITEM_UNIT_NUMBER
472 /*50*/ ,pdd.EXPENDITURE_ITEM_DATE
473 /*51*/ ,pdd.EXPENDITURE_ORGANIZATION_ID
474 /*52*/ ,pdd.EXPENDITURE_TYPE
475 /*53*/ , null
476 /*54*/ ,pdd.FAILED_FUNDS_LOOKUP_CODE
477 /*55*/ ,pdd.GL_CANCELLED_DATE
478 /*56*/ ,pdd.GL_CLOSED_DATE
479 /*57*/ ,pdd.GL_ENCUMBERED_DATE
480 /*58*/ ,pdd.GL_ENCUMBERED_PERIOD_NAME
481 /*59*/ ,pdd.GOVERNMENT_CONTEXT
482 /*1*/ --GROUP_LINE_ID
483 /*60*/ , null
484 /*61*/ , to_date(null)
485 /*62*/ , null
486 /*63*/ ,pdd.INVOICE_ADJUSTMENT_FLAG
487 /*64*/ ,pdd.KANBAN_CARD_ID
488 /*65*/ ,pdd.LAST_UPDATE_DATE
489 /*66*/ ,pdd.LAST_UPDATE_LOGIN
490 /*67*/ ,pdd.LAST_UPDATED_BY
491 /*68*/ , null
492 /*69*/ , null
493 /*70*/ ,pdd.LINE_LOCATION_ID
494 /*71*/ , null
495 /*72*/ , null
496 /*73*/ , null
497 /*74*/ ,pdd.MRC_ENCUMBERED_AMOUNT
498 /*75*/ ,pdd.MRC_RATE
499 /*76*/ ,pdd.MRC_RATE_DATE
500 /*77*/ ,pdd.MRC_UNENCUMBERED_AMOUNT
501 /*78*/ ,pdd.NONRECOVERABLE_TAX
502 /*79*/ ,pdd.OKE_CONTRACT_DELIVERABLE_ID
503 /*80*/ ,pdd.OKE_CONTRACT_LINE_ID
504 /*81*/ ,pdd.ORG_ID
505 /*1*/ --OWNER_ROLE
506 /*1*/ --OWNER_USER_ID
507 /*82*/ ,pdd.PO_DISTRIBUTION_ID
508 /*83*/ ,pdd.PO_HEADER_ID
509 /*84*/ ,pdd.PO_LINE_ID
510 /*85*/ ,pdd.PO_RELEASE_ID
511 /*86*/ ,pdd.PREVENT_ENCUMBRANCE_FLAG
512 /*87*/ ,pdd.PROGRAM_APPLICATION_ID
513 /*88*/ ,pdd.PROGRAM_ID
514 /*89*/ ,pdd.PROGRAM_UPDATE_DATE
515 /*90*/ ,pdd.PROJECT_ACCOUNTING_CONTEXT
516 /*91*/ ,pdd.PROJECT_ID
517 /*92*/ , null
518 /*93*/ ,pd.QUANTITY_BILLED --updated to draft while sync
519 /*94*/ ,pdd.QUANTITY_CANCELLED
520 /*94*/ ,pdd.QUANTITY_DELIVERED
521 /*96*/ ,pdd.QUANTITY_FINANCED
522 /*97*/ ,pdd.QUANTITY_ORDERED
523 /*98*/ ,pdd.QUANTITY_RECOUPED
524 /*99*/ ,pdd.RATE
525 /*100*/ ,pdd.RATE_DATE
526 /*101*/ ,pdd.RECOVERABLE_TAX
527 /*102*/ ,pdd.RECOVERY_RATE
528 /*103*/ ,pdd.REQ_DISTRIBUTION_ID
529 /*104*/ ,pdd.REQ_HEADER_REFERENCE_NUM
530 /*105*/ ,pdd.REQ_LINE_REFERENCE_NUM
531 /*106*/ ,pdd.REQUEST_ID
532 /*107*/ ,pdd.RETAINAGE_RELEASED_AMOUNT
533 /*108*/ ,pdd.RETAINAGE_WITHHELD_AMOUNT
534 /*109*/ ,pdd.SET_OF_BOOKS_ID
535 /*110*/ , null
536 /*111*/ , null
537 /*112*/ ,pdd.SOURCE_DISTRIBUTION_ID
538 /*1*/ --STATUS
539 /*113*/ ,pdd.TASK_ID
540 /*1*/ --TAX_ATTRIBUTE_UPDATE_CODE
541 /*114*/ ,pdd.TAX_RECOVERY_OVERRIDE_FLAG
542 /*115*/ , null
543 /*116*/ ,pdd.UNENCUMBERED_AMOUNT
544 /*117*/ ,pdd.UNENCUMBERED_QUANTITY
545 /*118*/ ,pdd.USSGL_TRANSACTION_CODE
546 /*119*/ ,pdd.VARIANCE_ACCOUNT_ID
547 /*120*/ ,pdd.WIP_ENTITY_ID
548 /*121*/ ,pdd.WIP_LINE_ID
549 /*122*/ ,pdd.WIP_OPERATION_SEQ_NUM
550 /*123*/ ,pdd.WIP_REPETITIVE_SCHEDULE_ID
551 /*124*/ ,pdd.WIP_RESOURCE_SEQ_NUM
552 /*pd.po_distribution_id,
553 pd.quantity_billed,
554 pd.amount_billed*/
555 from po_distributions_all pd, po_distributions_draft_all pdd
556 where pdd.po_distribution_id = pd.po_distribution_id
557 and pdd.draft_id = p_draft_id
558 and (pdd.quantity_billed <> pd.quantity_billed or
559 pdd.amount_billed <> pd.amount_billed);
560
561 BEGIN
562 log_debug('sync_with_award_process : start '||
563 'DocType '||p_document_type||' DraftId '||p_draft_id);
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565 if p_document_type = 'PO' then
566 --Sync action is only for the PO
567 --validate attributes
568 if p_draft_id = -1 then
569 return;
570 end if;
571 open c_changed_shipments;
572 fetch c_changed_shipments bulk collect into
573 /*1*/ l_shipments.ACCRUE_ON_RECEIPT_FLAG
574 /*2*/ ,l_shipments.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
575 /*3*/ ,l_shipments.AMOUNT
576 /*4*/ ,l_shipments.AMOUNT_ACCEPTED
577 /*5*/ ,l_shipments.AMOUNT_BILLED
578 /*6*/ ,l_shipments.AMOUNT_CANCELLED
579 /*7*/ ,l_shipments.AMOUNT_FINANCED
580 /*8*/ ,l_shipments.AMOUNT_RECEIVED
581 /*9*/ ,l_shipments.AMOUNT_RECOUPED
582 /*10*/ ,l_shipments.AMOUNT_REJECTED
583 /*11*/ ,l_shipments.APPROVED_DATE
584 /*12*/ ,l_shipments.APPROVED_FLAG
585 /*13*/ ,l_shipments.ATTRIBUTE_CATEGORY
586 /*14*/ ,l_shipments.ATTRIBUTE1
587 /*15*/ ,l_shipments.ATTRIBUTE10
588 /*16*/ ,l_shipments.ATTRIBUTE11
589 /*17*/ ,l_shipments.ATTRIBUTE12
590 /*18*/ ,l_shipments.ATTRIBUTE13
591 /*19*/ ,l_shipments.ATTRIBUTE14
592 /*20*/ ,l_shipments.ATTRIBUTE15
593 /*21*/ ,l_shipments.ATTRIBUTE2
594 /*22*/ ,l_shipments.ATTRIBUTE3
595 /*23*/ ,l_shipments.ATTRIBUTE4
596 /*24*/ ,l_shipments.ATTRIBUTE5
597 /*25*/ ,l_shipments.ATTRIBUTE6
598 /*26*/ ,l_shipments.ATTRIBUTE7
599 /*27*/ ,l_shipments.ATTRIBUTE8
600 /*28*/ ,l_shipments.ATTRIBUTE9
601 /*29*/ ,l_shipments.BID_PAYMENT_ID
602 /*30*/ ,l_shipments.CALCULATE_TAX_FLAG
603 /*31*/ ,l_shipments.CANCEL_DATE
604 /*32*/ ,l_shipments.CANCEL_FLAG
605 /*33*/ ,l_shipments.CANCEL_REASON
606 /*34*/ ,l_shipments.CANCELLED_BY
607 /*35*/ ,l_shipments.CHANGE_ACCEPTED_FLAG
608 /*36*/ ,l_shipments.CHANGE_PROMISED_DATE_REASON
609 /*37*/ ,l_shipments.CLOSED_BY
610 /*38*/ ,l_shipments.CLOSED_CODE
611 /*39*/ ,l_shipments.CLOSED_DATE
612 /*40*/ ,l_shipments.CLOSED_FLAG
613 /*41*/ ,l_shipments.CLOSED_FOR_INVOICE_DATE
614 /*42*/ ,l_shipments.CLOSED_FOR_RECEIVING_DATE
615 /*43*/ ,l_shipments.CLOSED_REASON
616 /*44*/ ,l_shipments.CONSIGNED_FLAG
617 /*45*/ ,l_shipments.COUNTRY_OF_ORIGIN_CODE
618 /*46*/ ,l_shipments.CREATED_BY
619 /*47*/ ,l_shipments.CREATION_DATE
620 /*48*/ ,l_shipments.DAYS_EARLY_RECEIPT_ALLOWED
621 /*49*/ ,l_shipments.DAYS_LATE_RECEIPT_ALLOWED
622 /*50*/ ,l_shipments.DELETE_FLAG
623 /*51*/ ,l_shipments.DESCRIPTION
624 /*52*/ ,l_shipments.DRAFT_ID
625 /*53*/ ,l_shipments.DROP_SHIP_FLAG
626 /*54*/ ,l_shipments.ENCUMBER_NOW
627 /*55*/ ,l_shipments.ENCUMBERED_DATE
628 /*56*/ ,l_shipments.ENCUMBERED_FLAG
629 /*57*/ ,l_shipments.END_DATE
630 /*58*/ ,l_shipments.ENFORCE_SHIP_TO_LOCATION_CODE
631 /*59*/ ,l_shipments.ESTIMATED_TAX_AMOUNT
632 /*60*/ ,l_shipments.FINAL_MATCH_FLAG
633 /*61*/ ,l_shipments.FIRM_DATE
634 /*62*/ ,l_shipments.FIRM_FLAG
635 /*63*/ ,l_shipments.FIRM_STATUS_LOOKUP_CODE
636 /*64*/ ,l_shipments.FOB_LOOKUP_CODE
637 /*65*/ ,l_shipments.FREIGHT_CARRIER
638 /*66*/ ,l_shipments.FREIGHT_TERMS_LOOKUP_CODE
639 /*67*/ ,l_shipments.FROM_DATE
640 /*68*/ ,l_shipments.FROM_HEADER_ID
641 /*69*/ ,l_shipments.FROM_LINE_ID
642 /*70*/ ,l_shipments.FROM_LINE_LOCATION_ID
643 /*71*/ ,l_shipments.GLOBAL_ATTRIBUTE_CATEGORY
644 /*72*/ ,l_shipments.GLOBAL_ATTRIBUTE1
645 /*73*/ ,l_shipments.GLOBAL_ATTRIBUTE10
646 /*74*/ ,l_shipments.GLOBAL_ATTRIBUTE11
647 /*75*/ ,l_shipments.GLOBAL_ATTRIBUTE12
648 /*76*/ ,l_shipments.GLOBAL_ATTRIBUTE13
649 /*77*/ ,l_shipments.GLOBAL_ATTRIBUTE14
650 /*78*/ ,l_shipments.GLOBAL_ATTRIBUTE15
651 /*79*/ ,l_shipments.GLOBAL_ATTRIBUTE16
652 /*80*/ ,l_shipments.GLOBAL_ATTRIBUTE17
653 /*81*/ ,l_shipments.GLOBAL_ATTRIBUTE18
654 /*82*/ ,l_shipments.GLOBAL_ATTRIBUTE19
655 /*83*/ ,l_shipments.GLOBAL_ATTRIBUTE2
656 /*84*/ ,l_shipments.GLOBAL_ATTRIBUTE20
657 /*85*/ ,l_shipments.GLOBAL_ATTRIBUTE3
658 /*86*/ ,l_shipments.GLOBAL_ATTRIBUTE4
659 /*87*/ ,l_shipments.GLOBAL_ATTRIBUTE5
660 /*88*/ ,l_shipments.GLOBAL_ATTRIBUTE6
661 /*89*/ ,l_shipments.GLOBAL_ATTRIBUTE7
662 /*90*/ ,l_shipments.GLOBAL_ATTRIBUTE8
663 /*91*/ ,l_shipments.GLOBAL_ATTRIBUTE9
664 /*92*/ ,l_shipments.GOVERNMENT_CONTEXT
665 /*93*/ ,l_shipments.HDR_END_DATE
666 /*94*/ ,l_shipments.HDR_START_DATE
667 /*95*/ ,l_shipments.HDR_STYLE_ID
668 /*96*/ ,l_shipments.INSPECTION_REQUIRED_FLAG
669 /*97*/ ,l_shipments.INTERFACE_ID
670 /*98*/ ,l_shipments.INVOICE_CLOSE_TOLERANCE
671 /*99*/ ,l_shipments.ITEM
672 /*100*/ ,l_shipments.ITEM_ID
673 /*101*/ ,l_shipments.ITEM_REVISION
674 /*102*/ ,l_shipments.LAST_ACCEPT_DATE
675 /*103*/ ,l_shipments.LAST_UPDATE_DATE
676 /*104*/ ,l_shipments.LAST_UPDATE_LOGIN
677 /*105*/ ,l_shipments.LAST_UPDATED_BY
678 /*106*/ ,l_shipments.LEAD_TIME
679 /*107*/ ,l_shipments.LEAD_TIME_UNIT
680 /*108*/ ,l_shipments.LINE_EXPIRATION_DATE
681 /*109*/ ,l_shipments.LINE_ITEM_ID
682 /*110*/ ,l_shipments.LINE_LOCATION_ID
683 /*111*/ ,l_shipments.LINE_ORDER_TYPE_LOOKUP_CODE
684 /*112*/ ,l_shipments.LINE_PRICE_BREAK_LOOKUP_CODE
685 /*113*/ ,l_shipments.MANUAL_PRICE_CHANGE_FLAG
686 /*114*/ ,l_shipments.MATCH_OPTION
687 /*115*/ ,l_shipments.MATCHING_BASIS
688 /*116*/ ,l_shipments.NEED_BY_DATE
689 /*117*/ ,l_shipments.NOTE_TO_RECEIVER
690 /*118*/ ,l_shipments.ORDER_TYPE_LOOKUP_CODE
691 /*119*/ ,l_shipments.ORG_ID
692 /*120*/ ,l_shipments.PAYMENT_TYPE
693 /*121*/ ,l_shipments.PO_HEADER_ID
694 /*122*/ ,l_shipments.PO_LINE_ID
695 /*123*/ ,l_shipments.PO_RELEASE_ID
696 /*124*/ ,l_shipments.PREFERRED_GRADE
697 /*125*/ ,l_shipments.PRICE_DISCOUNT
698 /*126*/ ,l_shipments.PRICE_OVERRIDE
699 /*127*/ ,l_shipments.PROGRAM_APPLICATION_ID
700 /*128*/ ,l_shipments.PROGRAM_ID
701 /*128*/ ,l_shipments.PROGRAM_UPDATE_DATE
702 /*130*/ ,l_shipments.PROMISED_DATE
703 /*131*/ ,l_shipments.PURCHASE_BASIS
704 /*132*/ ,l_shipments.QTY_RCV_EXCEPTION_CODE
705 /*133*/ ,l_shipments.QTY_RCV_TOLERANCE
706 /*134*/ ,l_shipments.QUANTITY
707 /*135*/ ,l_shipments.QUANTITY_ACCEPTED
708 /*136*/ ,l_shipments.QUANTITY_BILLED
709 /*137*/ ,l_shipments.QUANTITY_CANCELLED
710 /*138*/ ,l_shipments.QUANTITY_FINANCED
711 /*139*/ ,l_shipments.QUANTITY_RECEIVED
712 /*140*/ ,l_shipments.QUANTITY_RECOUPED
713 /*141*/ ,l_shipments.QUANTITY_REJECTED
714 /*142*/ ,l_shipments.QUANTITY_SHIPPED
715 /*143*/ ,l_shipments.RECEIPT_DAYS_EXCEPTION_CODE
716 /*144*/ ,l_shipments.RECEIPT_REQUIRED_FLAG
717 /*145*/ ,l_shipments.RECEIVE_CLOSE_TOLERANCE
718 /*146*/ ,l_shipments.RECEIVING_ROUTING_ID
719 /*147*/ ,l_shipments.REQUEST_ID
720 /*148*/ ,l_shipments.RETAINAGE_RELEASED_AMOUNT
721 /*149*/ ,l_shipments.RETAINAGE_WITHHELD_AMOUNT
722 /*150*/ ,l_shipments.RETROACTIVE_DATE
723 /*151*/ ,l_shipments.SALES_ORDER_UPDATE_DATE
724 /*152*/ ,l_shipments.SECONDARY_QUANTITY
725 /*153*/ ,l_shipments.SECONDARY_QUANTITY_ACCEPTED
726 /*154*/ ,l_shipments.SECONDARY_QUANTITY_CANCELLED
727 /*155*/ ,l_shipments.SECONDARY_QUANTITY_RECEIVED
728 /*156*/ ,l_shipments.SECONDARY_QUANTITY_REJECTED
729 /*157*/ ,l_shipments.SECONDARY_QUANTITY_SHIPPED
730 /*158*/ ,l_shipments.SECONDARY_UNIT_OF_MEASURE
731 /*159*/ ,l_shipments.SHIP_TO_LOCATION_ID
732 /*160*/ ,l_shipments.SHIP_TO_ORGANIZATION_ID
733 /*161*/ ,l_shipments.SHIP_VIA_LOOKUP_CODE
734 /*162*/ ,l_shipments.SHIPMENT_CLOSED_DATE
735 /*163*/ ,l_shipments.SHIPMENT_NUM
736 /*164*/ ,l_shipments.SHIPMENT_TYPE
737 /*165*/ ,l_shipments.SOURCE_SHIPMENT_ID
738 /*166*/ ,l_shipments.START_DATE
739 /*167*/ ,l_shipments.SUPPLIER_ORDER_LINE_NUMBER
740 /*168*/ ,l_shipments.TAX_CODE_ID
741 /*169*/ ,l_shipments.TAX_NAME
742 /*170*/ ,l_shipments.TAX_USER_OVERRIDE_FLAG
743 /*171*/ ,l_shipments.TAXABLE_FLAG
744 /*172*/ ,l_shipments.TERMS_ID
745 /*173*/ ,l_shipments.TO_DATE
746 /*174*/ ,l_shipments.TRANSACTION_FLOW_HEADER_ID
747 /*175*/ ,l_shipments.UNENCUMBERED_QUANTITY
748 /*176*/ ,l_shipments.UNIT_MEAS_LOOKUP_CODE
749 /*177*/ ,l_shipments.UNIT_OF_MEASURE_CLASS
750 /*178*/ ,l_shipments.USSGL_TRANSACTION_CODE
751 /*179*/ ,l_shipments.VALUE_BASIS
752 /*180*/ ,l_shipments.VMI_FLAG
753 /*181*/ ,l_shipments.WORK_APPROVER_ID;
754
755 close c_changed_shipments;
756
757 log_debug('sync_with_award_process : '||
758 ' Collected changed shipments count='||l_shipments.line_location_id.count);
759
760
761 open c_changed_distributions;
762 fetch c_changed_distributions bulk collect into
763 /*1*/ l_distributions.ACCRUAL_ACCOUNT_ID
764 /*2*/ ,l_distributions.ACCRUE_ON_RECEIPT_FLAG
765 /*3*/ ,l_distributions.ACCRUED_FLAG
766 /*4*/ ,l_distributions.AMOUNT_BILLED
767 /*5*/ ,l_distributions.AMOUNT_CANCELLED
768 /*6*/ ,l_distributions.AMOUNT_DELIVERED
769 /*7*/ ,l_distributions.AMOUNT_FINANCED
770 /*8*/ ,l_distributions.AMOUNT_ORDERED
771 /*9*/ ,l_distributions.AMOUNT_RECOUPED
772 /*10*/ ,l_distributions.AMOUNT_TO_ENCUMBER
773 /*11*/ ,l_distributions.ATTRIBUTE_CATEGORY
774 /*12*/ ,l_distributions.ATTRIBUTE1
775 /*13*/ ,l_distributions.ATTRIBUTE10
776 /*14*/ ,l_distributions.ATTRIBUTE11
777 /*15*/ ,l_distributions.ATTRIBUTE12
778 /*16*/ ,l_distributions.ATTRIBUTE13
779 /*17*/ ,l_distributions.ATTRIBUTE14
780 /*18*/ ,l_distributions.ATTRIBUTE15
781 /*19*/ ,l_distributions.ATTRIBUTE2
782 /*20*/ ,l_distributions.ATTRIBUTE3
783 /*21*/ ,l_distributions.ATTRIBUTE4
784 /*22*/ ,l_distributions.ATTRIBUTE5
785 /*23*/ ,l_distributions.ATTRIBUTE6
786 /*24*/ ,l_distributions.ATTRIBUTE7
787 /*25*/ ,l_distributions.ATTRIBUTE8
788 /*26*/ ,l_distributions.ATTRIBUTE9
789 /*27*/ ,l_distributions.AWARD_ID
790 /*28*/ ,l_distributions.AWARD_NUMBER
791 /*29*/ ,l_distributions.BOM_RESOURCE_ID
792 /*30*/ ,l_distributions.BUDGET_ACCOUNT_ID
793 /*31*/ ,l_distributions.CHANGE_ACCEPTED_FLAG
794 /*32*/ ,l_distributions.CODE_COMBINATION_ID
795 /*33*/ ,l_distributions.CREATED_BY
796 /*34*/ ,l_distributions.CREATION_DATE
797 /*35*/ ,l_distributions.DELETE_FLAG
798 /*36*/ ,l_distributions.DELIVER_TO_LOCATION_ID
799 /*37*/ ,l_distributions.DELIVER_TO_PERSON_ID
800 /*38*/ ,l_distributions.DEST_CHARGE_ACCOUNT_ID
801 /*39*/ ,l_distributions.DEST_VARIANCE_ACCOUNT_ID
802 /*40*/ ,l_distributions.DESTINATION_CONTEXT
803 /*41*/ ,l_distributions.DESTINATION_ORGANIZATION_ID
804 /*42*/ ,l_distributions.DESTINATION_SUBINVENTORY
805 /*43*/ ,l_distributions.DESTINATION_TYPE_CODE
806 /*44*/ ,l_distributions.DISTRIBUTION_NUM
807 /*45*/ ,l_distributions.DISTRIBUTION_TYPE
808 /*46*/ ,l_distributions.DRAFT_ID
809 /*47*/ ,l_distributions.ENCUMBERED_AMOUNT
810 /*48*/ ,l_distributions.ENCUMBERED_FLAG
811 /*49*/ ,l_distributions.END_ITEM_UNIT_NUMBER
812 /*50*/ ,l_distributions.EXPENDITURE_ITEM_DATE
813 /*51*/ ,l_distributions.EXPENDITURE_ORGANIZATION_ID
814 /*52*/ ,l_distributions.EXPENDITURE_TYPE
815 /*53*/ ,l_distributions.EXPENSE_ACCRUAL_CODE
816 /*54*/ ,l_distributions.FAILED_FUNDS_LOOKUP_CODE
817 /*55*/ ,l_distributions.GL_CANCELLED_DATE
818 /*56*/ ,l_distributions.GL_CLOSED_DATE
819 /*57*/ ,l_distributions.GL_ENCUMBERED_DATE
820 /*58*/ ,l_distributions.GL_ENCUMBERED_PERIOD_NAME
821 /*59*/ ,l_distributions.GOVERNMENT_CONTEXT
822 /*60*/ ,l_distributions.HDR_AGENT_ID
823 /*61*/ ,l_distributions.HEADER_NEED_BY_DATE
824 /*62*/ ,l_distributions.INTERFACE_ID
825 /*63*/ ,l_distributions.INVOICE_ADJUSTMENT_FLAG
826 /*64*/ ,l_distributions.KANBAN_CARD_ID
827 /*65*/ ,l_distributions.LAST_UPDATE_DATE
828 /*66*/ ,l_distributions.LAST_UPDATE_LOGIN
829 /*67*/ ,l_distributions.LAST_UPDATED_BY
830 /*68*/ ,l_distributions.LINE_ITEM_ID
831 /*69*/ ,l_distributions.LINE_LINE_TYPE_ID
832 /*70*/ ,l_distributions.LINE_LOCATION_ID
833 /*71*/ ,l_distributions.LINE_ORDER_TYPE_LOOKUP_CODE
834 /*72*/ ,l_distributions.LINE_PURCHASE_BASIS
835 /*73*/ ,l_distributions.LOC_OUTSOURCED_ASSEMBLY
836 /*74*/ ,l_distributions.MRC_ENCUMBERED_AMOUNT
837 /*75*/ ,l_distributions.MRC_RATE
838 /*76*/ ,l_distributions.MRC_RATE_DATE
839 /*77*/ ,l_distributions.MRC_UNENCUMBERED_AMOUNT
840 /*78*/ ,l_distributions.NONRECOVERABLE_TAX
841 /*79*/ ,l_distributions.OKE_CONTRACT_DELIVERABLE_ID
842 /*80*/ ,l_distributions.OKE_CONTRACT_LINE_ID
843 /*81*/ ,l_distributions.ORG_ID
844 /*82*/ ,l_distributions.PO_DISTRIBUTION_ID
845 /*83*/ ,l_distributions.PO_HEADER_ID
846 /*84*/ ,l_distributions.PO_LINE_ID
847 /*85*/ ,l_distributions.PO_RELEASE_ID
848 /*86*/ ,l_distributions.PREVENT_ENCUMBRANCE_FLAG
849 /*87*/ ,l_distributions.PROGRAM_APPLICATION_ID
850 /*88*/ ,l_distributions.PROGRAM_ID
851 /*89*/ ,l_distributions.PROGRAM_UPDATE_DATE
852 /*90*/ ,l_distributions.PROJECT_ACCOUNTING_CONTEXT
853 /*91*/ ,l_distributions.PROJECT_ID
854 /*92*/ ,l_distributions.PROMISED_DATE
855 /*93*/ ,l_distributions.QUANTITY_BILLED
856 /*94*/ ,l_distributions.QUANTITY_CANCELLED
857 /*95*/ ,l_distributions.QUANTITY_DELIVERED
858 /*96*/ ,l_distributions.QUANTITY_FINANCED
859 /*97*/ ,l_distributions.QUANTITY_ORDERED
860 /*98*/ ,l_distributions.QUANTITY_RECOUPED
861 /*99*/ ,l_distributions.RATE
862 /*100*/ ,l_distributions.RATE_DATE
863 /*101*/ ,l_distributions.RECOVERABLE_TAX
864 /*102*/ ,l_distributions.RECOVERY_RATE
865 /*103*/ ,l_distributions.REQ_DISTRIBUTION_ID
866 /*104*/ ,l_distributions.REQ_HEADER_REFERENCE_NUM
867 /*105*/ ,l_distributions.REQ_LINE_REFERENCE_NUM
868 /*106*/ ,l_distributions.REQUEST_ID
869 /*107*/ ,l_distributions.RETAINAGE_RELEASED_AMOUNT
870 /*108*/ ,l_distributions.RETAINAGE_WITHHELD_AMOUNT
871 /*109*/ ,l_distributions.SET_OF_BOOKS_ID
872 /*110*/ ,l_distributions.SHIP_TO_ORGANIZATION_ID
873 /*111*/ ,l_distributions.SHIP_VALUE_BASIS
874 /*112*/ ,l_distributions.SOURCE_DISTRIBUTION_ID
875 /*113*/ ,l_distributions.TASK_ID
876 /*114*/ ,l_distributions.TAX_RECOVERY_OVERRIDE_FLAG
877 /*115*/ ,l_distributions.TRANSACTION_FLOW_HEADER_ID
878 /*116*/ ,l_distributions.UNENCUMBERED_AMOUNT
879 /*117*/ ,l_distributions.UNENCUMBERED_QUANTITY
880 /*118*/ ,l_distributions.USSGL_TRANSACTION_CODE
881 /*119*/ ,l_distributions.VARIANCE_ACCOUNT_ID
882 /*120*/ ,l_distributions.WIP_ENTITY_ID
883 /*121*/ ,l_distributions.WIP_LINE_ID
884 /*122*/ ,l_distributions.WIP_OPERATION_SEQ_NUM
885 /*123*/ ,l_distributions.WIP_REPETITIVE_SCHEDULE_ID
886 /*124*/ ,l_distributions.WIP_RESOURCE_SEQ_NUM;
887 close c_changed_distributions;
888
889 log_debug('sync_with_award_process : '||
890 ' Collected changed distributions; count='||
891 l_distributions.po_distribution_id.count);
892 if l_distributions.po_distribution_id.count > 0 or
893 l_shipments.line_location_id.count > 0 then
894 po_validations.validate_modification_sync(
895 p_headers => l_headers
896 , p_lines => l_lines
897 , p_line_locations=> l_shipments
898 , p_distributions => l_distributions
899 , p_price_differentials => l_price_diff
900 , p_price_adjustments => l_price_adjs
901 , x_result_type => l_result_type
902 , x_results => l_results);
903 log_debug('sync_with_award_process : '||
904 'Validations result: '||l_result_type);
905
906 IF l_result_type <> po_validations.c_result_type_success THEN
907 x_return_status := FND_API.G_RET_STS_ERROR;
908 get_errors(l_results, x_results);
909 if p_reject_if_error = 'Y' then
910 system_reject_mod(p_draft_id, x_return_status);
911 if x_return_status = FND_API.G_RET_STS_SUCCESS
912 then
913 -- reseting the status to original
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 end if;
916 end if;
917 return;
918 END IF;
919 end if;
920
921 log_debug('sync_with_award_process : '||
922 'Updating shipments ');
923 --update
924 FORALL I in l_shipments.line_location_id.first
925 ..l_shipments.line_location_id.last
926 UPDATE po_line_locations_draft_all
927 set quantity_received = l_shipments.quantity_received(I),
928 quantity_billed = l_shipments.quantity_billed(I),
929 amount_received = l_shipments.amount_received(I),
930 amount_billed = l_shipments.amount_billed(I)
931 where draft_id = p_draft_id
932 and line_location_id = l_shipments.line_location_id(I);
933 log_debug('sync_with_award_process : '||
934 'Updated '||sql%rowcount||' no of shipments');
935
936 FORALL I in l_distributions.po_distribution_id.first
937 ..l_distributions.po_distribution_id.last
938 UPDATE po_distributions_draft_all
939 set quantity_billed = l_distributions.quantity_billed(I),
940 amount_billed = l_distributions.amount_billed(I)
941 where draft_id = p_draft_id
942 and po_distribution_id = l_distributions.po_distribution_id(I);
943 log_debug('sync_with_award_process : '||
944 'Updated '||sql%rowcount||' no of distributions');
945
946 end if;
947
948 EXCEPTION
949 when others then
950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
951 log_debug('sync_with_award_process : '||
952 'At exception'||substr(sqlerrm,1200));
953 END;
954
955
956 --------------------------------------------------------------------------------
957 --Start of Comments
958 --Name: sync_with_award_process
959 -- <Conc Mods Project>
960 --Pre-reqs:
961 -- none
962 --Modifies: None
963 --Function:
964 -- This procedure syncs the all entities of Mod with the corresponding base entites
965 --Parameters:
966 --IN:
967 --p_draft_id
968 -- draft_id of the Modification
969 --IN OUT:
970 --OUT:
971 --x_return_status
972 -- return status of the procedure
973 --Notes:
974 --End of Comments
975 --------------------------------------------------------------------------------
976
977
978 PROCEDURE sync_mod_updatable_attrs(
979 p_draft_id IN NUMBER
980 ,x_return_status OUT NOCOPY VARCHAR2
981 ) IS
982
983 l_address_type VARCHAR2(400);
984 d_api_name CONSTANT VARCHAR2(100) := 'sync_with_award_process';
985 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
986 d_position NUMBER;
987 d_api_version CONSTANT NUMBER := 1.0;
988 l_po_header_id NUMBER;
989 l_document_type_code VARCHAR2(500);
990 l_document_style_id NUMBER;
991 l_enabled_flag VARCHAR2(5);
992 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
993 BEGIN
994
995 d_position := 0;
996 IF (PO_LOG.d_proc) THEN
997 PO_LOG.proc_begin(d_module);
998 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
999 END IF;
1000
1001 --chk if uda enabled
1002 SELECT document_id
1003 INTO l_po_header_id
1004 FROM po_drafts
1005 WHERE draft_id = p_draft_id;
1006
1007 SELECT TYPE_LOOKUP_CODE, STYLE_ID
1008 INTO l_document_type_code, l_document_style_id
1009 FROM po_headers_all
1010 WHERE PO_HEADER_ID = l_po_header_id;
1011
1012 d_position := 70;
1013 IF PO_LOG.d_stmt THEN
1014 PO_LOG.stmt(d_module,d_position,'Before Check_Uda_Enabled');
1015 END IF;
1016
1017 PO_CLM_CLO_UTIL.Check_po_Uda_Enabled
1018 (
1019 p_document_type_code => l_document_type_code
1020 ,p_document_style_id => l_document_style_id
1021 ,x_enabled_flag => l_enabled_flag
1022 ,x_return_status => l_return_status
1023 );
1024
1025 d_position := 80;
1026 IF PO_LOG.d_stmt THEN
1027 PO_LOG.stmt(d_module,d_position,'After Check_Uda_Enabled with x_return_status : ' || l_return_status);
1028 END IF;
1029
1030 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1032 END IF;
1033
1034 -- update header
1035 sync_headers( p_draft_id => p_draft_id
1036 ,p_uda_enabled_flag => l_enabled_flag
1037 ,x_return_status => x_return_status
1038 );
1039 d_position := 20;
1040 IF (PO_LOG.d_stmt) THEN
1041 PO_LOG.stmt(d_module,d_position ,'Sync_headers successful');
1042 END IF;
1043
1044 -- update lines
1045 sync_lines(p_draft_id => p_draft_id
1046 ,p_uda_enabled_flag => l_enabled_flag
1047 ,x_return_status => x_return_status
1048 );
1049
1050 d_position := 40;
1051 IF (PO_LOG.d_stmt) THEN
1052 PO_LOG.stmt(d_module,d_position ,'sync_lines successful');
1053 END IF;
1054
1055 --update shipments
1056 sync_shipments(p_draft_id => p_draft_id
1057 ,p_uda_enabled_flag => l_enabled_flag
1058 ,x_return_status => x_return_status
1059 );
1060
1061 d_position := 60;
1062 IF (PO_LOG.d_stmt) THEN
1063 PO_LOG.stmt(d_module,d_position ,'sync_shipments successful');
1064 END IF;
1065
1066 -- update distributions
1067 sync_distributions(
1068 p_draft_id => p_draft_id
1069 ,x_return_status => x_return_status
1070 );
1071
1072 d_position := 60;
1073 IF (PO_LOG.d_stmt) THEN
1074 PO_LOG.stmt(d_module,d_position ,'sync_distributions successful');
1075 END IF;
1076
1077 -- Update the revision_num on po_drafts table.
1078 -- This can be updated to -1 from update without Mod flow.
1079 UPDATE po_drafts
1080 SET revision_num = NULL
1081 WHERE draft_id = p_draft_id
1082 AND revision_num = -1;
1083
1084 d_position := 80;
1085 IF (PO_LOG.d_proc) THEN
1086 PO_LOG.proc_end(d_module);
1087 END IF;
1088 x_return_status := FND_API.G_RET_STS_SUCCESS;
1089
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 IF (PO_LOG.d_exc) THEN
1094 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
1095 END IF;
1096 RAISE;
1097 END sync_mod_updatable_attrs;
1098 --END
1099
1100 --------------------------------------------------------------------------------
1101 --Start of Comments
1102 --Name: sync_headers
1103 -- <Conc Mods Project>
1104 --Pre-reqs:
1105 -- none
1106 --Modifies: None
1107 --Function:
1108 -- This procedure syncs the all Header entities of Mod with the corresponding
1109 -- Base Header entites
1110 --Parameters:
1111 --IN:
1112 --p_draft_id
1113 -- draft_id of the Modification
1114 --IN OUT:
1115 --OUT:
1116 --Notes:
1117 --End of Comments
1118 --------------------------------------------------------------------------------
1119
1120 PROCEDURE sync_headers(
1121 p_draft_id IN NUMBER,
1122 p_uda_enabled_flag VARCHAR2,
1123 p_ignore_rev_number IN VARCHAR DEFAULT 'N',
1124 x_return_status OUT NOCOPY VARCHAR2
1125 ) IS
1126 l_po_header_id NUMBER;
1127 l_header_draft_rev_num NUMBER;
1128 l_header_base_rev_num NUMBER;
1129 l_base_amount_limit NUMBER;
1130 l_draft_amount_limit NUMBER;
1131 l_draft_old_amount_limit NUMBER;
1132 l_draft_rev_num NUMBER;
1133 l_lock_type VARCHAR2(1);
1134 d_api_name CONSTANT VARCHAR2(30) := 'sync_headers';
1135 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1136 d_position NUMBER;
1137 d_api_version CONSTANT NUMBER := 1.0;
1138 l_document_type_code VARCHAR2(500);
1139 l_document_style_id NUMBER;
1140 l_enabled_flag VARCHAR2(5);
1141 l_return_status VARCHAR2(1);
1142 l_delete_flag VARCHAR2(1);
1143 l_record_already_exists VARCHAR2(1);
1144 l_org_assignment_id_tbl PO_TBL_NUMBER;
1145 l_notification_id_tbl PO_TBL_NUMBER;
1146 l_draft_id_tbl PO_TBL_NUMBER;
1147 l_delete_flag_tbl PO_TBL_VARCHAR1;
1148 l_record_already_exist_tbl PO_TBL_VARCHAR1;
1149 l_change_status VARCHAR2(30);
1150 l_change_status_tbl PO_TBL_VARCHAR30;-- used po_ga_org_assign_draft,po_notification_ctrl_draft
1151 BEGIN
1152
1153 l_org_assignment_id_tbl:= Po_Tbl_Number();
1154 l_notification_id_tbl:= Po_Tbl_Number();
1155 l_draft_id_tbl :=po_tbl_number();
1156 l_delete_flag_tbl:=Po_Tbl_Varchar1();
1157 l_record_already_exist_tbl:=Po_Tbl_Varchar1();
1158 l_change_status_tbl :=PO_TBL_VARCHAR30();
1159 d_position := 0;
1160 IF (PO_LOG.d_proc) THEN
1161 PO_LOG.proc_begin(d_module);
1162 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
1163 END IF;
1164
1165
1166 SELECT document_id
1167 INTO l_po_header_id
1168 FROM po_drafts
1169 WHERE draft_id = p_draft_id;
1170
1171 d_position := 10;
1172 IF (PO_LOG.d_stmt) THEN
1173 PO_LOG.stmt(d_module,d_position ,'Document Header ID', l_po_header_id);
1174 END IF;
1175
1176 /*amount limit and old amount limit are the concurrent update attributes for header*/
1177 SELECT phd.revision_num,
1178 pha.revision_num,
1179 pha.amount_limit,
1180 phd.amount_limit,
1181 phd.old_amount_limit,
1182 phd.change_status
1183 INTO l_header_draft_rev_num,
1184 l_header_base_rev_num,
1185 l_base_amount_limit,
1186 l_draft_amount_limit,
1187 l_draft_old_amount_limit,
1188 l_change_status
1189 FROM po_headers_all pha
1190 ,po_headers_draft_all phd
1191 WHERE pha.po_header_id = phd.po_header_id
1192 AND phd.draft_id = p_draft_id
1193 AND pha.po_header_id = l_po_header_id;
1194
1195 d_position := 20;
1196 IF (PO_LOG.d_stmt) THEN
1197 PO_LOG.stmt(d_module,d_position ,'Draft Header Revision Num', l_header_draft_rev_num);
1198 PO_LOG.stmt(d_module,d_position ,'Base Header Revision Num', l_header_base_rev_num);
1199 PO_LOG.stmt(d_module,d_position ,'Base Amount Limit', l_base_amount_limit);
1200 PO_LOG.stmt(d_module,d_position ,'Draft Amount Limit', l_draft_amount_limit);
1201 PO_LOG.stmt(d_module,d_position ,'Draft Old Amount Limit', l_draft_old_amount_limit);
1202 END IF;
1203
1204 /* fetch the rev_num */
1205 SELECT revision_num
1206 INTO l_draft_rev_num
1207 FROM po_drafts
1208 WHERE draft_id = p_draft_id;
1209
1210 /*fetch the lock_type */
1211 BEGIN
1212 SELECT lock_type
1213 INTO l_lock_type
1214 FROM po_entity_locks
1215 WHERE lock_by_draft_id = p_draft_id
1216 AND entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
1217 AND entity_pk1 = l_po_header_id;
1218 EXCEPTION
1219 WHEN NO_DATA_FOUND THEN
1220 l_lock_type := NULL;
1221 END;
1222
1223 d_position := 20;
1224 IF (PO_LOG.d_stmt) THEN
1225 PO_LOG.stmt(d_module,d_position ,'PODraft revision Number', l_draft_rev_num);
1226 PO_LOG.stmt(d_module,d_position ,'lock_type', l_lock_type);
1227 END IF;
1228
1229 /*if the draft revision num is less than base revision num then a sync is required*/
1230 IF( p_ignore_rev_number = 'Y' OR (l_header_draft_rev_num < l_header_base_rev_num OR l_draft_rev_num = -1)) THEN
1231 IF( p_ignore_rev_number = 'Y' OR ( l_lock_type = 'S' OR l_lock_type IS NULL)) THEN
1232 UPDATE po_headers_draft_all pohd
1233 SET ( pohd.amount_limit ,
1234 pohd.user_document_status,
1235 pohd.vendor_site_id,
1236 pohd.vendor_contact_id,
1237 pohd.supplier_notif_method,
1238 pohd.fax,
1239 pohd.email_address,
1240 pohd.clm_external_idv,
1241 pohd.clm_vendor_offer_number,
1242 pohd.clm_effective_date,
1243 pohd.agent_id,
1244 pohd.clm_award_administrator,
1245 pohd.comments,
1246 pohd.confirming_order_flag,
1247 pohd.terms_id,
1248 pohd.ship_to_location_id,
1249 pohd.bill_to_location_id,
1250 pohd.ship_via_lookup_code,
1251 pohd.freight_terms_lookup_code,
1252 pohd.fob_lookup_code,
1253 pohd.shipping_control,
1254 pohd.note_to_vendor,
1255 pohd.note_to_receiver,
1256 pohd.blanket_total_amount,
1257 pohd.price_update_tolerance,
1258 pohd.start_date,
1259 pohd.end_date,
1260 pohd.clm_max_order_amount,
1261 pohd.clm_min_order_amount,
1262 pohd.clm_min_guarantee_award_amt,
1263 pohd.clm_min_guar_award_amt_percent,
1264 pohd.auto_sourcing_flag,
1265 pohd.update_sourcing_rules_flag,
1266 pohd.retro_price_apply_updates_flag,
1267 pohd.retro_price_comm_updates_flag,
1268 pohd.attribute_category ,
1269 pohd.attribute1 ,
1270 pohd.attribute2 ,
1271 pohd.attribute3 ,
1272 pohd.attribute4 ,
1273 pohd.attribute5 ,
1274 pohd.attribute6 ,
1275 pohd.attribute7 ,
1276 pohd.attribute8 ,
1277 pohd.attribute9 ,
1278 pohd.attribute10 ,
1279 pohd.attribute11 ,
1280 pohd.attribute12 ,
1281 pohd.attribute13 ,
1282 pohd.attribute14 ,
1283 pohd.attribute15,
1284 pohd.umbrella_program_id,
1285 pohd.revision_num,
1286 pohd.clm_payment_instr_code) -- Added for reverting PI in headers
1287
1288 = ( SELECT poh.amount_limit,
1289 poh.user_document_status,
1290 poh.vendor_site_id,
1291 poh.vendor_contact_id,
1292 poh.supplier_notif_method,
1293 poh.fax,
1294 poh.email_address,
1295 poh.clm_external_idv,
1296 poh.clm_vendor_offer_number,
1297 poh.clm_effective_date,
1298 poh.agent_id,
1299 poh.clm_award_administrator,
1300 poh.comments,
1301 poh.confirming_order_flag,
1302 poh.terms_id,
1303 poh.ship_to_location_id,
1304 poh.bill_to_location_id,
1305 poh.ship_via_lookup_code,
1306 poh.freight_terms_lookup_code,
1307 poh.fob_lookup_code,
1308 poh.shipping_control,
1309 poh.note_to_vendor,
1310 poh.note_to_receiver,
1311 poh.blanket_total_amount,
1312 poh.price_update_tolerance,
1313 poh.start_date,
1314 poh.end_date,
1315 poh.clm_max_order_amount,
1316 poh.clm_min_order_amount,
1317 poh.clm_min_guarantee_award_amt,
1318 poh.clm_min_guar_award_amt_percent,
1319 poh.auto_sourcing_flag,
1320 poh.update_sourcing_rules_flag,
1321 poh.retro_price_apply_updates_flag,
1322 poh.retro_price_comm_updates_flag,
1323 poh.attribute_category ,
1324 poh.attribute1 ,
1325 poh.attribute2 ,
1326 poh.attribute3 ,
1327 poh.attribute4 ,
1328 poh.attribute5 ,
1329 poh.attribute6 ,
1330 poh.attribute7 ,
1331 poh.attribute8 ,
1332 poh.attribute9 ,
1333 poh.attribute10 ,
1334 poh.attribute11 ,
1335 poh.attribute12 ,
1336 poh.attribute13 ,
1337 poh.attribute14 ,
1338 poh.attribute15 ,
1339 poh.umbrella_program_id,
1340 poh.revision_num,
1341 poh.clm_payment_instr_code -- Added for reverting PI in headers
1342 FROM po_headers_all poh
1343 WHERE poh.po_header_id = l_po_header_id)
1344 WHERE pohd.po_header_id=l_po_header_id
1345 AND pohd.draft_id = p_draft_id;
1346
1347 d_position := 40;
1348 IF (PO_LOG.d_stmt) THEN
1349 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
1350 PO_LOG.stmt(d_module,d_position ,'synced header for S or No locks');
1351 END IF;
1352
1353 IF p_uda_enabled_flag='Y' THEN
1354 sync_headers_ext(
1355 p_draft_id => p_draft_id
1356 ,x_return_status => x_return_status
1357 );
1358 END IF;
1359
1360 d_position := 50;
1361 IF (PO_LOG.d_stmt) THEN
1362 PO_LOG.stmt(d_module,d_position ,'synced Header_ext tables');
1363 END IF;
1364
1365 /* Sync records coresponding to po_ga_org_assignments */
1366 --delete the recored from po_ga_org_assign_draft for the given header_id and draft_id
1367 -- and then insert the data from the base table
1368 SELECT org_assignment_id,
1369 p_draft_id,
1370 'N',
1371 change_status
1372 BULK COLLECT INTO l_org_assignment_id_tbl,
1373 l_draft_id_tbl,
1374 l_delete_flag_tbl,
1375 l_change_status_tbl
1376 FROM po_ga_org_assign_draft
1377 WHERE po_header_id=l_po_header_id
1378 AND draft_id = p_draft_id ;
1379
1380 FORALL i in 1 ..l_org_assignment_id_tbl.COUNT
1381 DELETE FROM po_ga_org_assign_draft pgoad
1382 WHERE pgoad.org_assignment_id = l_org_assignment_id_tbl(i)
1383 AND pgoad.draft_id = p_draft_id ;
1384
1385 PO_GA_ORG_ASSIGN_DRAFT_PKG.sync_draft_from_txn
1386 ( p_org_assignment_id_tbl => l_org_assignment_id_tbl,
1387 p_draft_id_tbl => l_draft_id_tbl,
1388 p_delete_flag_tbl => l_delete_flag_tbl,
1389 x_record_already_exist_tbl => l_record_already_exist_tbl
1390 );
1391
1392 FORALL i in 1 ..l_org_assignment_id_tbl.COUNT
1393 UPDATE po_ga_org_assign_draft pgoad
1394 SET pgoad.change_status = l_change_status_tbl(i)
1395 WHERE pgoad.org_assignment_id = l_org_assignment_id_tbl(i)
1396 AND pgoad.draft_id = p_draft_id ;
1397
1398 d_position := 60;
1399 IF (PO_LOG.d_stmt) THEN
1400 PO_LOG.stmt(d_module,d_position ,'PO_GA_ORG_ASSIGN_DRAFT_PKG.sync_draft_from_txn');
1401 END IF;
1402
1403 /* Sync records coresponding to po_notification_controls */
1404 --delete the recored from po_notification_ctrl_draft for the given header_id and draft_id
1405 -- and then insert the data from the base table
1406 l_draft_id_tbl.DELETE;
1407 l_delete_flag_tbl.DELETE;
1408 l_change_status_tbl.DELETE;
1409
1410 SELECT notification_id,
1411 p_draft_id,
1412 'N',
1413 change_status
1414 BULK COLLECT INTO l_notification_id_tbl,
1415 l_draft_id_tbl,
1416 l_delete_flag_tbl,
1417 l_change_status_tbl
1418 FROM po_notification_ctrl_draft
1419 WHERE po_header_id=l_po_header_id
1420 AND draft_id = p_draft_id ;
1421
1422 FORALL i in 1 .. l_notification_id_tbl.COUNT
1423 DELETE FROM po_notification_ctrl_draft pncd
1424 WHERE pncd.notification_id=l_notification_id_tbl(i)
1425 AND pncd.draft_id = p_draft_id ;
1426
1427
1428 PO_NOTIFICATION_CTRL_DRAFT_PKG.sync_draft_from_txn
1429 ( p_notification_id_tbl => l_notification_id_tbl,
1430 p_draft_id_tbl => l_draft_id_tbl,
1431 p_delete_flag_tbl => l_delete_flag_tbl,
1432 x_record_already_exist_tbl => l_record_already_exist_tbl
1433 );
1434
1435 FORALL i in 1 .. l_notification_id_tbl.COUNT
1436 UPDATE po_notification_ctrl_draft pncd
1437 SET pncd.change_status = l_change_status_tbl(i)
1438 WHERE pncd.notification_id=l_notification_id_tbl(i)
1439 AND pncd.draft_id = p_draft_id ;
1440
1441 d_position := 70;
1442 IF (PO_LOG.d_stmt) THEN
1443 PO_LOG.stmt(d_module,d_position ,'PO_NOTIFICATION_CTRL_DRAFT_PKG.sync_draft_from_txn');
1444 END IF;
1445
1446 END IF; --IF( p_ignore_rev_number = 'Y' OR ( l_lock_type = 'S' OR l_lock_type IS NULL))
1447
1448 /*we need to update the delta values for the concurrent update attributes in case of both shared and partial lock*/
1449 IF l_lock_type IN ('S','P') THEN
1450 -- Update only if either of base/drat amount limit is not null
1451 IF l_base_amount_limit IS NOT NULL OR l_draft_amount_limit IS NOT NULL THEN
1452 UPDATE po_headers_draft_all
1453 SET old_amount_limit = l_base_amount_limit,
1454 amount_limit = NVL(l_base_amount_limit, 0) +
1455 ( NVL(l_draft_amount_limit, 0) -
1456 NVL(l_draft_old_amount_limit, 0) )
1457 WHERE po_header_id = l_po_header_id
1458 AND draft_id = p_draft_id;
1459 END IF;
1460 END IF;--IF l_lock_type IN ('S','P')
1461
1462 d_position := 80;
1463 IF (PO_LOG.d_stmt) THEN
1464
1465 PO_LOG.stmt(d_module,d_position ,'updated old_amount_limit', l_base_amount_limit);
1466 PO_LOG.stmt(d_module,d_position ,'updated amount_limit', l_base_amount_limit +
1467 (l_draft_amount_limit-l_draft_old_amount_limit));
1468 END IF;
1469
1470 IF p_uda_enabled_flag='Y'
1471 AND ( (p_ignore_rev_number ='Y') OR (l_header_draft_rev_num < l_header_base_rev_num OR l_draft_rev_num = -1))
1472 THEN
1473 sync_uda_address(
1474 p_draft_id => p_draft_id
1475 ,p_ignore_rev_number => p_ignore_rev_number
1476 ,x_return_status => x_return_status
1477 );
1478 END IF;
1479
1480 d_position := 90;
1481 IF (PO_LOG.d_stmt) THEN
1482 PO_LOG.stmt(d_module,d_position ,'synced uda_address tables');
1483 END IF;
1484
1485 /*we need to update revision_num , change_status*/
1486 UPDATE po_headers_draft_all
1487 SET revision_num = l_header_base_rev_num,
1488 change_status = l_change_status
1489 WHERE po_header_id = l_po_header_id
1490 AND draft_id = p_draft_id;
1491
1492 d_position := 80;
1493 IF (PO_LOG.d_stmt) THEN
1494 PO_LOG.stmt(d_module,d_position ,'updated revision_num', l_header_base_rev_num);
1495 END IF;
1496
1497 END IF;--IF( p_ignore_rev_number = 'Y' OR (l_header_draft_rev_num < l_header_base_rev_num OR l_draft_rev_num = -1))
1498
1499 d_position := 100;
1500 IF (PO_LOG.d_proc) THEN
1501 PO_LOG.proc_end(d_module);
1502 END IF;
1503
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1507 IF (PO_LOG.d_exc) THEN
1508 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
1509 END IF;
1510 RAISE;
1511 END sync_headers;
1512
1513 --------------------------------------------------------------------------------
1514 --Start of Comments
1515 --Name: sync_lines
1516 -- <Conc Mods Project>
1517 --Pre-reqs:
1518 -- none
1519 --Modifies: None
1520 --Function:
1521 -- This procedure syncs Mod line which are out of sync with the corresponding
1522 -- Base line record, i.e. when rev num of base line > rev num of Mod line
1523 -- Logic for each type of lock:
1524 -- No Lock: Delete the draft row and copy row back from base
1525 -- S Lock: Delete the draft row, copy the row back from base and sync S lock attrs
1526 -- P Lock: Sync S lock attrs
1527 -- F Lock: No action required
1528 --Parameters:
1529 --IN:
1530 --p_draft_id
1531 -- draft_id of the Modification
1532 --IN OUT:
1533 --OUT:
1534 --Notes:
1535 --End of Comments
1536 --------------------------------------------------------------------------------
1537 PROCEDURE sync_lines
1538 ( p_draft_id IN NUMBER,
1539 p_uda_enabled_flag IN VARCHAR2
1540 ,x_return_status OUT NOCOPY VARCHAR2
1541 ) IS
1542
1543 l_base_quantity_tbl PO_TBL_NUMBER;
1544 l_base_amount_tbl PO_TBL_NUMBER;
1545 l_draft_quantity_tbl PO_TBL_NUMBER;
1546 l_draft_old_quantity_tbl PO_TBL_NUMBER;
1547 l_draft_amount_tbl PO_TBL_NUMBER;
1548 l_draft_old_amount_tbl PO_TBL_NUMBER;
1549 l_po_line_id_tbl PO_TBL_NUMBER;
1550 l_po_line_s_no_lck_tbl PO_TBL_NUMBER;
1551 l_draft_id_tbl PO_TBL_NUMBER;
1552 l_delete_flag_tbl PO_TBL_VARCHAR1;
1553 l_record_already_exists PO_TBL_VARCHAR1;
1554 l_header_base_rev_num PO_TBL_NUMBER;
1555 d_api_name CONSTANT VARCHAR2(30) := 'sync_lines';
1556 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1557 d_position NUMBER;
1558 d_api_version CONSTANT NUMBER := 1.0;
1559 l_attribute_values_id_tbl PO_TBL_NUMBER;
1560 l_attribute_values_tlp_id_tbl PO_TBL_NUMBER;
1561 l_price_differential_id_tbl PO_TBL_NUMBER;
1562 l_change_status_lines_tbl Po_Tbl_Varchar30; -- chnage_status of po_lines_draft_all
1563 l_change_status_tbl Po_Tbl_Varchar30; -- change_status of po_attribute_values_draft/po_attribute_values_tlp_draft/po_price_diff_draft
1564 --UCA Project - CLM R4 Changes Begin.
1565 l_base_CLM_UNDEF_FLAG_tbl Po_Tbl_varchar1;
1566 l_base_CLM_APP_UNDEF_AMT_tbl Po_tbl_number;
1567 l_draft_CLM_UNDEF_ACTION_CODE po_tbl_varchar30;
1568 l_isUcaEnabled varchar2(1);
1569 --UCA Project - CLM R4 Changes End
1570 l_draft_CLM_UNDEF_FLAG_tbl Po_Tbl_varchar1; --bug 16451273
1571 l_po_line_s_no_p_lck_tbl PO_TBL_NUMBER; --<Bug 16671702>
1572 BEGIN
1573 l_base_quantity_tbl:=po_tbl_number();
1574 l_base_amount_tbl:=po_tbl_number();
1575 l_draft_quantity_tbl:=po_tbl_number();
1576 l_draft_old_quantity_tbl:=po_tbl_number();
1577 l_draft_amount_tbl:=po_tbl_number();
1578 l_draft_old_amount_tbl:=po_tbl_number();
1579 l_po_line_id_tbl:=po_tbl_number();
1580 l_po_line_s_no_lck_tbl:=po_tbl_number();
1581 l_draft_id_tbl :=po_tbl_number();
1582 l_delete_flag_tbl:=Po_Tbl_Varchar1();
1583 l_record_already_exists:=Po_Tbl_Varchar1();
1584 l_header_base_rev_num:=po_tbl_number();
1585 l_attribute_values_id_tbl := po_tbl_number();
1586 l_attribute_values_tlp_id_tbl := po_tbl_number();
1587 l_price_differential_id_tbl := po_tbl_number();
1588 l_change_status_tbl :=Po_Tbl_Varchar30();
1589 l_change_status_lines_tbl:=Po_Tbl_Varchar30();
1590 --UCA Project - CLM R4 Changes Begin.
1591 l_base_CLM_UNDEF_FLAG_tbl := Po_Tbl_varchar1();
1592 l_base_CLM_APP_UNDEF_AMT_tbl := Po_tbl_number();
1593 l_draft_CLM_UNDEF_ACTION_CODE := po_tbl_varchar30();
1594 --UCA Project - CLM R4 Changes End
1595 l_draft_CLM_UNDEF_FLAG_tbl := Po_Tbl_varchar1(); --bug 16451273
1596 l_po_line_s_no_p_lck_tbl := po_tbl_number(); --<Bug 16671702>
1597
1598 d_position := 0;
1599 IF (PO_LOG.d_proc) THEN
1600 PO_LOG.proc_begin(d_module);
1601 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
1602 END IF;
1603
1604 -- 1. Identify lines which P, S or no lock which needs to be synced
1605 -- Collect quantity and amount for all such lines
1606 -- UCA changes to collect CLM_UNDEF_FLAG,CLM_APPROVED_UNDEF_AMOUNT,
1607 -- and CLM_UNDEF_ACTION_CODE
1608 SELECT pla.revision_num,
1609 pla.quantity,
1610 pla.amount,
1611 plda.quantity,
1612 plda.old_quantity ,
1613 plda.amount,
1614 plda.old_amount,
1615 plda.po_line_id,
1616 plda.CHANGE_STATUS,
1617 pla.CLM_UNDEF_FLAG,--UCA Project
1618 pla.CLM_APPROVED_UNDEF_AMOUNT,--UCA Project
1619 plda.CLM_UNDEF_ACTION_CODE, --UCA Project
1620 plda.CLM_UNDEF_FLAG --bug 16451273
1621
1622 BULK COLLECT INTO l_header_base_rev_num,
1623 l_base_quantity_tbl,
1624 l_base_amount_tbl,
1625 l_draft_quantity_tbl,
1626 l_draft_old_quantity_tbl,
1627 l_draft_amount_tbl,
1628 l_draft_old_amount_tbl,
1629 l_po_line_id_tbl,
1630 l_change_status_lines_tbl,
1631 l_base_CLM_UNDEF_FLAG_tbl, --UCA Project
1632 l_base_CLM_APP_UNDEF_AMT_tbl,--UCA Project
1633 l_draft_CLM_UNDEF_ACTION_CODE,--UCA Project
1634 l_draft_CLM_UNDEF_FLAG_tbl --bug 16451273
1635 FROM po_lines_all pla,
1636 po_lines_draft_all plda
1637 WHERE pla.po_line_id = plda.po_line_id
1638 AND plda.draft_id = p_draft_id
1639 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
1640 AND NOT EXISTS (SELECT 'F locks'
1641 FROM po_entity_locks pel
1642 WHERE pel.entity_pk1 = plda.po_line_id
1643 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
1644 AND pel.lock_by_draft_id = plda.draft_id
1645 AND pel.lock_type = 'F');
1646
1647 d_position := 20;
1648 IF (PO_LOG.d_stmt) THEN
1649 PO_LOG.stmt(d_module,d_position ,'Base Header Revision Num', l_header_base_rev_num);
1650 PO_LOG.stmt(d_module,d_position ,'Base Quantity', l_base_quantity_tbl);
1651 PO_LOG.stmt(d_module,d_position ,'Base Amount', l_base_amount_tbl);
1652 PO_LOG.stmt(d_module,d_position ,'Draft Qunatity Limit', l_draft_quantity_tbl);
1653 PO_LOG.stmt(d_module,d_position ,'Draft Old Quantity Limit', l_draft_old_quantity_tbl);
1654 PO_LOG.stmt(d_module,d_position ,'Draft Amount Limit', l_draft_amount_tbl);
1655 PO_LOG.stmt(d_module,d_position ,'Draft Old Amount Limit', l_po_line_id_tbl);
1656 PO_LOG.stmt(d_module,d_position ,'po_line_id', l_po_line_id_tbl);
1657 END IF;
1658
1659 -- 2. Identify lines with S lock or no lock which needs to be synced
1660 SELECT plda.po_line_id,
1661 plda.draft_id,
1662 'N'
1663 BULK COLLECT INTO l_po_line_s_no_lck_tbl,
1664 l_draft_id_tbl,
1665 l_delete_flag_tbl
1666 FROM po_lines_all pla,
1667 po_lines_draft_all plda
1668 WHERE plda.draft_id = p_draft_id
1669 AND pla.po_line_id = plda.po_line_id
1670 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
1671 AND NOT EXISTS (SELECT 'F/P locks'
1672 FROM po_entity_locks pel
1673 WHERE pel.entity_pk1 = plda.po_line_id
1674 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
1675 AND pel.lock_by_draft_id = plda.draft_id
1676 AND pel.lock_type IN ('F', 'P'));
1677 d_position := 40;
1678 IF (PO_LOG.d_stmt) THEN
1679 PO_LOG.stmt(d_module,d_position ,'po_line_id for S and No locks', l_po_line_s_no_lck_tbl);
1680 PO_LOG.stmt(d_module,d_position ,'Draft Id Tbl', l_draft_id_tbl);
1681 PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
1682 END IF;
1683
1684 --<Bug 16671702> : Identify po_line_id for S, No and P locks including comlex pricing attr
1685 SELECT plda.po_line_id
1686 BULK COLLECT
1687 INTO l_po_line_s_no_p_lck_tbl
1688 FROM po_lines_draft_all plda,
1689 po_lines_all pla
1690 WHERE plda.draft_id = p_draft_id
1691 AND pla.po_line_id = plda.po_line_id
1692 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
1693 AND NOT EXISTS (SELECT 'F locks'
1694 FROM po_entity_locks pel
1695 WHERE pel.entity_pk1 = plda.po_line_id
1696 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
1697 AND pel.lock_by_draft_id = plda.draft_id
1698 AND pel.lock_type ='F');
1699
1700 -- 3. Delete lines identified in #2 above.
1701 FORALL i in 1..l_po_line_s_no_lck_tbl.COUNT
1702 DELETE FROM po_lines_draft_all plda
1703 WHERE plda.po_line_id = l_po_line_s_no_lck_tbl(i)
1704 AND plda.draft_id=p_draft_id;
1705
1706 d_position := 50;
1707 IF (PO_LOG.d_stmt) THEN
1708 PO_LOG.stmt(d_module,d_position ,'Deleted po_line_ids for S and No locks',SQL%ROWCOUNT);
1709 END IF;
1710
1711 -- 4. Copy the delted lines in #3 from the base document
1712 PO_LINES_DRAFT_PKG.sync_draft_from_txn
1713 ( p_po_line_id_tbl => l_po_line_s_no_lck_tbl,
1714 p_draft_id_tbl => l_draft_id_tbl,
1715 p_delete_flag_tbl => l_delete_flag_tbl,
1716 x_record_already_exist_tbl => l_record_already_exists
1717 );
1718
1719 d_position := 50;
1720 IF (PO_LOG.d_stmt) THEN
1721 PO_LOG.stmt(d_module,d_position ,'sync_draft_from_txn');
1722 END IF;
1723
1724 -- 5. Sync quantity and amount using the values fetched in #1 above
1725 FORALL i in 1.. l_po_line_id_tbl.COUNT
1726 UPDATE po_lines_draft_all
1727 SET old_quantity = l_base_quantity_tbl(i)
1728 ,quantity = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
1729 ,old_amount = l_base_amount_tbl(i)
1730 ,amount = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
1731 WHERE po_line_id = l_po_line_id_tbl(i)
1732 AND draft_id = p_draft_id;
1733
1734 d_position := 60;
1735 IF (PO_LOG.d_stmt) THEN
1736 PO_LOG.stmt(d_module,d_position ,'update po_lines_draft all for all locks excluding F lock',SQL%ROWCOUNT);
1737 END IF;
1738
1739 -- 6. If UDA is enabled, then sync uda attributes
1740 IF p_uda_enabled_flag = 'Y'THEN
1741 sync_lines_ext(
1742 p_draft_id => p_draft_id
1743 ,x_return_status => x_return_status
1744 ,p_po_line_s_no_lck_tbl => l_po_line_s_no_lck_tbl --<Bug 16671702>
1745 ,p_po_line_s_no_p_lck_tbl => l_po_line_s_no_p_lck_tbl --<Bug 16671702>
1746 );
1747 END IF;
1748
1749 d_position := 70;
1750 IF (PO_LOG.d_stmt) THEN
1751 PO_LOG.stmt(d_module,d_position ,'sync_lines_ext');
1752 END IF;
1753
1754 -- 7. Sync po_attribute_values_draft
1755 --delete the recored from po_attribute_values_draft for the given header_id and draft_id
1756 -- and then insert the data from the base table
1757 l_draft_id_tbl.DELETE;
1758 l_delete_flag_tbl.DELETE;
1759
1760 SELECT pavd.attribute_values_id,
1761 p_draft_id,
1762 'N',
1763 change_status
1764 BULK COLLECT INTO l_attribute_values_id_tbl,
1765 l_draft_id_tbl,
1766 l_delete_flag_tbl,
1767 l_change_status_tbl
1768 FROM po_attribute_values_draft pavd
1769 WHERE pavd.po_line_id in ( select * from table(l_po_line_s_no_lck_tbl))
1770 AND pavd.draft_id= p_draft_id;
1771
1772 FORALL i in 1..l_attribute_values_id_tbl.COUNT
1773 DELETE FROM po_attribute_values_draft pavd
1774 WHERE pavd.attribute_values_id=l_attribute_values_id_tbl(i)
1775 AND pavd.draft_id = p_draft_id ;
1776
1777 PO_ATTR_VALUES_DRAFT_PKG.sync_draft_from_txn
1778 ( p_attribute_values_id_tbl => l_attribute_values_id_tbl,
1779 p_draft_id_tbl => l_draft_id_tbl,
1780 p_delete_flag_tbl => l_delete_flag_tbl,
1781 x_record_already_exist_tbl => l_record_already_exists
1782 );
1783
1784 FORALL i in 1..l_attribute_values_id_tbl.COUNT
1785 UPDATE po_attribute_values_draft pavd
1786 SET pavd.change_status = l_change_status_tbl(i)
1787 WHERE pavd.attribute_values_id=l_attribute_values_id_tbl(i)
1788 AND pavd.draft_id = p_draft_id ;
1789
1790 d_position := 80;
1791 IF (PO_LOG.d_stmt) THEN
1792 PO_LOG.stmt(d_module,d_position ,'PO_ATTR_VALUES_DRAFT_PKG.sync_draft_from_txn');
1793 END IF;
1794
1795 -- 8. Sync po_attribute_values_tlp_draft
1796 --delete the recored from po_attribute_values_tlp_draft for the given header_id and draft_id
1797 -- and then insert the data from the base table
1798 l_draft_id_tbl.DELETE;
1799 l_delete_flag_tbl.DELETE;
1800 l_change_status_tbl.DELETE;
1801
1802 SELECT pavtd.attribute_values_tlp_id,
1803 p_draft_id,
1804 'N',
1805 change_status
1806 BULK COLLECT INTO l_attribute_values_tlp_id_tbl,
1807 l_draft_id_tbl,
1808 l_delete_flag_tbl,
1809 l_change_status_tbl
1810 FROM po_attribute_values_tlp_draft pavtd
1811 WHERE pavtd.po_line_id in ( select * from table(l_po_line_s_no_lck_tbl))
1812 AND pavtd.draft_id = p_draft_id;
1813
1814 FORALL i in 1..l_attribute_values_tlp_id_tbl.COUNT
1815 DELETE FROM po_attribute_values_tlp_draft pavd
1816 WHERE pavd.attribute_values_tlp_id=l_attribute_values_tlp_id_tbl(i)
1817 AND pavd.draft_id = p_draft_id ;
1818
1819 PO_ATTR_VALUES_TLP_DRAFT_PKG.sync_draft_from_txn
1820 ( p_attribute_values_tlp_id_tbl => l_attribute_values_tlp_id_tbl,
1821 p_draft_id_tbl => l_draft_id_tbl,
1822 p_delete_flag_tbl => l_delete_flag_tbl,
1823 x_record_already_exist_tbl => l_record_already_exists
1824 );
1825
1826 FORALL i in 1..l_attribute_values_tlp_id_tbl.COUNT
1827 UPDATE po_attribute_values_tlp_draft pavd
1828 SET pavd.change_status = l_change_status_tbl(i)
1829 WHERE pavd.attribute_values_tlp_id=l_attribute_values_tlp_id_tbl(i)
1830 AND pavd.draft_id = p_draft_id ;
1831
1832 d_position := 90;
1833 IF (PO_LOG.d_stmt) THEN
1834 PO_LOG.stmt(d_module,d_position ,'PO_ATTR_VALUES_TLP_DRAFT_PKG.sync_draft_from_txn');
1835 END IF;
1836
1837 -- 9. Sync po_price_differentials
1838 --delete the recored from po_price_differentials for the given header_id and draft_id
1839 -- and then insert the data from the base table
1840 l_draft_id_tbl.DELETE;
1841 l_delete_flag_tbl.DELETE;
1842 l_change_status_tbl.DELETE;
1843
1844 SELECT ppdr.price_differential_id,
1845 p_draft_id,
1846 'N',
1847 change_status
1848 BULK COLLECT INTO l_price_differential_id_tbl,
1849 l_draft_id_tbl,
1850 l_delete_flag_tbl,
1851 l_change_status_tbl
1852 FROM po_price_diff_draft ppdr
1853 WHERE ppdr.entity_id in ( select * from table(l_po_line_s_no_lck_tbl))
1854 AND ppdr.draft_id = p_draft_id;
1855
1856 FORALL i in 1..l_price_differential_id_tbl.COUNT
1857 DELETE FROM po_price_diff_draft ppdr
1858 WHERE ppdr.price_differential_id=l_price_differential_id_tbl(i)
1859 AND ppdr.draft_id = p_draft_id ;
1860
1861 PO_PRICE_DIFF_DRAFT_PKG.sync_draft_from_txn
1862 ( p_price_differential_id_tbl => l_price_differential_id_tbl,
1863 p_draft_id_tbl => l_draft_id_tbl,
1864 p_delete_flag_tbl => l_delete_flag_tbl,
1865 x_record_already_exist_tbl => l_record_already_exists
1866 );
1867
1868 FORALL i in 1..l_price_differential_id_tbl.COUNT
1869 UPDATE po_price_diff_draft ppdr
1870 SET ppdr.change_status = l_change_status_tbl(i)
1871 WHERE ppdr.price_differential_id=l_price_differential_id_tbl(i)
1872 AND ppdr.draft_id = p_draft_id ;
1873
1874 d_position := 100;
1875 IF (PO_LOG.d_stmt) THEN
1876 PO_LOG.stmt(d_module,d_position ,'PO_PRICE_DIFF_DRAFT_PKG.sync_draft_from_txn');
1877 END IF;
1878
1879 FORALL i in 1.. l_po_line_id_tbl.COUNT
1880 UPDATE po_lines_draft_all
1881 SET revision_num=l_header_base_rev_num(i),
1882 change_status = l_change_status_lines_tbl(i)
1883 WHERE po_line_id = l_po_line_id_tbl(i)
1884 AND draft_id = p_draft_id;
1885
1886 d_position := 110;
1887 IF (PO_LOG.d_stmt) THEN
1888 PO_LOG.stmt(d_module,d_position ,'Number Of Rows Updated',SQL%ROWCOUNT);
1889 END IF;
1890
1891 -- UCA Project CLM R4 changes BEGIN
1892 IF l_po_line_id_tbl.COUNT > 0 THEN
1893 SELECT Nvl(po_core_s.Retrieveoptionvalue(org_id,
1894 po_core_s.g_undef_cont_act_col), 'N')
1895 INTO l_isucaenabled
1896 FROM po_lines_draft_all
1897 WHERE draft_id = p_draft_id
1898 AND rownum = 1;
1899 END IF;
1900
1901 -- Sync of CLM_UNDEF_FLAG,CLM_APPROVED_UNDEF_AMOUNT and CLM_UNDEF_ACTION_CODE
1902 IF l_isucaenabled = 'Y' THEN
1903 FORALL i in 1.. l_po_line_id_tbl.COUNT
1904 UPDATE po_lines_draft_all
1905 SET clm_approved_undef_amount = nvl(l_base_clm_app_undef_amt_tbl(i),0)
1906 + nvl((SELECT undef_amount
1907 FROM po_line_ucas
1908 WHERE po_line_id = L_po_line_id_tbl(i)
1909 AND undef_draft_id = p_draft_id),0)
1910 - (SELECT Nvl(SUM(undef_amount), 0)
1911 FROM po_line_ucas
1912 WHERE po_line_id = L_po_line_id_tbl(i)
1913 AND def_draft_id = p_draft_id),
1914 clm_undef_flag = (SELECT Nvl(Max('Y'), l_base_clm_undef_flag_tbl(i))
1915 FROM po_line_ucas
1916 WHERE po_line_id = l_po_line_id_tbl(i)
1917 AND undef_draft_id = p_draft_id
1918 AND def_draft_id IS NULL),
1919 clm_undef_action_code = l_draft_CLM_UNDEF_ACTION_CODE(i)
1920 WHERE po_line_id = l_po_line_id_tbl(i)
1921 AND draft_id = p_draft_id
1922 AND (l_base_clm_undef_flag_tbl(i) = 'Y' OR
1923 l_draft_CLM_UNDEF_FLAG_tbl(i) = 'Y'); --bug 16451273
1924 END IF; --IF l_isucaenabled = 'Y'
1925
1926 -- Sync of CLM_UDA_PRICING_TOTAL
1927 FORALL i in 1.. l_po_line_id_tbl.COUNT
1928 UPDATE po_lines_draft_all
1929 SET clm_uda_pricing_total =
1930 CASE
1931 WHEN NVL(cost_constraint,'X') IN ('NC','NSP')
1932 THEN 0
1933 ELSE
1934 CASE
1935 WHEN matching_basis = 'QUANTITY'
1936 THEN quantity*unit_price - Nvl(clm_approved_undef_amount,0)
1937 ELSE amount - NVL(clm_approved_undef_amount,0)
1938 END
1939 END
1940 WHERE po_line_id = l_po_line_id_tbl(i)
1941 AND draft_id = p_draft_id
1942 AND order_type_lookup_code in('AMOUNT', 'FIXED PRICE');--bug 16451273
1943 -- UCA Project CLM R4 Changes End
1944
1945 d_position := 120;
1946 IF (PO_LOG.d_proc) THEN
1947 PO_LOG.proc_end(d_module);
1948 END IF;
1949
1950 EXCEPTION
1951 WHEN others THEN
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1953 IF (PO_LOG.d_exc) THEN
1954 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
1955 END IF;
1956 RAISE;
1957 END sync_lines;
1958
1959
1960 --------------------------------------------------------------------------------
1961 --Start of Comments
1962 --Name: sync_shipments
1963 -- <Conc Mods Project>
1964 --Pre-reqs:
1965 -- none
1966 --Modifies: None
1967 --Function:
1968 -- This procedure syncs Mod shipment which are out of sync with the corresponding
1969 -- Base shipment record, i.e. when rev num of base > rev num of Mod
1970 -- Logic for each type of lock:
1971 -- No Lock: Delete the draft row and copy row back from base
1972 -- S Lock: Delete the draft row, copy the row back from base and sync S lock attrs
1973 -- P Lock: Sync S lock attrs
1974 -- F Lock: No action required
1975 --Parameters:
1976 --IN:
1977 --p_draft_id
1978 -- draft_id of the Modification
1979 --IN OUT:
1980 --OUT:
1981 --Notes:
1982 --End of Comments
1983 --------------------------------------------------------------------------------
1984 PROCEDURE sync_shipments(
1985 p_draft_id IN NUMBER
1986 ,p_uda_enabled_flag VARCHAR2
1987 ,x_return_status OUT NOCOPY VARCHAR2
1988 ) IS
1989 l_base_quantity_tbl PO_TBL_NUMBER;
1990 l_base_amount_tbl PO_TBL_NUMBER;
1991 l_draft_quantity_tbl PO_TBL_NUMBER;
1992 l_draft_old_quantity_tbl PO_TBL_NUMBER;
1993 l_draft_amount_tbl PO_TBL_NUMBER;
1994 l_draft_old_amount_tbl PO_TBL_NUMBER;
1995 l_line_location_tbl PO_TBL_NUMBER;
1996 l_po_line_loc_s_no_lck_tbl PO_TBL_NUMBER;
1997 l_draft_id_tbl PO_TBL_NUMBER;
1998 l_delete_flag_tbl PO_TBL_VARCHAR1;
1999 l_record_already_exists PO_TBL_VARCHAR1;
2000 l_header_base_rev_num PO_TBL_NUMBER;
2001 d_api_name CONSTANT VARCHAR2(30) := 'sync_shipments';
2002 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2003 d_position NUMBER;
2004 d_api_version CONSTANT NUMBER := 1.0;
2005 l_change_status_tbl PO_TBL_VARCHAR30;
2006 BEGIN
2007 l_base_quantity_tbl:=po_tbl_number();
2008 l_base_amount_tbl:=po_tbl_number();
2009 l_draft_quantity_tbl:=po_tbl_number();
2010 l_draft_old_quantity_tbl:=po_tbl_number();
2011 l_draft_amount_tbl:=po_tbl_number();
2012 l_draft_old_amount_tbl:=po_tbl_number();
2013 l_line_location_tbl:=po_tbl_number();
2014 l_po_line_loc_s_no_lck_tbl:=po_tbl_number();
2015 l_draft_id_tbl :=po_tbl_number();
2016 l_delete_flag_tbl:=Po_Tbl_Varchar1();
2017 l_record_already_exists:=Po_Tbl_Varchar1();
2018 l_header_base_rev_num:=po_tbl_number();
2019 l_change_status_tbl :=PO_TBL_VARCHAR30();
2020 d_position := 0;
2021 IF (PO_LOG.d_proc) THEN
2022 PO_LOG.proc_begin(d_module);
2023 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2024 END IF;
2025
2026 -- 1. Identify shipments which P, S or no lock which needs to be synced
2027 -- Collect quantity and amount for all such shipments
2028 SELECT
2029 pla.revision_num,
2030 pla.quantity,
2031 pla.amount,
2032 plda.quantity,
2033 plda.old_quantity ,
2034 plda.amount,
2035 plda.old_amount,
2036 plda.line_location_id,
2037 plda.change_status
2038 BULK COLLECT INTO
2039 l_header_base_rev_num,
2040 l_base_quantity_tbl,
2041 l_base_amount_tbl,
2042 l_draft_quantity_tbl,
2043 l_draft_old_quantity_tbl,
2044 l_draft_amount_tbl,
2045 l_draft_old_amount_tbl,
2046 l_line_location_tbl,
2047 l_change_status_tbl
2048 FROM po_line_locations_all pla
2049 ,po_line_locations_draft_all plda
2050 WHERE pla.line_location_id = plda.line_location_id
2051 AND plda.draft_id = p_draft_id
2052 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
2053 AND NOT EXISTS (SELECT 'F locks'
2054 FROM po_entity_locks pel
2055 WHERE pel.entity_pk1 = plda.line_location_id
2056 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY
2057 AND pel.lock_by_draft_id = plda.draft_id
2058 AND pel.lock_type = 'F')
2059 AND pla.shipment_type <> 'PRICE BREAK';
2060
2061 d_position := 20;
2062 IF (PO_LOG.d_stmt) THEN
2063 PO_LOG.stmt(d_module,d_position ,'Base Header Revision Num', l_header_base_rev_num);
2064 PO_LOG.stmt(d_module,d_position ,'Base Quantity', l_base_quantity_tbl);
2065 PO_LOG.stmt(d_module,d_position ,'Base Amount', l_base_amount_tbl);
2066 PO_LOG.stmt(d_module,d_position ,'Draft Qunatity Limit', l_draft_quantity_tbl);
2067 PO_LOG.stmt(d_module,d_position ,'Draft Old Quantity Limit', l_draft_old_quantity_tbl);
2068 PO_LOG.stmt(d_module,d_position ,'Draft Amount Limit', l_draft_amount_tbl);
2069 PO_LOG.stmt(d_module,d_position ,'Draft Old Amount Limit', l_draft_old_amount_tbl);
2070 PO_LOG.stmt(d_module,d_position ,'line_location_id', l_line_location_tbl);
2071 PO_LOG.stmt(d_module,d_position ,'the above values computed for all lock types excluding F and excluding shipment type=PRICE BREAK');
2072 END IF;
2073
2074 -- 2. Identify shipments with S lock or no lock which needs to be synced
2075 SELECT plda.line_location_id
2076 ,plda.draft_id
2077 ,'N'
2078 BULK COLLECT INTO l_po_line_loc_s_no_lck_tbl
2079 ,l_draft_id_tbl
2080 ,l_delete_flag_tbl
2081 FROM po_line_locations_draft_all plda,
2082 po_line_locations_all pla
2083 WHERE plda.draft_id = p_draft_id
2084 AND pla.line_location_id = plda.line_location_id
2085 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
2086 AND NOT EXISTS (SELECT 'P/F locks'
2087 FROM po_entity_locks pel
2088 WHERE pel.entity_pk1 = plda.line_location_id
2089 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY
2090 AND pel.lock_by_draft_id = plda.draft_id
2091 AND pel.lock_type IN ('F','P'))
2092 AND pla.shipment_type <> 'PRICE BREAK';
2093
2094 d_position := 40;
2095 IF (PO_LOG.d_stmt) THEN
2096 PO_LOG.stmt(d_module,d_position ,'line_location_id for S and No locks', l_po_line_loc_s_no_lck_tbl);
2097 PO_LOG.stmt(d_module,d_position ,'Draft Id Tbl', l_draft_id_tbl);
2098 PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
2099 END IF;
2100
2101 -- 3. Delete shipments identified in #2 above.
2102 FORALL i in 1..l_po_line_loc_s_no_lck_tbl.COUNT
2103 DELETE FROM po_line_locations_draft_all plda
2104 WHERE plda.line_location_id = l_po_line_loc_s_no_lck_tbl(i)
2105 AND plda.draft_id = p_draft_id;
2106
2107 d_position := 50;
2108 IF (PO_LOG.d_stmt) THEN
2109 PO_LOG.stmt(d_module,d_position ,'Deleted line_location_ids having S and No locks',SQL%ROWCOUNT);
2110 END IF;
2111
2112 -- 4. Copy the delted shipments in #3 from the base document
2113 PO_LINE_LOCATIONS_DRAFT_PKG.sync_draft_from_txn
2114 (p_line_location_id_tbl => l_po_line_loc_s_no_lck_tbl,
2115 p_draft_id_tbl => l_draft_id_tbl,
2116 p_delete_flag_tbl => l_delete_flag_tbl,
2117 x_record_already_exist_tbl => l_record_already_exists
2118 );
2119
2120 d_position := 50;
2121 IF (PO_LOG.d_stmt) THEN
2122 PO_LOG.stmt(d_module,d_position ,'sync_draft_from_txn');
2123 END IF;
2124
2125 -- 5. Sync quantity and amount using the values fetched in #1 above
2126 FORALL i in 1.. l_line_location_tbl.COUNT
2127 UPDATE po_line_locations_draft_all
2128 SET old_quantity = l_base_quantity_tbl(i)
2129 ,quantity = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
2130 ,old_amount = l_base_amount_tbl(i)
2131 ,amount = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
2132 WHERE line_location_id = l_line_location_tbl(i)
2133 AND draft_id = p_draft_id;
2134
2135 d_position := 60;
2136 IF (PO_LOG.d_stmt) THEN
2137 PO_LOG.stmt(d_module,d_position ,'update po_lines_draft all for all locks excluding F lock',SQL%ROWCOUNT);
2138 PO_LOG.stmt(d_module,d_position,'revision_num',l_header_base_rev_num);
2139 END IF;
2140
2141 -- 6. If UDA is enabled, then sync uda attributes
2142 IF p_uda_enabled_flag ='Y' THEN
2143 sync_shipments_ext(
2144 p_draft_id => p_draft_id
2145 ,x_return_status => x_return_status
2146 ,p_po_line_loc_s_no_lck_tbl => l_po_line_loc_s_no_lck_tbl --<Bug 16671702>
2147 );
2148 END IF;
2149
2150 d_position := 70;
2151 IF (PO_LOG.d_stmt) THEN
2152 PO_LOG.stmt(d_module,d_position ,'sync_shipments_ext');
2153 END IF;
2154
2155 FORALL i in 1.. l_line_location_tbl.COUNT
2156 UPDATE po_line_locations_draft_all
2157 SET revision_num=l_header_base_rev_num(i),
2158 change_status = l_change_status_tbl(i)
2159 WHERE line_location_id = l_line_location_tbl(i)
2160 AND draft_id = p_draft_id;
2161
2162 d_position := 80;
2163 IF (PO_LOG.d_stmt) THEN
2164 PO_LOG.stmt(d_module,d_position ,'Number of Rows Updated',SQL%ROWCOUNT);
2165 END IF;
2166
2167 d_position := 90;
2168 IF (PO_LOG.d_proc) THEN
2169 PO_LOG.proc_end(d_module);
2170 END IF;
2171
2172 EXCEPTION
2173 WHEN others THEN
2174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2175 IF (PO_LOG.d_exc) THEN
2176 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
2177 END IF;
2178 RAISE;
2179 END sync_shipments;
2180
2181 --------------------------------------------------------------------------------
2182 --Start of Comments
2183 --Name: sync_distributions
2184 -- <Conc Mods Project>
2185 --Pre-reqs:
2186 -- none
2187 --Modifies: None
2188 --Function:
2189 -- This procedure syncs Mod dist which are out of sync with the corresponding
2190 -- Base dist record, i.e. when rev num of base > rev num of Mod
2191 -- Logic for each type of lock:
2192 -- No Lock: Delete the draft row and copy row back from base
2193 -- S Lock: Delete the draft row, copy the row back from base and sync S lock attrs
2194 -- P Lock: Sync S lock attrs
2195 -- F Lock: No action required
2196 --Parameters:
2197 --IN:
2198 --p_draft_id
2199 -- draft_id of the Modification
2200 --IN OUT:
2201 --OUT:
2202 --Notes:
2203 --End of Comments
2204 --------------------------------------------------------------------------------
2205 PROCEDURE sync_distributions(
2206 p_draft_id IN NUMBER
2207 ,x_return_status OUT NOCOPY VARCHAR2
2208 ) IS
2209
2210 l_base_quantity_tbl PO_TBL_NUMBER;
2211 l_base_amount_tbl PO_TBL_NUMBER;
2212 l_draft_quantity_tbl PO_TBL_NUMBER;
2213 l_draft_old_quantity_tbl PO_TBL_NUMBER;
2214 l_draft_amount_tbl PO_TBL_NUMBER;
2215 l_draft_old_amount_tbl PO_TBL_NUMBER;
2216 l_po_distribution_tbl PO_TBL_NUMBER;
2217 l_po_dist_s_no_lck_tbl PO_TBL_NUMBER;
2218 l_draft_id_tbl PO_TBL_NUMBER;
2219 l_delete_flag_tbl PO_TBL_VARCHAR1;
2220 l_record_already_exists PO_TBL_VARCHAR1;
2221 l_header_base_rev_num PO_TBL_NUMBER;
2222 d_api_name CONSTANT VARCHAR2(30) := 'sync_distributions';
2223 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2224 d_position NUMBER;
2225 d_api_version CONSTANT NUMBER := 1.0;
2226 l_change_status_tbl PO_TBL_VARCHAR30;
2227 BEGIN
2228 l_base_quantity_tbl:=po_tbl_number();
2229 l_base_amount_tbl:=po_tbl_number();
2230 l_draft_quantity_tbl:=po_tbl_number();
2231 l_draft_old_quantity_tbl:=po_tbl_number();
2232 l_draft_amount_tbl:=po_tbl_number();
2233 l_draft_old_amount_tbl:=po_tbl_number();
2234 l_po_distribution_tbl:=po_tbl_number();
2235 l_po_dist_s_no_lck_tbl:=po_tbl_number();
2236 l_draft_id_tbl :=po_tbl_number();
2237 l_delete_flag_tbl:=Po_Tbl_Varchar1();
2238 l_record_already_exists:=Po_Tbl_Varchar1();
2239 l_header_base_rev_num :=po_tbl_number();
2240 l_change_status_tbl := PO_TBL_VARCHAR30();
2241 d_position := 0;
2242 IF (PO_LOG.d_proc) THEN
2243 PO_LOG.proc_begin(d_module);
2244 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2245 END IF;
2246
2247 -- 1. Identify dist which P, S or no lock which needs to be synced
2248 -- Collect quantity and amount for all such dist
2249 SELECT pla.revision_num,
2250 pla.quantity_ordered,
2251 pla.amount_ordered,
2252 plda.quantity_ordered ,
2253 plda.old_quantity_ordered ,
2254 plda.amount_ordered,
2255 plda.old_amount_ordered,
2256 plda.po_distribution_id,
2257 plda.change_status
2258 BULK COLLECT INTO
2259 l_header_base_rev_num,
2260 l_base_quantity_tbl,
2261 l_base_amount_tbl,
2262 l_draft_quantity_tbl,
2263 l_draft_old_quantity_tbl,
2264 l_draft_amount_tbl,
2265 l_draft_old_amount_tbl,
2266 l_po_distribution_tbl,
2267 l_change_status_tbl
2268 FROM po_distributions_all pla
2269 ,po_distributions_draft_all plda
2270 WHERE pla.po_distribution_id = plda.po_distribution_id
2271 AND plda.draft_id = p_draft_id
2272 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
2273 AND NOT EXISTS (SELECT 'F locks'
2274 FROM po_entity_locks pel
2275 WHERE pel.entity_pk1 = plda.po_distribution_id
2276 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY
2277 AND pel.lock_by_draft_id = plda.draft_id
2278 AND pel.lock_type = 'F')
2279 AND pla.distribution_type <> 'AGREEMENT';
2280
2281 d_position := 20;
2282 IF (PO_LOG.d_stmt) THEN
2283 PO_LOG.stmt(d_module,d_position ,'Base Header Revision Num', l_header_base_rev_num);
2284 PO_LOG.stmt(d_module,d_position ,'Base Quantity', l_base_quantity_tbl);
2285 PO_LOG.stmt(d_module,d_position ,'Base Amount', l_base_amount_tbl);
2286 PO_LOG.stmt(d_module,d_position ,'Draft Qunatity Limit', l_draft_quantity_tbl);
2287 PO_LOG.stmt(d_module,d_position ,'Draft Old Quantity Limit', l_draft_old_quantity_tbl);
2288 PO_LOG.stmt(d_module,d_position ,'Draft Amount Limit', l_draft_amount_tbl);
2289 PO_LOG.stmt(d_module,d_position ,'Draft Old Amount Limit', l_draft_old_amount_tbl);
2290 PO_LOG.stmt(d_module,d_position ,'po_distribution_id', l_po_distribution_tbl);
2291 PO_LOG.stmt(d_module,d_position ,'the above values computed for all lock types excluding F and excluding shipment type=PRICE BREAK');
2292 END IF;
2293
2294 -- 2. Identify dist with S lock or no lock which needs to be synced
2295 SELECT plda.po_distribution_id
2296 ,p_draft_id
2297 ,'N'
2298 BULK COLLECT INTO l_po_dist_s_no_lck_tbl
2299 ,l_draft_id_tbl
2300 ,l_delete_flag_tbl
2301 FROM po_distributions_draft_all plda,
2302 po_distributions_all pla
2303 WHERE plda.draft_id = p_draft_id
2304 AND pla.po_distribution_id = plda.po_distribution_id
2305 AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
2306 AND NOT EXISTS (SELECT 'P/F locks'
2307 FROM po_entity_locks pel
2308 WHERE pel.entity_pk1 = plda.po_distribution_id
2309 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY
2310 AND pel.lock_by_draft_id = plda.draft_id
2311 AND pel.lock_type IN ('F','P'))
2312 AND pla.distribution_type <> 'AGREEMENT';
2313
2314 d_position := 40;
2315 IF (PO_LOG.d_stmt) THEN
2316 PO_LOG.stmt(d_module,d_position ,'po_distribution_id for S and No locks', l_po_dist_s_no_lck_tbl);
2317 PO_LOG.stmt(d_module,d_position ,'Draft Id Tbl', l_draft_id_tbl);
2318 PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
2319 END IF;
2320
2321 -- 3. Delete dist identified in #2 above.
2322 FORALL i in 1..l_po_dist_s_no_lck_tbl.COUNT
2323 DELETE FROM po_distributions_draft_all plda
2324 WHERE plda.po_distribution_id=l_po_dist_s_no_lck_tbl(i)
2325 AND plda.draft_id = p_draft_id;
2326
2327 d_position := 50;
2328 IF (PO_LOG.d_stmt) THEN
2329 PO_LOG.stmt(d_module,d_position ,'Deleted po_dist_ids having S and No locks',SQL%ROWCOUNT);
2330 END IF;
2331
2332 -- 4. Copy the delted dist in #3 from the base document
2333 PO_DISTRIBUTIONS_DRAFT_PKG.sync_draft_from_txn
2334 (p_po_distribution_id_tbl => l_po_dist_s_no_lck_tbl,
2335 p_draft_id_tbl => l_draft_id_tbl,
2336 p_delete_flag_tbl => l_delete_flag_tbl,
2337 x_record_already_exist_tbl => l_record_already_exists
2338 );
2339
2340 d_position := 50;
2341 IF (PO_LOG.d_stmt) THEN
2342 PO_LOG.stmt(d_module,d_position ,'sync_draft_from_txn');
2343 END IF;
2344
2345 -- 5. Sync quantity and amount using the values fetched in #1 above
2346 FORALL i in 1.. l_po_distribution_tbl.COUNT
2347 UPDATE po_distributions_draft_all
2348 SET old_quantity_ordered = l_base_quantity_tbl(i)
2349 ,quantity_ordered = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
2350 ,old_amount_ordered = l_base_amount_tbl(i)
2351 ,amount_ordered = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
2352 ,revision_num = l_header_base_rev_num(i),
2353 change_status= l_change_status_tbl(i)
2354 WHERE po_distribution_id = l_po_distribution_tbl(i)
2355 AND draft_id = p_draft_id;
2356
2357 d_position := 60;
2358 IF (PO_LOG.d_stmt) THEN
2359 PO_LOG.stmt(d_module,d_position ,'update po_distributions_draft_all for all locks excluding F lock');
2360 PO_LOG.stmt(d_module,d_position,'revision_num',l_header_base_rev_num);
2361 END IF;
2362
2363 d_position := 80;
2364 IF (PO_LOG.d_proc) THEN
2365 PO_LOG.proc_end(d_module);
2366 END IF;
2367
2368 EXCEPTION
2369 WHEN others THEN
2370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2371 IF (PO_LOG.d_exc) THEN
2372 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
2373 END IF;
2374 RAISE;
2375 END sync_distributions;
2376
2377
2378 --------------------------------------------------------------------------------
2379 --Start of Comments
2380 --Name: sync_headers_ext
2381 -- <Conc Mods Project>
2382 --Pre-reqs:
2383 -- none
2384 --Modifies: None
2385 --Function:
2386 -- This procedure syncs the all header_ext entities of Mod with the corresponding
2387 -- Base header_ext entites
2388 --Parameters:
2389 --IN:
2390 --p_draft_id
2391 -- draft_id of the Modification
2392 --IN OUT:
2393 --OUT:
2394 --Notes:
2395 --End of Comments
2396 --------------------------------------------------------------------------------
2397 PROCEDURE sync_headers_ext(
2398 p_draft_id IN NUMBER
2399 ,x_return_status OUT NOCOPY VARCHAR2
2400 )
2401 IS
2402
2403 l_po_header_id NUMBER;
2404 l_base_lang VARCHAR2(30);
2405 l_entension_id_tbl PO_TBL_NUMBER;
2406 d_api_name CONSTANT VARCHAR2(30) := 'sync_headers_ext';
2407 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2408 d_position NUMBER;
2409 d_api_version CONSTANT NUMBER := 1.0;
2410 BEGIN
2411
2412 d_position := 0;
2413 IF (PO_LOG.d_proc) THEN
2414 PO_LOG.proc_begin(d_module);
2415 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2416 END IF;
2417
2418 l_entension_id_tbl:=po_tbl_number();
2419 SELECT document_id
2420 INTO l_po_header_id
2421 FROM po_drafts
2422 WHERE draft_id = p_draft_id;
2423
2424 d_position := 20;
2425 IF (PO_LOG.d_stmt) THEN
2426 PO_LOG.stmt(d_module,d_position ,'PODraft Header Id', l_po_header_id);
2427 END IF;
2428
2429 MERGE
2430 INTO po_headers_all_ext_b phbd
2431 USING
2432 (SELECT phb1.extension_id ,
2433 phb1.attr_group_id ,
2434 phb1.po_header_id ,
2435 phb1.data_level_id ,
2436 phb1.pk1_value ,
2437 phb1.pk2_value ,
2438 phb1.pk3_value ,
2439 phb1.pk4_value ,
2440 phb1.pk5_value ,
2441 phb1.last_update_date ,
2442 phb1.last_updated_by ,
2443 phb1.last_update_login ,
2444 phb1.created_by ,
2445 phb1.creation_date ,
2446 phb1.c_ext_attr1 ,
2447 phb1.c_ext_attr2 ,
2448 phb1.c_ext_attr3 ,
2449 phb1.c_ext_attr4 ,
2450 phb1.c_ext_attr5 ,
2451 phb1.c_ext_attr6 ,
2452 phb1.c_ext_attr7 ,
2453 phb1.c_ext_attr8 ,
2454 phb1.c_ext_attr9 ,
2455 phb1.c_ext_attr10 ,
2456 phb1.c_ext_attr11 ,
2457 phb1.c_ext_attr12 ,
2458 phb1.c_ext_attr13 ,
2459 phb1.c_ext_attr14 ,
2460 phb1.c_ext_attr15 ,
2461 phb1.c_ext_attr16 ,
2462 phb1.c_ext_attr17 ,
2463 phb1.c_ext_attr18 ,
2464 phb1.c_ext_attr19 ,
2465 phb1.c_ext_attr20 ,
2466 phb1.c_ext_attr21 ,
2467 phb1.c_ext_attr22 ,
2468 phb1.c_ext_attr23 ,
2469 phb1.c_ext_attr24 ,
2470 phb1.c_ext_attr25 ,
2471 phb1.c_ext_attr26 ,
2472 phb1.c_ext_attr27 ,
2473 phb1.c_ext_attr28 ,
2474 phb1.c_ext_attr29 ,
2475 phb1.c_ext_attr30 ,
2476 phb1.c_ext_attr31 ,
2477 phb1.c_ext_attr32 ,
2478 phb1.c_ext_attr33 ,
2479 phb1.c_ext_attr34 ,
2480 phb1.c_ext_attr35 ,
2481 phb1.c_ext_attr36 ,
2482 phb1.c_ext_attr37 ,
2483 phb1.c_ext_attr38 ,
2484 phb1.c_ext_attr39 ,
2485 phb1.c_ext_attr40 ,
2486 phb1.n_ext_attr1 ,
2487 phb1.n_ext_attr2 ,
2488 phb1.n_ext_attr3 ,
2489 phb1.n_ext_attr4 ,
2490 phb1.n_ext_attr5 ,
2491 phb1.n_ext_attr6 ,
2492 phb1.n_ext_attr7 ,
2493 phb1.n_ext_attr8 ,
2494 phb1.n_ext_attr9 ,
2495 phb1.n_ext_attr10 ,
2496 phb1.n_ext_attr11 ,
2497 phb1.n_ext_attr12 ,
2498 phb1.n_ext_attr13 ,
2499 phb1.n_ext_attr14 ,
2500 phb1.n_ext_attr15 ,
2501 phb1.n_ext_attr16 ,
2502 phb1.n_ext_attr17 ,
2503 phb1.n_ext_attr18 ,
2504 phb1.n_ext_attr19 ,
2505 phb1.n_ext_attr20 ,
2506 phb1.uom_ext_attr1 ,
2507 phb1.uom_ext_attr2 ,
2508 phb1.uom_ext_attr3 ,
2509 phb1.uom_ext_attr4 ,
2510 phb1.uom_ext_attr5 ,
2511 phb1.uom_ext_attr6 ,
2512 phb1.uom_ext_attr7 ,
2513 phb1.uom_ext_attr8 ,
2514 phb1.uom_ext_attr9 ,
2515 phb1.uom_ext_attr10 ,
2516 phb1.uom_ext_attr11 ,
2517 phb1.uom_ext_attr12 ,
2518 phb1.uom_ext_attr13 ,
2519 phb1.uom_ext_attr14 ,
2520 phb1.uom_ext_attr15 ,
2521 phb1.uom_ext_attr16 ,
2522 phb1.uom_ext_attr17 ,
2523 phb1.uom_ext_attr18 ,
2524 phb1.uom_ext_attr19 ,
2525 phb1.uom_ext_attr20 ,
2526 phb1.d_ext_attr1 ,
2527 phb1.d_ext_attr2 ,
2528 phb1.d_ext_attr3 ,
2529 phb1.d_ext_attr4 ,
2530 phb1.d_ext_attr5 ,
2531 phb1.d_ext_attr6 ,
2532 phb1.d_ext_attr7 ,
2533 phb1.d_ext_attr8 ,
2534 phb1.d_ext_attr9 ,
2535 phb1.d_ext_attr10 ,
2536 phb1.uda_template_id ,
2537 phb1.draft_id ,
2538 phb1.revision_num
2539 FROM po_headers_all_ext_b phb1
2540 WHERE phb1.draft_id = -1
2541 AND phb1.po_header_id = l_po_header_id
2542 -- exclude Mod specific and doc numbering attribute groups
2543 AND NOT EXISTS (SELECT 'Mod specific or doc numbering attribute groups'
2544 FROM po_uda_ag_template_usages usg
2545 WHERE usg.template_id = phb1.uda_template_id
2546 AND usg.attribute_group_id = phb1.attr_group_id
2547 AND usg.attribute_category IN ('DOCUMENT_NUMBERING', 'MODIFICATIONS')
2548 )
2549 AND NOT EXISTS (SELECT 'only S or Null Locks'
2550 FROM po_entity_locks pel
2551 WHERE pel.lock_type IN ('P','F')
2552 AND pel.entity_pk1= phb1.po_header_id
2553 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
2554 AND pel.lock_by_draft_id = p_draft_id )
2555 -- exclude multi-row attribute groups
2556 AND EXISTS (SELECT 'Single row attr grp'
2557 FROM ego_fnd_dsc_flx_ctx_ext ag
2558 WHERE phb1.attr_group_id = ag.attr_group_id
2559 AND ag.multi_row = 'N'
2560 )
2561 ) phb
2562 ON ( phb.attr_group_id = phbd.attr_group_id
2563 AND phbd.po_header_id = l_po_header_id
2564 AND phbd.draft_id = p_draft_id
2565 )
2566 WHEN MATCHED THEN
2567 UPDATE
2568 SET
2569 phbd.c_ext_attr1 = phb.c_ext_attr1,
2570 phbd.c_ext_attr2 = phb.c_ext_attr2 ,
2571 phbd.c_ext_attr3 = phb.c_ext_attr3 ,
2572 phbd.c_ext_attr4 = phb.c_ext_attr4 ,
2573 phbd.c_ext_attr5 = phb.c_ext_attr5 ,
2574 phbd.c_ext_attr6 = phb.c_ext_attr6 ,
2575 phbd.c_ext_attr7 = phb.c_ext_attr7 ,
2576 phbd.c_ext_attr8 = phb.c_ext_attr8 ,
2577 phbd.c_ext_attr9 = phb.c_ext_attr9 ,
2578 phbd.c_ext_attr10 = phb.c_ext_attr10 ,
2579 phbd.c_ext_attr11 = phb.c_ext_attr11 ,
2580 phbd.c_ext_attr12 = phb.c_ext_attr12 ,
2581 phbd.c_ext_attr13 = phb.c_ext_attr13 ,
2582 phbd.c_ext_attr14 = phb.c_ext_attr14 ,
2583 phbd.c_ext_attr15 = phb.c_ext_attr15 ,
2584 phbd.c_ext_attr16 = phb.c_ext_attr16 ,
2585 phbd.c_ext_attr17 = phb.c_ext_attr17 ,
2586 phbd.c_ext_attr18 = phb.c_ext_attr18 ,
2587 phbd.c_ext_attr19 = phb.c_ext_attr19 ,
2588 phbd.c_ext_attr20 = phb.c_ext_attr20 ,
2589 phbd.c_ext_attr21 = phb.c_ext_attr21 ,
2590 phbd.c_ext_attr22 = phb.c_ext_attr22 ,
2591 phbd.c_ext_attr23 = phb.c_ext_attr23 ,
2592 phbd.c_ext_attr24 = phb.c_ext_attr24 ,
2593 phbd.c_ext_attr25 = phb.c_ext_attr25 ,
2594 phbd.c_ext_attr26 = phb.c_ext_attr26 ,
2595 phbd.c_ext_attr27 = phb.c_ext_attr27 ,
2596 phbd.c_ext_attr28 = phb.c_ext_attr28 ,
2597 phbd.c_ext_attr29 = phb.c_ext_attr29 ,
2598 phbd.c_ext_attr30 = phb.c_ext_attr30 ,
2599 phbd.c_ext_attr31 = phb.c_ext_attr31 ,
2600 phbd.c_ext_attr32 = phb.c_ext_attr32 ,
2601 phbd.c_ext_attr33 = phb.c_ext_attr33 ,
2602 phbd.c_ext_attr34 = phb.c_ext_attr34 ,
2603 phbd.c_ext_attr35 = phb.c_ext_attr35 ,
2604 phbd.c_ext_attr36 = phb.c_ext_attr36 ,
2605 phbd.c_ext_attr37 = phb.c_ext_attr37 ,
2606 phbd.c_ext_attr38 = phb.c_ext_attr38 ,
2607 phbd.c_ext_attr39 = phb.c_ext_attr39 ,
2608 phbd.c_ext_attr40 = phb.c_ext_attr40 ,
2609 phbd.n_ext_attr1 = phb.n_ext_attr1 ,
2610 phbd.n_ext_attr2 = phb.n_ext_attr2 ,
2611 phbd.n_ext_attr3 = phb.n_ext_attr3 ,
2612 phbd.n_ext_attr4 = phb.n_ext_attr4 ,
2613 phbd.n_ext_attr5 = phb.n_ext_attr5 ,
2614 phbd.n_ext_attr6 = phb.n_ext_attr6 ,
2615 phbd.n_ext_attr7 = phb.n_ext_attr7 ,
2616 phbd.n_ext_attr8 = phb.n_ext_attr8 ,
2617 phbd.n_ext_attr9 = phb.n_ext_attr9 ,
2618 phbd.n_ext_attr10 = phb.n_ext_attr10 ,
2619 phbd.n_ext_attr11 = phb.n_ext_attr11 ,
2620 phbd.n_ext_attr12 = phb.n_ext_attr12 ,
2621 phbd.n_ext_attr13 = phb.n_ext_attr13 ,
2622 phbd.n_ext_attr14 = phb.n_ext_attr14 ,
2623 phbd.n_ext_attr15 = phb.n_ext_attr15 ,
2624 phbd.n_ext_attr16 = phb.n_ext_attr16 ,
2625 phbd.n_ext_attr17 = phb.n_ext_attr17 ,
2626 phbd.n_ext_attr18 = phb.n_ext_attr18 ,
2627 phbd.n_ext_attr19 = phb.n_ext_attr19 ,
2628 phbd.n_ext_attr20 = phb.n_ext_attr20 ,
2629 phbd.uom_ext_attr1 = phb.uom_ext_attr1 ,
2630 phbd.uom_ext_attr2 = phb.uom_ext_attr2 ,
2631 phbd.uom_ext_attr3 = phb.uom_ext_attr3 ,
2632 phbd.uom_ext_attr4= phb.uom_ext_attr4,
2633 phbd.uom_ext_attr5 = phb.uom_ext_attr5 ,
2634 phbd.uom_ext_attr6 = phb.uom_ext_attr6 ,
2635 phbd.uom_ext_attr7 = phb.uom_ext_attr7 ,
2636 phbd.uom_ext_attr8 = phb.uom_ext_attr8 ,
2637 phbd.uom_ext_attr9 = phb.uom_ext_attr9 ,
2638 phbd.uom_ext_attr10 = phb.uom_ext_attr10 ,
2639 phbd.uom_ext_attr11 = phb.uom_ext_attr11 ,
2640 phbd.uom_ext_attr12 = phb.uom_ext_attr12 ,
2641 phbd.uom_ext_attr13 = phb.uom_ext_attr13 ,
2642 phbd.uom_ext_attr14 = phb.uom_ext_attr14 ,
2643 phbd.uom_ext_attr15 = phb.uom_ext_attr15 ,
2644 phbd.uom_ext_attr16 = phb.uom_ext_attr16 ,
2645 phbd.uom_ext_attr17 = phb.uom_ext_attr17 ,
2646 phbd.uom_ext_attr18 = phb.uom_ext_attr18 ,
2647 phbd.uom_ext_attr19 = phb.uom_ext_attr19 ,
2648 phbd.uom_ext_attr20 = phb.uom_ext_attr20 ,
2649 phbd.d_ext_attr1 = phb.d_ext_attr1 ,
2650 phbd.d_ext_attr2 = phb.d_ext_attr2 ,
2651 phbd.d_ext_attr3 = phb.d_ext_attr3 ,
2652 phbd.d_ext_attr4 = phb.d_ext_attr4 ,
2653 phbd.d_ext_attr5 = phb.d_ext_attr5 ,
2654 phbd.d_ext_attr6 = phb.d_ext_attr6 ,
2655 phbd.d_ext_attr7 = phb.d_ext_attr7 ,
2656 phbd.d_ext_attr8 = phb.d_ext_attr8 ,
2657 phbd.d_ext_attr9 = phb.d_ext_attr9 ,
2658 phbd.d_ext_attr10 = phb.d_ext_attr10 ,
2659 phbd.revision_num = phb.revision_num
2660 WHEN NOT MATCHED THEN
2661 INSERT
2662 (phbd.extension_id ,
2663 phbd.attr_group_id ,
2664 phbd.po_header_id ,
2665 phbd.data_level_id ,
2666 phbd.pk1_value ,
2667 phbd.pk2_value ,
2668 phbd.pk3_value ,
2669 phbd.pk4_value ,
2670 phbd.pk5_value ,
2671 phbd.last_update_date ,
2672 phbd.last_updated_by ,
2673 phbd.last_update_login ,
2674 phbd.created_by ,
2675 phbd.creation_date ,
2676 phbd.c_ext_attr1 ,
2677 phbd.c_ext_attr2 ,
2678 phbd.c_ext_attr3 ,
2679 phbd.c_ext_attr4 ,
2680 phbd.c_ext_attr5 ,
2681 phbd.c_ext_attr6 ,
2682 phbd.c_ext_attr7 ,
2683 phbd.c_ext_attr8 ,
2684 phbd.c_ext_attr9 ,
2685 phbd.c_ext_attr10 ,
2686 phbd.c_ext_attr11 ,
2687 phbd.c_ext_attr12 ,
2688 phbd.c_ext_attr13 ,
2689 phbd.c_ext_attr14 ,
2690 phbd.c_ext_attr15 ,
2691 phbd.c_ext_attr16 ,
2692 phbd.c_ext_attr17 ,
2693 phbd.c_ext_attr18 ,
2694 phbd.c_ext_attr19 ,
2695 phbd.c_ext_attr20 ,
2696 phbd.c_ext_attr21 ,
2697 phbd.c_ext_attr22 ,
2698 phbd.c_ext_attr23 ,
2699 phbd.c_ext_attr24 ,
2700 phbd.c_ext_attr25 ,
2701 phbd.c_ext_attr26 ,
2702 phbd.c_ext_attr27 ,
2703 phbd.c_ext_attr28 ,
2704 phbd.c_ext_attr29 ,
2705 phbd.c_ext_attr30 ,
2706 phbd.c_ext_attr31 ,
2707 phbd.c_ext_attr32 ,
2708 phbd.c_ext_attr33 ,
2709 phbd.c_ext_attr34 ,
2710 phbd.c_ext_attr35 ,
2711 phbd.c_ext_attr36 ,
2712 phbd.c_ext_attr37 ,
2713 phbd.c_ext_attr38 ,
2714 phbd.c_ext_attr39 ,
2715 phbd.c_ext_attr40 ,
2716 phbd.n_ext_attr1 ,
2717 phbd.n_ext_attr2 ,
2718 phbd.n_ext_attr3 ,
2719 phbd.n_ext_attr4 ,
2720 phbd.n_ext_attr5 ,
2721 phbd.n_ext_attr6 ,
2722 phbd.n_ext_attr7 ,
2723 phbd.n_ext_attr8 ,
2724 phbd.n_ext_attr9 ,
2725 phbd.n_ext_attr10 ,
2726 phbd.n_ext_attr11 ,
2727 phbd.n_ext_attr12 ,
2728 phbd.n_ext_attr13 ,
2729 phbd.n_ext_attr14 ,
2730 phbd.n_ext_attr15 ,
2731 phbd.n_ext_attr16 ,
2732 phbd.n_ext_attr17 ,
2733 phbd.n_ext_attr18 ,
2734 phbd.n_ext_attr19 ,
2735 phbd.n_ext_attr20 ,
2736 phbd.uom_ext_attr1 ,
2737 phbd.uom_ext_attr2 ,
2738 phbd.uom_ext_attr3 ,
2739 phbd.uom_ext_attr4 ,
2740 phbd.uom_ext_attr5 ,
2741 phbd.uom_ext_attr6 ,
2742 phbd.uom_ext_attr7 ,
2743 phbd.uom_ext_attr8 ,
2744 phbd.uom_ext_attr9 ,
2745 phbd.uom_ext_attr10 ,
2746 phbd.uom_ext_attr11 ,
2747 phbd.uom_ext_attr12 ,
2748 phbd.uom_ext_attr13 ,
2749 phbd.uom_ext_attr14 ,
2750 phbd.uom_ext_attr15 ,
2751 phbd.uom_ext_attr16 ,
2752 phbd.uom_ext_attr17 ,
2753 phbd.uom_ext_attr18 ,
2754 phbd.uom_ext_attr19 ,
2755 phbd.uom_ext_attr20 ,
2756 phbd.d_ext_attr1 ,
2757 phbd.d_ext_attr2 ,
2758 phbd.d_ext_attr3 ,
2759 phbd.d_ext_attr4 ,
2760 phbd.d_ext_attr5 ,
2761 phbd.d_ext_attr6 ,
2762 phbd.d_ext_attr7 ,
2763 phbd.d_ext_attr8 ,
2764 phbd.d_ext_attr9 ,
2765 phbd.d_ext_attr10 ,
2766 phbd.uda_template_id ,
2767 phbd.draft_id ,
2768 phbd.revision_num )
2769 VALUES
2770 (EGO_EXTFWK_S.NEXTVAL ,
2771 phb.attr_group_id ,
2772 phb.po_header_id ,
2773 phb.data_level_id ,
2774 phb.pk1_value ,
2775 phb.pk2_value ,
2776 phb.pk3_value ,
2777 phb.pk4_value ,
2778 phb.pk5_value ,
2779 phb.last_update_date ,
2780 phb.last_updated_by ,
2781 phb.last_update_login ,
2782 phb.created_by ,
2783 phb.creation_date ,
2784 phb.c_ext_attr1 ,
2785 phb.c_ext_attr2 ,
2786 phb.c_ext_attr3 ,
2787 phb.c_ext_attr4 ,
2788 phb.c_ext_attr5 ,
2789 phb.c_ext_attr6 ,
2790 phb.c_ext_attr7 ,
2791 phb.c_ext_attr8 ,
2792 phb.c_ext_attr9 ,
2793 phb.c_ext_attr10 ,
2794 phb.c_ext_attr11 ,
2795 phb.c_ext_attr12 ,
2796 phb.c_ext_attr13 ,
2797 phb.c_ext_attr14 ,
2798 phb.c_ext_attr15 ,
2799 phb.c_ext_attr16 ,
2800 phb.c_ext_attr17 ,
2801 phb.c_ext_attr18 ,
2802 phb.c_ext_attr19 ,
2803 phb.c_ext_attr20 ,
2804 phb.c_ext_attr21 ,
2805 phb.c_ext_attr22 ,
2806 phb.c_ext_attr23 ,
2807 phb.c_ext_attr24 ,
2808 phb.c_ext_attr25 ,
2809 phb.c_ext_attr26 ,
2810 phb.c_ext_attr27 ,
2811 phb.c_ext_attr28 ,
2812 phb.c_ext_attr29 ,
2813 phb.c_ext_attr30 ,
2814 phb.c_ext_attr31 ,
2815 phb.c_ext_attr32 ,
2816 phb.c_ext_attr33 ,
2817 phb.c_ext_attr34 ,
2818 phb.c_ext_attr35 ,
2819 phb.c_ext_attr36 ,
2820 phb.c_ext_attr37 ,
2821 phb.c_ext_attr38 ,
2822 phb.c_ext_attr39 ,
2823 phb.c_ext_attr40 ,
2824 phb.n_ext_attr1 ,
2825 phb.n_ext_attr2 ,
2826 phb.n_ext_attr3 ,
2827 phb.n_ext_attr4 ,
2828 phb.n_ext_attr5 ,
2829 phb.n_ext_attr6 ,
2830 phb.n_ext_attr7 ,
2831 phb.n_ext_attr8 ,
2832 phb.n_ext_attr9 ,
2833 phb.n_ext_attr10 ,
2834 phb.n_ext_attr11 ,
2835 phb.n_ext_attr12 ,
2836 phb.n_ext_attr13 ,
2837 phb.n_ext_attr14 ,
2838 phb.n_ext_attr15 ,
2839 phb.n_ext_attr16 ,
2840 phb.n_ext_attr17 ,
2841 phb.n_ext_attr18 ,
2842 phb.n_ext_attr19 ,
2843 phb.n_ext_attr20 ,
2844 phb.uom_ext_attr1 ,
2845 phb.uom_ext_attr2 ,
2846 phb.uom_ext_attr3 ,
2847 phb.uom_ext_attr4 ,
2848 phb.uom_ext_attr5 ,
2849 phb.uom_ext_attr6 ,
2850 phb.uom_ext_attr7 ,
2851 phb.uom_ext_attr8 ,
2852 phb.uom_ext_attr9 ,
2853 phb.uom_ext_attr10 ,
2854 phb.uom_ext_attr11 ,
2855 phb.uom_ext_attr12 ,
2856 phb.uom_ext_attr13 ,
2857 phb.uom_ext_attr14 ,
2858 phb.uom_ext_attr15 ,
2859 phb.uom_ext_attr16 ,
2860 phb.uom_ext_attr17 ,
2861 phb.uom_ext_attr18 ,
2862 phb.uom_ext_attr19 ,
2863 phb.uom_ext_attr20 ,
2864 phb.d_ext_attr1 ,
2865 phb.d_ext_attr2 ,
2866 phb.d_ext_attr3 ,
2867 phb.d_ext_attr4 ,
2868 phb.d_ext_attr5 ,
2869 phb.d_ext_attr6 ,
2870 phb.d_ext_attr7 ,
2871 phb.d_ext_attr8 ,
2872 phb.d_ext_attr9 ,
2873 phb.d_ext_attr10 ,
2874 phb.uda_template_id ,
2875 p_draft_id ,
2876 phb.revision_num );
2877
2878
2879 d_position := 30;
2880 IF (PO_LOG.d_stmt) THEN
2881 PO_LOG.stmt(d_module,d_position ,'Updated po_headers_all_ext_b for Single Row attr grp and c_ext_attr40 not in PO_UDA_ADDRESS_TYPES');
2882 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
2883 END IF;
2884
2885 -- Update multi Row header Ext n rev num
2886 DELETE FROM po_headers_all_ext_b pheb
2887 WHERE pheb.po_header_id=l_po_header_id
2888 AND pheb.draft_id = p_draft_id
2889 AND (pheb.c_ext_attr40 NOT IN ('PO_UDA_ADDRESS_TYPES', 'PO_MOD_UDA_ADDRESS_TYPES')
2890 OR pheb.c_ext_attr40 IS NULL)
2891 AND NOT EXISTS (SELECT 'only S or Null Locks'
2892 FROM po_entity_locks pel
2893 WHERE pel.lock_type IN ('P','F')
2894 AND pel.entity_pk1= pheb.po_header_id
2895 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
2896 AND pel.lock_by_draft_id = p_draft_id )
2897 AND EXISTS (SELECT 'if multi row attr grp'
2898 FROM ego_fnd_dsc_flx_ctx_ext ag
2899 WHERE pheb.attr_group_id = ag.attr_group_id
2900 AND ag.multi_row = 'Y');
2901 d_position := 40;
2902 IF (PO_LOG.d_stmt) THEN
2903 PO_LOG.stmt(d_module,d_position ,'Deleted from po_headers_all_ext_b for Multi Row attr grp and c_ext_attr40 not in PO_UDA_ADDRESS_TYPES');
2904 END IF;
2905
2906 INSERT INTO po_headers_all_ext_b phbd
2907 ( phbd.extension_id ,
2908 phbd.attr_group_id ,
2909 phbd.po_header_id ,
2910 phbd.data_level_id ,
2911 phbd.pk1_value ,
2912 phbd.pk2_value ,
2913 phbd.pk3_value ,
2914 phbd.pk4_value ,
2915 phbd.pk5_value ,
2916 phbd.last_update_date,
2917 phbd.last_updated_by ,
2918 phbd.last_update_login ,
2919 phbd.created_by ,
2920 phbd.creation_date,
2921 phbd.c_ext_attr1,
2922 phbd.c_ext_attr2 ,
2923 phbd.c_ext_attr3 ,
2924 phbd.c_ext_attr4 ,
2925 phbd.c_ext_attr5 ,
2926 phbd.c_ext_attr6 ,
2927 phbd.c_ext_attr7 ,
2928 phbd.c_ext_attr8 ,
2929 phbd.c_ext_attr9 ,
2930 phbd.c_ext_attr10 ,
2931 phbd.c_ext_attr11 ,
2932 phbd.c_ext_attr12 ,
2933 phbd.c_ext_attr13 ,
2934 phbd.c_ext_attr14 ,
2935 phbd.c_ext_attr15 ,
2936 phbd.c_ext_attr16 ,
2937 phbd.c_ext_attr17 ,
2938 phbd.c_ext_attr18 ,
2939 phbd.c_ext_attr19 ,
2940 phbd.c_ext_attr20 ,
2941 phbd.c_ext_attr21 ,
2942 phbd.c_ext_attr22 ,
2943 phbd.c_ext_attr23 ,
2944 phbd.c_ext_attr24 ,
2945 phbd.c_ext_attr25 ,
2946 phbd.c_ext_attr26 ,
2947 phbd.c_ext_attr27 ,
2948 phbd.c_ext_attr28 ,
2949 phbd.c_ext_attr29 ,
2950 phbd.c_ext_attr30 ,
2951 phbd.c_ext_attr31 ,
2952 phbd.c_ext_attr32 ,
2953 phbd.c_ext_attr33 ,
2954 phbd.c_ext_attr34 ,
2955 phbd.c_ext_attr35 ,
2956 phbd.c_ext_attr36 ,
2957 phbd.c_ext_attr37 ,
2958 phbd.c_ext_attr38 ,
2959 phbd.c_ext_attr39 ,
2960 phbd.c_ext_attr40 ,
2961 phbd.n_ext_attr1 ,
2962 phbd.n_ext_attr2 ,
2963 phbd.n_ext_attr3 ,
2964 phbd.n_ext_attr4 ,
2965 phbd.n_ext_attr5 ,
2966 phbd.n_ext_attr6 ,
2967 phbd.n_ext_attr7 ,
2968 phbd.n_ext_attr8 ,
2969 phbd.n_ext_attr9 ,
2970 phbd.n_ext_attr10 ,
2971 phbd.n_ext_attr11 ,
2972 phbd.n_ext_attr12 ,
2973 phbd.n_ext_attr13 ,
2974 phbd.n_ext_attr14 ,
2975 phbd.n_ext_attr15 ,
2976 phbd.n_ext_attr16 ,
2977 phbd.n_ext_attr17 ,
2978 phbd.n_ext_attr18 ,
2979 phbd.n_ext_attr19 ,
2980 phbd.n_ext_attr20 ,
2981 phbd.uom_ext_attr1 ,
2982 phbd.uom_ext_attr2 ,
2983 phbd.uom_ext_attr3 ,
2984 phbd.uom_ext_attr4,
2985 phbd.uom_ext_attr5 ,
2986 phbd.uom_ext_attr6 ,
2987 phbd.uom_ext_attr7 ,
2988 phbd.uom_ext_attr8 ,
2989 phbd.uom_ext_attr9 ,
2990 phbd.uom_ext_attr10 ,
2991 phbd.uom_ext_attr11 ,
2992 phbd.uom_ext_attr12 ,
2993 phbd.uom_ext_attr13 ,
2994 phbd.uom_ext_attr14 ,
2995 phbd.uom_ext_attr15 ,
2996 phbd.uom_ext_attr16 ,
2997 phbd.uom_ext_attr17 ,
2998 phbd.uom_ext_attr18 ,
2999 phbd.uom_ext_attr19 ,
3000 phbd.uom_ext_attr20 ,
3001 phbd.d_ext_attr1 ,
3002 phbd.d_ext_attr2 ,
3003 phbd.d_ext_attr3 ,
3004 phbd.d_ext_attr4 ,
3005 phbd.d_ext_attr5 ,
3006 phbd.d_ext_attr6 ,
3007 phbd.d_ext_attr7 ,
3008 phbd.d_ext_attr8 ,
3009 phbd.d_ext_attr9 ,
3010 phbd.d_ext_attr10,
3011 phbd.uda_template_id ,
3012 phbd.draft_id ,
3013 phbd.revision_num )
3014 SELECT
3015 EGO_EXTFWK_S.NEXTVAL ,
3016 phb.attr_group_id ,
3017 phb.po_header_id ,
3018 phb.data_level_id ,
3019 phb.pk1_value ,
3020 phb.pk2_value ,
3021 phb.pk3_value ,
3022 phb.pk4_value ,
3023 phb.pk5_value ,
3024 phb.last_update_date,
3025 phb.last_updated_by ,
3026 phb.last_update_login ,
3027 phb.created_by ,
3028 phb.creation_date,
3029 phb.c_ext_attr1,
3030 phb.c_ext_attr2 ,
3031 phb.c_ext_attr3 ,
3032 phb.c_ext_attr4 ,
3033 phb.c_ext_attr5 ,
3034 phb.c_ext_attr6 ,
3035 phb.c_ext_attr7 ,
3036 phb.c_ext_attr8 ,
3037 phb.c_ext_attr9 ,
3038 phb.c_ext_attr10 ,
3039 phb.c_ext_attr11 ,
3040 phb.c_ext_attr12 ,
3041 phb.c_ext_attr13 ,
3042 phb.c_ext_attr14 ,
3043 phb.c_ext_attr15 ,
3044 phb.c_ext_attr16 ,
3045 phb.c_ext_attr17 ,
3046 phb.c_ext_attr18 ,
3047 phb.c_ext_attr19 ,
3048 phb.c_ext_attr20 ,
3049 phb.c_ext_attr21 ,
3050 phb.c_ext_attr22 ,
3051 phb.c_ext_attr23 ,
3052 phb.c_ext_attr24 ,
3053 phb.c_ext_attr25 ,
3054 phb.c_ext_attr26 ,
3055 phb.c_ext_attr27 ,
3056 phb.c_ext_attr28 ,
3057 phb.c_ext_attr29 ,
3058 phb.c_ext_attr30 ,
3059 phb.c_ext_attr31 ,
3060 phb.c_ext_attr32 ,
3061 phb.c_ext_attr33 ,
3062 phb.c_ext_attr34 ,
3063 phb.c_ext_attr35 ,
3064 phb.c_ext_attr36 ,
3065 phb.c_ext_attr37 ,
3066 phb.c_ext_attr38 ,
3067 phb.c_ext_attr39 ,
3068 phb.c_ext_attr40 ,
3069 phb.n_ext_attr1 ,
3070 phb.n_ext_attr2 ,
3071 phb.n_ext_attr3 ,
3072 phb.n_ext_attr4 ,
3073 phb.n_ext_attr5 ,
3074 phb.n_ext_attr6 ,
3075 phb.n_ext_attr7 ,
3076 phb.n_ext_attr8 ,
3077 phb.n_ext_attr9 ,
3078 phb.n_ext_attr10 ,
3079 phb.n_ext_attr11 ,
3080 phb.n_ext_attr12 ,
3081 phb.n_ext_attr13 ,
3082 phb.n_ext_attr14 ,
3083 phb.n_ext_attr15 ,
3084 phb.n_ext_attr16 ,
3085 phb.n_ext_attr17 ,
3086 phb.n_ext_attr18 ,
3087 phb.n_ext_attr19 ,
3088 phb.n_ext_attr20 ,
3089 phb.uom_ext_attr1 ,
3090 phb.uom_ext_attr2 ,
3091 phb.uom_ext_attr3 ,
3092 phb.uom_ext_attr4,
3093 phb.uom_ext_attr5 ,
3094 phb.uom_ext_attr6 ,
3095 phb.uom_ext_attr7 ,
3096 phb.uom_ext_attr8 ,
3097 phb.uom_ext_attr9 ,
3098 phb.uom_ext_attr10 ,
3099 phb.uom_ext_attr11 ,
3100 phb.uom_ext_attr12 ,
3101 phb.uom_ext_attr13 ,
3102 phb.uom_ext_attr14 ,
3103 phb.uom_ext_attr15 ,
3104 phb.uom_ext_attr16 ,
3105 phb.uom_ext_attr17 ,
3106 phb.uom_ext_attr18 ,
3107 phb.uom_ext_attr19 ,
3108 phb.uom_ext_attr20 ,
3109 phb.d_ext_attr1 ,
3110 phb.d_ext_attr2 ,
3111 phb.d_ext_attr3 ,
3112 phb.d_ext_attr4 ,
3113 phb.d_ext_attr5 ,
3114 phb.d_ext_attr6 ,
3115 phb.d_ext_attr7 ,
3116 phb.d_ext_attr8 ,
3117 phb.d_ext_attr9 ,
3118 phb.d_ext_attr10,
3119 phb.uda_template_id ,
3120 p_draft_id ,
3121 phb.revision_num
3122 FROM po_headers_all_ext_b phb --base
3123 WHERE phb.draft_id = -1
3124 AND phb.po_header_id = l_po_header_id
3125 AND (phb.c_ext_attr40 NOT IN ('PO_UDA_ADDRESS_TYPES', 'PO_MOD_UDA_ADDRESS_TYPES')
3126 OR phb.c_ext_attr40 IS NULL)
3127 AND NOT EXISTS (SELECT 'only S or Null Locks'
3128 FROM po_entity_locks pel
3129 WHERE pel.lock_type IN ('P','F')
3130 AND pel.entity_pk1= phb.po_header_id
3131 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
3132 AND pel.lock_by_draft_id = p_draft_id )
3133 AND EXISTS (SELECT 'Multi row attr grp'
3134 FROM ego_fnd_dsc_flx_ctx_ext ag
3135 WHERE phb.attr_group_id = ag.attr_group_id
3136 AND ag.multi_row = 'Y');
3137
3138 d_position :=60;
3139 IF (PO_LOG.d_stmt) THEN
3140 PO_LOG.stmt(d_module,d_position ,'Inserted Into po_headers_all_ext_b for Multi Row attr grp from base tables');
3141 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3142 END IF;
3143
3144
3145 SELECT b.extension_id
3146 BULK COLLECT
3147 INTO l_entension_id_tbl
3148 FROM po_headers_all_ext_b b
3149 WHERE draft_id = p_draft_id
3150 AND po_header_id = l_po_header_id
3151 AND NOT EXISTS (SELECT 'tl table record exists'
3152 FROM po_headers_all_ext_tl tl
3153 WHERE b.extension_id = tl.extension_id);
3154
3155 SELECT language_code
3156 INTO l_base_lang
3157 FROM fnd_languages
3158 WHERE installed_flag ='B';
3159
3160
3161 DELETE FROM po_headers_all_ext_tl phaet
3162 WHERE phaet.po_header_id=l_po_header_id
3163 AND phaet.draft_id = p_draft_id
3164 AND EXISTS (SELECT 'if multi row attr grp'
3165 FROM ego_fnd_dsc_flx_ctx_ext ag
3166 WHERE phaet.attr_group_id = ag.attr_group_id
3167 AND ag.multi_row = 'Y')
3168 AND NOT EXISTS (SELECT 'only S or Null Locks'
3169 FROM po_entity_locks pel
3170 WHERE pel.lock_type IN ('P','F')
3171 AND pel.entity_pk1= phaet.po_header_id
3172 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
3173 AND pel.lock_by_draft_id = p_draft_id )
3174 AND NOT EXISTS (SELECT 'Exclude Uda attributes'
3175 FROM po_headers_all_ext_b phb
3176 WHERE PHB.EXTENSION_ID = phaet.EXTENSION_ID
3177 AND phb.c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
3178 );
3179
3180 FORALL i IN 1 .. l_entension_id_tbl.COUNT
3181 INSERT INTO po_headers_all_ext_tl tl
3182 ( tl.extension_id,
3183 tl.attr_group_id,
3184 tl.po_header_id,
3185 tl.source_lang,
3186 tl.language,
3187 tl.data_level_id,
3188 tl.pk1_value,
3189 tl.pk2_value,
3190 tl.pk3_value,
3191 tl.pk4_value,
3192 tl.pk5_value,
3193 tl.last_update_date,
3194 tl.last_updated_by ,
3195 tl.last_update_login,
3196 tl.created_by,
3197 tl.creation_date ,
3198 tl.uda_template_id,
3199 tl.draft_id
3200 )
3201 SELECT l_entension_id_tbl(i),
3202 b.attr_group_id,
3203 b.po_header_id,
3204 l_base_lang,
3205 l.language_code,
3206 b.data_level_id,
3207 b.pk1_value,
3208 b.pk2_value,
3209 b.pk3_value,
3210 b.pk4_value,
3211 b.pk5_value,
3212 b.last_update_date,
3213 b.last_updated_by ,
3214 b.last_update_login,
3215 b.created_by,
3216 b.creation_date ,
3217 b.uda_template_id,
3218 p_draft_id
3219 FROM po_headers_all_ext_b b,
3220 fnd_languages l
3221 WHERE b.extension_id = l_entension_id_tbl(i)
3222 AND l.installed_flag IN ('B', 'I')
3223 ;
3224
3225 d_position :=70;
3226 IF (PO_LOG.d_stmt) THEN
3227 PO_LOG.stmt(d_module,d_position ,'Updated po_headers_all_ext_tl for S or No locks');
3228 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3229 END IF;
3230
3231 d_position := 80;
3232 IF (PO_LOG.d_proc) THEN
3233 PO_LOG.proc_end(d_module);
3234 END IF;
3235
3236 EXCEPTION
3237 WHEN others THEN
3238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3239 IF (PO_LOG.d_exc) THEN
3240 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
3241 END IF;
3242 RAISE;
3243 END sync_headers_ext;
3244
3245
3246 --------------------------------------------------------------------------------
3247 --Start of Comments
3248 --Name: sync_lines_ext
3249 -- <Conc Mods Project>
3250 --Pre-reqs:
3251 -- none
3252 --Modifies: None
3253 --Function:
3254 -- This procedure syncs the all line_ext entities of Mod with the corresponding
3255 -- Base line_ext entites
3256 --Parameters:
3257 --IN:
3258 --p_draft_id
3259 -- draft_id of the Modification
3260 --IN OUT:
3261 --OUT:
3262 --Notes:
3263 --End of Comments
3264 --------------------------------------------------------------------------------
3265
3266 PROCEDURE sync_lines_ext(
3267 p_draft_id IN NUMBER
3268 ,x_return_status OUT NOCOPY VARCHAR2
3269 ,p_po_line_s_no_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
3270 ,p_po_line_s_no_p_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
3271 ) IS
3272
3273 d_api_name CONSTANT VARCHAR2(30) := 'sync_lines_ext';
3274 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3275 d_position NUMBER;
3276 d_api_version CONSTANT NUMBER := 1.0;
3277 BEGIN
3278
3279 --<Bug 16671702> : Will use p_po_line_s_no_lck_tbl instead of l_po_line_s_no_lck_tbl
3280 --Also replace l_po_line_s_no_p_lck_tbl with p_po_line_s_no_p_lck_tbl
3281
3282 d_position := 0;
3283 IF (PO_LOG.d_proc) THEN
3284 PO_LOG.proc_begin(d_module);
3285 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3286 END IF;
3287
3288 --<Bug 16671702> : Cleaning up. p_po_line_s_no_lck_tbl already passed as param to api
3289
3290 d_position := 20;
3291 IF (PO_LOG.d_stmt) THEN
3292 PO_LOG.stmt(d_module,d_position ,'po_line_id for S and No locks excluding comlex pricing attr', p_po_line_s_no_lck_tbl);
3293 END IF;
3294
3295 FORALL i in 1..p_po_line_s_no_lck_tbl.COUNT
3296 UPDATE po_lines_all_ext_b plbd
3297 SET ( plbd.c_ext_attr1,
3298 plbd.c_ext_attr2 ,
3299 plbd.c_ext_attr3 ,
3300 plbd.c_ext_attr4 ,
3301 plbd.c_ext_attr5 ,
3302 plbd.c_ext_attr6 ,
3303 plbd.c_ext_attr7 ,
3304 plbd.c_ext_attr8 ,
3305 plbd.c_ext_attr9 ,
3306 plbd.c_ext_attr10 ,
3307 plbd.c_ext_attr11 ,
3308 plbd.c_ext_attr12 ,
3309 plbd.c_ext_attr13 ,
3310 plbd.c_ext_attr14 ,
3311 plbd.c_ext_attr15 ,
3312 plbd.c_ext_attr16 ,
3313 plbd.c_ext_attr17 ,
3314 plbd.c_ext_attr18 ,
3315 plbd.c_ext_attr19 ,
3316 plbd.c_ext_attr20 ,
3317 plbd.c_ext_attr21 ,
3318 plbd.c_ext_attr22 ,
3319 plbd.c_ext_attr23 ,
3320 plbd.c_ext_attr24 ,
3321 plbd.c_ext_attr25 ,
3322 plbd.c_ext_attr26 ,
3323 plbd.c_ext_attr27 ,
3324 plbd.c_ext_attr28 ,
3325 plbd.c_ext_attr29 ,
3326 plbd.c_ext_attr30 ,
3327 plbd.c_ext_attr31 ,
3328 plbd.c_ext_attr32 ,
3329 plbd.c_ext_attr33 ,
3330 plbd.c_ext_attr34 ,
3331 plbd.c_ext_attr35 ,
3332 plbd.c_ext_attr36 ,
3333 plbd.c_ext_attr37 ,
3334 plbd.c_ext_attr38 ,
3335 plbd.c_ext_attr39 ,
3336 plbd.c_ext_attr40 ,
3337 plbd.n_ext_attr1 ,
3338 plbd.n_ext_attr2 ,
3339 plbd.n_ext_attr3 ,
3340 plbd.n_ext_attr4 ,
3341 plbd.n_ext_attr5 ,
3342 plbd.n_ext_attr6 ,
3343 plbd.n_ext_attr7 ,
3344 plbd.n_ext_attr8 ,
3345 plbd.n_ext_attr9 ,
3346 plbd.n_ext_attr10 ,
3347 plbd.n_ext_attr11 ,
3348 plbd.n_ext_attr12 ,
3349 plbd.n_ext_attr13 ,
3350 plbd.n_ext_attr14 ,
3351 plbd.n_ext_attr15 ,
3352 plbd.n_ext_attr16 ,
3353 plbd.n_ext_attr17 ,
3354 plbd.n_ext_attr18 ,
3355 plbd.n_ext_attr19 ,
3356 plbd.n_ext_attr20 ,
3357 plbd.uom_ext_attr1 ,
3358 plbd.uom_ext_attr2 ,
3359 plbd.uom_ext_attr3 ,
3360 plbd.uom_ext_attr4,
3361 plbd.uom_ext_attr5 ,
3362 plbd.uom_ext_attr6 ,
3363 plbd.uom_ext_attr7 ,
3364 plbd.uom_ext_attr8 ,
3365 plbd.uom_ext_attr9 ,
3366 plbd.uom_ext_attr10 ,
3367 plbd.uom_ext_attr11 ,
3368 plbd.uom_ext_attr12 ,
3369 plbd.uom_ext_attr13 ,
3370 plbd.uom_ext_attr14 ,
3371 plbd.uom_ext_attr15 ,
3372 plbd.uom_ext_attr16 ,
3373 plbd.uom_ext_attr17 ,
3374 plbd.uom_ext_attr18 ,
3375 plbd.uom_ext_attr19 ,
3376 plbd.uom_ext_attr20 ,
3377 plbd.d_ext_attr1 ,
3378 plbd.d_ext_attr2 ,
3379 plbd.d_ext_attr3 ,
3380 plbd.d_ext_attr4 ,
3381 plbd.d_ext_attr5 ,
3382 plbd.d_ext_attr6 ,
3383 plbd.d_ext_attr7 ,
3384 plbd.d_ext_attr8 ,
3385 plbd.d_ext_attr9 ,
3386 plbd.d_ext_attr10
3387 )
3388 = (SELECT
3389 plb.c_ext_attr1,
3390 plb.c_ext_attr2 ,
3391 plb.c_ext_attr3 ,
3392 plb.c_ext_attr4 ,
3393 plb.c_ext_attr5 ,
3394 plb.c_ext_attr6 ,
3395 plb.c_ext_attr7 ,
3396 plb.c_ext_attr8 ,
3397 plb.c_ext_attr9 ,
3398 plb.c_ext_attr10 ,
3399 plb.c_ext_attr11 ,
3400 plb.c_ext_attr12 ,
3401 plb.c_ext_attr13 ,
3402 plb.c_ext_attr14 ,
3403 plb.c_ext_attr15 ,
3404 plb.c_ext_attr16 ,
3405 plb.c_ext_attr17 ,
3406 plb.c_ext_attr18 ,
3407 plb.c_ext_attr19 ,
3408 plb.c_ext_attr20 ,
3409 plb.c_ext_attr21 ,
3410 plb.c_ext_attr22 ,
3411 plb.c_ext_attr23 ,
3412 plb.c_ext_attr24 ,
3413 plb.c_ext_attr25 ,
3414 plb.c_ext_attr26 ,
3415 plb.c_ext_attr27 ,
3416 plb.c_ext_attr28 ,
3417 plb.c_ext_attr29 ,
3418 plb.c_ext_attr30 ,
3419 plb.c_ext_attr31 ,
3420 plb.c_ext_attr32 ,
3421 plb.c_ext_attr33 ,
3422 plb.c_ext_attr34 ,
3423 plb.c_ext_attr35 ,
3424 plb.c_ext_attr36 ,
3425 plb.c_ext_attr37 ,
3426 plb.c_ext_attr38 ,
3427 plb.c_ext_attr39 ,
3428 plb.c_ext_attr40 ,
3429 plb.n_ext_attr1 ,
3430 plb.n_ext_attr2 ,
3431 plb.n_ext_attr3 ,
3432 plb.n_ext_attr4 ,
3433 plb.n_ext_attr5 ,
3434 plb.n_ext_attr6 ,
3435 plb.n_ext_attr7 ,
3436 plb.n_ext_attr8 ,
3437 plb.n_ext_attr9 ,
3438 plb.n_ext_attr10 ,
3439 plb.n_ext_attr11 ,
3440 plb.n_ext_attr12 ,
3441 plb.n_ext_attr13 ,
3442 plb.n_ext_attr14 ,
3443 plb.n_ext_attr15 ,
3444 plb.n_ext_attr16 ,
3445 plb.n_ext_attr17 ,
3446 plb.n_ext_attr18 ,
3447 plb.n_ext_attr19 ,
3448 plb.n_ext_attr20 ,
3449 plb.uom_ext_attr1 ,
3450 plb.uom_ext_attr2 ,
3451 plb.uom_ext_attr3 ,
3452 plb.uom_ext_attr4 ,
3453 plb.uom_ext_attr5 ,
3454 plb.uom_ext_attr6 ,
3455 plb.uom_ext_attr7 ,
3456 plb.uom_ext_attr8 ,
3457 plb.uom_ext_attr9 ,
3458 plb.uom_ext_attr10 ,
3459 plb.uom_ext_attr11 ,
3460 plb.uom_ext_attr12 ,
3461 plb.uom_ext_attr13 ,
3462 plb.uom_ext_attr14 ,
3463 plb.uom_ext_attr15 ,
3464 plb.uom_ext_attr16 ,
3465 plb.uom_ext_attr17 ,
3466 plb.uom_ext_attr18 ,
3467 plb.uom_ext_attr19 ,
3468 plb.uom_ext_attr20 ,
3469 plb.d_ext_attr1 ,
3470 plb.d_ext_attr2 ,
3471 plb.d_ext_attr3 ,
3472 plb.d_ext_attr4 ,
3473 plb.d_ext_attr5 ,
3474 plb.d_ext_attr6 ,
3475 plb.d_ext_attr7 ,
3476 plb.d_ext_attr8 ,
3477 plb.d_ext_attr9 ,
3478 plb.d_ext_attr10
3479 FROM po_lines_all_ext_b plb --base
3480 WHERE plb.po_line_id = p_po_line_s_no_lck_tbl(i)
3481 AND plb.draft_id = -1
3482 AND plb.attr_group_id = plbd.attr_group_id
3483 )
3484 WHERE plbd.po_line_id = p_po_line_s_no_lck_tbl(i)
3485 AND plbd.draft_id = p_draft_id
3486 AND NOT EXISTS (SELECT 'exclude PRICING attribute category'
3487 FROM po_uda_ag_template_usages puatu,
3488 po_lines_all pol
3489 WHERE puatu.template_id = pol.uda_template_id
3490 AND plbd.po_line_id = pol.po_line_id
3491 AND puatu.attribute_category = 'PRICING'
3492 AND puatu.attribute_group_id = plbd.attr_group_id
3493 );
3494
3495 d_position := 40;
3496 IF (PO_LOG.d_stmt) THEN
3497 PO_LOG.stmt(d_module,d_position ,'Updated po_lines_all_ext_b for S and No locks');
3498 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3499 END IF;
3500
3501 --<Bug 16671702> : Cleaning up. p_po_line_s_no_p_lck_tblalready passed as param to this api.
3502
3503 d_position := 40;
3504 IF (PO_LOG.d_stmt) THEN
3505 PO_LOG.stmt(d_module,d_position ,'po_line_id for S, No and P locks including comlex pricing attr', p_po_line_s_no_p_lck_tbl);
3506 END IF;
3507
3508 -- We need 3 update statement
3509 -- 1st update old value records for S lock, P lock and no lock lines
3510 FORALL i in 1..p_po_line_s_no_p_lck_tbl.COUNT
3511 UPDATE po_lines_all_ext_b plbd
3512 SET ( plbd.c_ext_attr1,
3513 plbd.c_ext_attr2 ,
3514 plbd.c_ext_attr3 ,
3515 plbd.c_ext_attr4 ,
3516 plbd.c_ext_attr5 ,
3517 plbd.c_ext_attr6 ,
3518 plbd.c_ext_attr7 ,
3519 plbd.c_ext_attr8 ,
3520 plbd.c_ext_attr9 ,
3521 plbd.c_ext_attr10 ,
3522 plbd.c_ext_attr11 ,
3523 plbd.c_ext_attr12 ,
3524 plbd.c_ext_attr13 ,
3525 plbd.c_ext_attr14 ,
3526 plbd.c_ext_attr15 ,
3527 plbd.c_ext_attr16 ,
3528 plbd.c_ext_attr17 ,
3529 plbd.c_ext_attr18 ,
3530 plbd.c_ext_attr19 ,
3531 plbd.c_ext_attr20 ,
3532 plbd.c_ext_attr21 ,
3533 plbd.c_ext_attr22 ,
3534 plbd.c_ext_attr23 ,
3535 plbd.c_ext_attr24 ,
3536 plbd.c_ext_attr25 ,
3537 plbd.c_ext_attr26 ,
3538 plbd.c_ext_attr27 ,
3539 plbd.c_ext_attr28 ,
3540 plbd.c_ext_attr29 ,
3541 plbd.c_ext_attr30 ,
3542 plbd.c_ext_attr31 ,
3543 plbd.c_ext_attr32 ,
3544 plbd.c_ext_attr33 ,
3545 plbd.c_ext_attr34 ,
3546 plbd.c_ext_attr35 ,
3547 plbd.c_ext_attr36 ,
3548 plbd.c_ext_attr37 ,
3549 plbd.c_ext_attr38 ,
3550 plbd.c_ext_attr39 ,
3551 plbd.c_ext_attr40 ,
3552 plbd.n_ext_attr1 ,
3553 plbd.n_ext_attr2 ,
3554 plbd.n_ext_attr3 ,
3555 plbd.n_ext_attr4 ,
3556 plbd.n_ext_attr5 ,
3557 plbd.n_ext_attr6 ,
3558 plbd.n_ext_attr7 ,
3559 plbd.n_ext_attr8 ,
3560 plbd.n_ext_attr9 ,
3561 plbd.n_ext_attr10 ,
3562 plbd.n_ext_attr11 ,
3563 plbd.n_ext_attr12 ,
3564 plbd.n_ext_attr13 ,
3565 plbd.n_ext_attr14 ,
3566 plbd.n_ext_attr15 ,
3567 plbd.n_ext_attr16 ,
3568 plbd.n_ext_attr17 ,
3569 plbd.n_ext_attr18 ,
3570 plbd.n_ext_attr19 ,
3571 plbd.n_ext_attr20 ,
3572 plbd.uom_ext_attr1 ,
3573 plbd.uom_ext_attr2 ,
3574 plbd.uom_ext_attr3 ,
3575 plbd.uom_ext_attr4,
3576 plbd.uom_ext_attr5 ,
3577 plbd.uom_ext_attr6 ,
3578 plbd.uom_ext_attr7 ,
3579 plbd.uom_ext_attr8 ,
3580 plbd.uom_ext_attr9 ,
3581 plbd.uom_ext_attr10 ,
3582 plbd.uom_ext_attr11 ,
3583 plbd.uom_ext_attr12 ,
3584 plbd.uom_ext_attr13 ,
3585 plbd.uom_ext_attr14 ,
3586 plbd.uom_ext_attr15 ,
3587 plbd.uom_ext_attr16 ,
3588 plbd.uom_ext_attr17 ,
3589 plbd.uom_ext_attr18 ,
3590 plbd.uom_ext_attr19 ,
3591 plbd.uom_ext_attr20 ,
3592 plbd.d_ext_attr1 ,
3593 plbd.d_ext_attr2 ,
3594 plbd.d_ext_attr3 ,
3595 plbd.d_ext_attr4 ,
3596 plbd.d_ext_attr5 ,
3597 plbd.d_ext_attr6 ,
3598 plbd.d_ext_attr7 ,
3599 plbd.d_ext_attr8 ,
3600 plbd.d_ext_attr9 ,
3601 plbd.d_ext_attr10 )
3602 = (SELECT
3603 plb.c_ext_attr1,
3604 plb.c_ext_attr2 ,
3605 plb.c_ext_attr3 ,
3606 plb.c_ext_attr4 ,
3607 plb.c_ext_attr5 ,
3608 plb.c_ext_attr6 ,
3609 plb.c_ext_attr7 ,
3610 plb.c_ext_attr8 ,
3611 plb.c_ext_attr9 ,
3612 plb.c_ext_attr10 ,
3613 plb.c_ext_attr11 ,
3614 plb.c_ext_attr12 ,
3615 plb.c_ext_attr13 ,
3616 plb.c_ext_attr14 ,
3617 plb.c_ext_attr15 ,
3618 plb.c_ext_attr16 ,
3619 plb.c_ext_attr17 ,
3620 plb.c_ext_attr18 ,
3621 plb.c_ext_attr19 ,
3622 plb.c_ext_attr20 ,
3623 plb.c_ext_attr21 ,
3624 plb.c_ext_attr22 ,
3625 plb.c_ext_attr23 ,
3626 plb.c_ext_attr24 ,
3627 plb.c_ext_attr25 ,
3628 plb.c_ext_attr26 ,
3629 plb.c_ext_attr27 ,
3630 plb.c_ext_attr28 ,
3631 plb.c_ext_attr29 ,
3632 plb.c_ext_attr30 ,
3633 plb.c_ext_attr31 ,
3634 plb.c_ext_attr32 ,
3635 plb.c_ext_attr33 ,
3636 plb.c_ext_attr34 ,
3637 plb.c_ext_attr35 ,
3638 plb.c_ext_attr36 ,
3639 plb.c_ext_attr37 ,
3640 plb.c_ext_attr38 ,
3641 plb.c_ext_attr39 ,
3642 plb.c_ext_attr40 ,
3643 plb.n_ext_attr1 ,
3644 plb.n_ext_attr2 ,
3645 plb.n_ext_attr3 ,
3646 plb.n_ext_attr4 ,
3647 plb.n_ext_attr5 ,
3648 plb.n_ext_attr6 ,
3649 plb.n_ext_attr7 ,
3650 plb.n_ext_attr8 ,
3651 plb.n_ext_attr9 ,
3652 plb.n_ext_attr10 ,
3653 plb.n_ext_attr11 ,
3654 plb.n_ext_attr12 ,
3655 plb.n_ext_attr13 ,
3656 plb.n_ext_attr14 ,
3657 plb.n_ext_attr15 ,
3658 plb.n_ext_attr16 ,
3659 plb.n_ext_attr17 ,
3660 plb.n_ext_attr18 ,
3661 plb.n_ext_attr19 ,
3662 plb.n_ext_attr20 ,
3663 plb.uom_ext_attr1 ,
3664 plb.uom_ext_attr2 ,
3665 plb.uom_ext_attr3 ,
3666 plb.uom_ext_attr4 ,
3667 plb.uom_ext_attr5 ,
3668 plb.uom_ext_attr6 ,
3669 plb.uom_ext_attr7 ,
3670 plb.uom_ext_attr8 ,
3671 plb.uom_ext_attr9 ,
3672 plb.uom_ext_attr10 ,
3673 plb.uom_ext_attr11 ,
3674 plb.uom_ext_attr12 ,
3675 plb.uom_ext_attr13 ,
3676 plb.uom_ext_attr14 ,
3677 plb.uom_ext_attr15 ,
3678 plb.uom_ext_attr16 ,
3679 plb.uom_ext_attr17 ,
3680 plb.uom_ext_attr18 ,
3681 plb.uom_ext_attr19 ,
3682 plb.uom_ext_attr20 ,
3683 plb.d_ext_attr1 ,
3684 plb.d_ext_attr2 ,
3685 plb.d_ext_attr3 ,
3686 plb.d_ext_attr4 ,
3687 plb.d_ext_attr5 ,
3688 plb.d_ext_attr6 ,
3689 plb.d_ext_attr7 ,
3690 plb.d_ext_attr8 ,
3691 plb.d_ext_attr9 ,
3692 plb.d_ext_attr10
3693 FROM po_lines_all_ext_b plb --base
3694 WHERE plb.po_line_id = plbd.po_line_id
3695 AND plb.draft_id = -1
3696 AND plb.attr_group_id = plbd.attr_group_id
3697 AND plb.pk1_value IS NULL
3698 )
3699 WHERE plbd.po_line_id = p_po_line_s_no_p_lck_tbl(i)
3700 AND plbd.draft_id = -1
3701 AND plbd.pk1_value = p_draft_id
3702 AND EXISTS (SELECT 'include only PRICING attribute category'
3703 FROM po_uda_ag_template_usages puatu,
3704 po_lines_all pol
3705 WHERE puatu.template_id = pol.uda_template_id
3706 AND plbd.po_line_id = pol.po_line_id
3707 AND puatu.attribute_category = 'PRICING'
3708 AND puatu.attribute_group_id = plbd.attr_group_id
3709 );
3710
3711 d_position := 60;
3712 IF (PO_LOG.d_stmt) THEN
3713 PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to Old MOdification');
3714 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3715 END IF;
3716
3717 --2nd update all attributes excluding numeric attributes for S or no lock lines
3718 FORALL i in 1..p_po_line_s_no_lck_tbl.COUNT
3719 UPDATE po_lines_all_ext_b plbd
3720 SET ( plbd.c_ext_attr1,
3721 plbd.c_ext_attr2 ,
3722 plbd.c_ext_attr3 ,
3723 plbd.c_ext_attr4 ,
3724 plbd.c_ext_attr5 ,
3725 plbd.c_ext_attr6 ,
3726 plbd.c_ext_attr7 ,
3727 plbd.c_ext_attr8 ,
3728 plbd.c_ext_attr9 ,
3729 plbd.c_ext_attr10 ,
3730 plbd.c_ext_attr11 ,
3731 plbd.c_ext_attr12 ,
3732 plbd.c_ext_attr13 ,
3733 plbd.c_ext_attr14 ,
3734 plbd.c_ext_attr15 ,
3735 plbd.c_ext_attr16 ,
3736 plbd.c_ext_attr17 ,
3737 plbd.c_ext_attr18 ,
3738 plbd.c_ext_attr19 ,
3739 plbd.c_ext_attr20 ,
3740 plbd.c_ext_attr21 ,
3741 plbd.c_ext_attr22 ,
3742 plbd.c_ext_attr23 ,
3743 plbd.c_ext_attr24 ,
3744 plbd.c_ext_attr25 ,
3745 plbd.c_ext_attr26 ,
3746 plbd.c_ext_attr27 ,
3747 plbd.c_ext_attr28 ,
3748 plbd.c_ext_attr29 ,
3749 plbd.c_ext_attr30 ,
3750 plbd.c_ext_attr31 ,
3751 plbd.c_ext_attr32 ,
3752 plbd.c_ext_attr33 ,
3753 plbd.c_ext_attr34 ,
3754 plbd.c_ext_attr35 ,
3755 plbd.c_ext_attr36 ,
3756 plbd.c_ext_attr37 ,
3757 plbd.c_ext_attr38 ,
3758 plbd.c_ext_attr39 ,
3759 plbd.c_ext_attr40 ,
3760 plbd.uom_ext_attr1 ,
3761 plbd.uom_ext_attr2 ,
3762 plbd.uom_ext_attr3 ,
3763 plbd.uom_ext_attr4,
3764 plbd.uom_ext_attr5 ,
3765 plbd.uom_ext_attr6 ,
3766 plbd.uom_ext_attr7 ,
3767 plbd.uom_ext_attr8 ,
3768 plbd.uom_ext_attr9 ,
3769 plbd.uom_ext_attr10 ,
3770 plbd.uom_ext_attr11 ,
3771 plbd.uom_ext_attr12 ,
3772 plbd.uom_ext_attr13 ,
3773 plbd.uom_ext_attr14 ,
3774 plbd.uom_ext_attr15 ,
3775 plbd.uom_ext_attr16 ,
3776 plbd.uom_ext_attr17 ,
3777 plbd.uom_ext_attr18 ,
3778 plbd.uom_ext_attr19 ,
3779 plbd.uom_ext_attr20 ,
3780 plbd.d_ext_attr1 ,
3781 plbd.d_ext_attr2 ,
3782 plbd.d_ext_attr3 ,
3783 plbd.d_ext_attr4 ,
3784 plbd.d_ext_attr5 ,
3785 plbd.d_ext_attr6 ,
3786 plbd.d_ext_attr7 ,
3787 plbd.d_ext_attr8 ,
3788 plbd.d_ext_attr9 ,
3789 plbd.d_ext_attr10 )
3790 = ( SELECT
3791 plb.c_ext_attr1,
3792 plb.c_ext_attr2 ,
3793 plb.c_ext_attr3 ,
3794 plb.c_ext_attr4 ,
3795 plb.c_ext_attr5 ,
3796 plb.c_ext_attr6 ,
3797 plb.c_ext_attr7 ,
3798 plb.c_ext_attr8 ,
3799 plb.c_ext_attr9 ,
3800 plb.c_ext_attr10 ,
3801 plb.c_ext_attr11 ,
3802 plb.c_ext_attr12 ,
3803 plb.c_ext_attr13 ,
3804 plb.c_ext_attr14 ,
3805 plb.c_ext_attr15 ,
3806 plb.c_ext_attr16 ,
3807 plb.c_ext_attr17 ,
3808 plb.c_ext_attr18 ,
3809 plb.c_ext_attr19 ,
3810 plb.c_ext_attr20 ,
3811 plb.c_ext_attr21 ,
3812 plb.c_ext_attr22 ,
3813 plb.c_ext_attr23 ,
3814 plb.c_ext_attr24 ,
3815 plb.c_ext_attr25 ,
3816 plb.c_ext_attr26 ,
3817 plb.c_ext_attr27 ,
3818 plb.c_ext_attr28 ,
3819 plb.c_ext_attr29 ,
3820 plb.c_ext_attr30 ,
3821 plb.c_ext_attr31 ,
3822 plb.c_ext_attr32 ,
3823 plb.c_ext_attr33 ,
3824 plb.c_ext_attr34 ,
3825 plb.c_ext_attr35 ,
3826 plb.c_ext_attr36 ,
3827 plb.c_ext_attr37 ,
3828 plb.c_ext_attr38 ,
3829 plb.c_ext_attr39 ,
3830 plb.c_ext_attr40 ,
3831 plb.uom_ext_attr1 ,
3832 plb.uom_ext_attr2 ,
3833 plb.uom_ext_attr3 ,
3834 plb.uom_ext_attr4,
3835 plb.uom_ext_attr5 ,
3836 plb.uom_ext_attr6 ,
3837 plb.uom_ext_attr7 ,
3838 plb.uom_ext_attr8 ,
3839 plb.uom_ext_attr9 ,
3840 plb.uom_ext_attr10 ,
3841 plb.uom_ext_attr11 ,
3842 plb.uom_ext_attr12 ,
3843 plb.uom_ext_attr13 ,
3844 plb.uom_ext_attr14 ,
3845 plb.uom_ext_attr15 ,
3846 plb.uom_ext_attr16 ,
3847 plb.uom_ext_attr17 ,
3848 plb.uom_ext_attr18 ,
3849 plb.uom_ext_attr19 ,
3850 plb.uom_ext_attr20 ,
3851 plb.d_ext_attr1 ,
3852 plb.d_ext_attr2 ,
3853 plb.d_ext_attr3 ,
3854 plb.d_ext_attr4 ,
3855 plb.d_ext_attr5 ,
3856 plb.d_ext_attr6 ,
3857 plb.d_ext_attr7 ,
3858 plb.d_ext_attr8 ,
3859 plb.d_ext_attr9 ,
3860 plb.d_ext_attr10
3861 FROM po_lines_all_ext_b plb --base
3862 WHERE plb.po_line_id = plbd.po_line_id
3863 AND plb.draft_id = -1
3864 AND plb.attr_group_id = plbd.attr_group_id
3865 AND plb.pk1_value IS NULL
3866 )
3867 WHERE plbd.po_line_id = p_po_line_s_no_lck_tbl(i)
3868 AND plbd.draft_id = p_draft_id
3869 AND plbd.pk1_value IS NULL
3870 AND EXISTS (SELECT 'include only PRICING attribute category'
3871 FROM po_uda_ag_template_usages puatu,
3872 po_lines_all pol
3873 WHERE puatu.template_id = pol.uda_template_id
3874 AND plbd.po_line_id = pol.po_line_id
3875 AND puatu.attribute_category = 'PRICING'
3876 AND puatu.attribute_group_id = plbd.attr_group_id
3877 );
3878
3879 d_position := 70;
3880 IF (PO_LOG.d_stmt) THEN
3881 PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to New MOdification Excluding Numeric attributes');
3882 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3883 END IF;
3884
3885 ---3rd update numeric attributes.. for s, p, null lock types
3886 FORALL i in 1..p_po_line_s_no_p_lck_tbl.COUNT
3887 UPDATE po_lines_all_ext_b plbd
3888 SET ( plbd.n_ext_attr1 ,
3889 plbd.n_ext_attr2 ,
3890 plbd.n_ext_attr3 ,
3891 plbd.n_ext_attr4 ,
3892 plbd.n_ext_attr5 ,
3893 plbd.n_ext_attr6 ,
3894 plbd.n_ext_attr7 ,
3895 plbd.n_ext_attr8 ,
3896 plbd.n_ext_attr9 ,
3897 plbd.n_ext_attr10 ,
3898 plbd.n_ext_attr11 ,
3899 plbd.n_ext_attr12 ,
3900 plbd.n_ext_attr13 ,
3901 plbd.n_ext_attr14 ,
3902 plbd.n_ext_attr15 ,
3903 plbd.n_ext_attr16 ,
3904 plbd.n_ext_attr17 ,
3905 plbd.n_ext_attr18 ,
3906 plbd.n_ext_attr19 ,
3907 plbd.n_ext_attr20 )
3908 = ( SELECT
3909 plb.n_ext_attr1 + pld.n_ext_attr1 ,
3910 plb.n_ext_attr2 + pld.n_ext_attr2 ,
3911 plb.n_ext_attr3 + pld.n_ext_attr3 ,
3912 plb.n_ext_attr4 + pld.n_ext_attr4 ,
3913 plb.n_ext_attr5 + pld.n_ext_attr5 ,
3914 plb.n_ext_attr6 + pld.n_ext_attr6 ,
3915 plb.n_ext_attr7 + pld.n_ext_attr7 ,
3916 plb.n_ext_attr8 + pld.n_ext_attr8 ,
3917 plb.n_ext_attr9 + pld.n_ext_attr9 ,
3918 plb.n_ext_attr10 + pld.n_ext_attr10 ,
3919 plb.n_ext_attr11 + pld.n_ext_attr11 ,
3920 plb.n_ext_attr12 + pld.n_ext_attr12 ,
3921 plb.n_ext_attr13 + pld.n_ext_attr13 ,
3922 plb.n_ext_attr14 + pld.n_ext_attr14 ,
3923 plb.n_ext_attr15 + pld.n_ext_attr15 ,
3924 plb.n_ext_attr16 + pld.n_ext_attr16 ,
3925 plb.n_ext_attr17 + pld.n_ext_attr17 ,
3926 plb.n_ext_attr18 + pld.n_ext_attr18 ,
3927 plb.n_ext_attr19 + pld.n_ext_attr19 ,
3928 plb.n_ext_attr20 + pld.n_ext_attr20
3929 FROM po_lines_all_ext_b plb,--base
3930 po_lines_all_ext_b pld--delta
3931 WHERE plb.po_line_id = plbd.po_line_id
3932 AND pld.po_line_id = -(plb.po_line_id)
3933 AND plb.attr_group_id = plbd.attr_group_id
3934 AND plb.attr_group_id = pld.attr_group_id
3935 AND plb.draft_id = -1
3936 AND pld.draft_id = -p_draft_id
3937 AND plb.pk1_value IS NULL
3938 AND pld.pk1_value = -p_draft_id
3939 )
3940 WHERE plbd.po_line_id = p_po_line_s_no_p_lck_tbl(i)
3941 AND plbd.draft_id = p_draft_id
3942 AND plbd.pk1_value IS NULL
3943 AND EXISTS (SELECT 'include only PRICING attribute category'
3944 FROM po_uda_ag_template_usages puatu,
3945 po_lines_all pol
3946 WHERE puatu.template_id = pol.uda_template_id
3947 AND plbd.po_line_id = pol.po_line_id
3948 AND puatu.attribute_category = 'PRICING'
3949 AND puatu.attribute_group_id = plbd.attr_group_id
3950 );
3951
3952 d_position := 80;
3953 IF (PO_LOG.d_stmt) THEN
3954 PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to New MOdification only for Numeric attributes');
3955 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
3956 END IF;
3957
3958 -- Update lines in ext_tl table
3959 FORALL i in 1..p_po_line_s_no_lck_tbl.COUNT
3960 UPDATE po_lines_all_ext_tl pltd
3961 SET ( pltd.tl_ext_attr1 ,
3962 pltd.tl_ext_attr2 ,
3963 pltd.tl_ext_attr3 ,
3964 pltd.tl_ext_attr4 ,
3965 pltd.tl_ext_attr5 ,
3966 pltd.tl_ext_attr6 ,
3967 pltd.tl_ext_attr7 ,
3968 pltd.tl_ext_attr8 ,
3969 pltd.tl_ext_attr9 ,
3970 pltd.tl_ext_attr10 ,
3971 pltd.tl_ext_attr11 ,
3972 pltd.tl_ext_attr12 ,
3973 pltd.tl_ext_attr13 ,
3974 pltd.tl_ext_attr14 ,
3975 pltd.tl_ext_attr15 ,
3976 pltd.tl_ext_attr16 ,
3977 pltd.tl_ext_attr17 ,
3978 pltd.tl_ext_attr18 ,
3979 pltd.tl_ext_attr19 ,
3980 pltd.tl_ext_attr20 ,
3981 pltd.tl_ext_attr21 ,
3982 pltd.tl_ext_attr22 ,
3983 pltd.tl_ext_attr23 ,
3984 pltd.tl_ext_attr24 ,
3985 pltd.tl_ext_attr25 ,
3986 pltd.tl_ext_attr26 ,
3987 pltd.tl_ext_attr27 ,
3988 pltd.tl_ext_attr28 ,
3989 pltd.tl_ext_attr29 ,
3990 pltd.tl_ext_attr30 ,
3991 pltd.tl_ext_attr31 ,
3992 pltd.tl_ext_attr32 ,
3993 pltd.tl_ext_attr33 ,
3994 pltd.tl_ext_attr34 ,
3995 pltd.tl_ext_attr35 ,
3996 pltd.tl_ext_attr36 ,
3997 pltd.tl_ext_attr37 ,
3998 pltd.tl_ext_attr38 ,
3999 pltd.tl_ext_attr39 ,
4000 pltd.tl_ext_attr40 )
4001 = ( SELECT
4002 pltb.tl_ext_attr1 ,
4003 pltb.tl_ext_attr2 ,
4004 pltb.tl_ext_attr3 ,
4005 pltb.tl_ext_attr4 ,
4006 pltb.tl_ext_attr5 ,
4007 pltb.tl_ext_attr6 ,
4008 pltb.tl_ext_attr7 ,
4009 pltb.tl_ext_attr8 ,
4010 pltb.tl_ext_attr9 ,
4011 pltb.tl_ext_attr10 ,
4012 pltb.tl_ext_attr11 ,
4013 pltb.tl_ext_attr12 ,
4014 pltb.tl_ext_attr13 ,
4015 pltb.tl_ext_attr14 ,
4016 pltb.tl_ext_attr15 ,
4017 pltb.tl_ext_attr16 ,
4018 pltb.tl_ext_attr17 ,
4019 pltb.tl_ext_attr18 ,
4020 pltb.tl_ext_attr19 ,
4021 pltb.tl_ext_attr20 ,
4022 pltb.tl_ext_attr21 ,
4023 pltb.tl_ext_attr22 ,
4024 pltb.tl_ext_attr23 ,
4025 pltb.tl_ext_attr24 ,
4026 pltb.tl_ext_attr25 ,
4027 pltb.tl_ext_attr26 ,
4028 pltb.tl_ext_attr27 ,
4029 pltb.tl_ext_attr28 ,
4030 pltb.tl_ext_attr29 ,
4031 pltb.tl_ext_attr30 ,
4032 pltb.tl_ext_attr31 ,
4033 pltb.tl_ext_attr32 ,
4034 pltb.tl_ext_attr33 ,
4035 pltb.tl_ext_attr34 ,
4036 pltb.tl_ext_attr35 ,
4037 pltb.tl_ext_attr36 ,
4038 pltb.tl_ext_attr37 ,
4039 pltb.tl_ext_attr38 ,
4040 pltb.tl_ext_attr39 ,
4041 pltb.tl_ext_attr40
4042 FROM po_lines_all_ext_tl pltb --base
4043 WHERE pltb.po_line_id = pltd.po_line_id
4044 AND pltb.draft_id = -1
4045 AND pltb.attr_group_id = pltd.attr_group_id
4046 AND pltb.language = pltd.language
4047 AND pltb.PK1_VALUE is null
4048 )
4049 WHERE pltd.po_line_id = p_po_line_s_no_lck_tbl(i)
4050 AND pltd.draft_id = p_draft_id
4051 AND pltd.PK1_VALUE is null;
4052
4053
4054 d_position := 90;
4055 IF (PO_LOG.d_stmt) THEN
4056 PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_tl ');
4057 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
4058 END IF;
4059 d_position := 100;
4060 IF (PO_LOG.d_proc) THEN
4061 PO_LOG.proc_end(d_module);
4062 END IF;
4063
4064 EXCEPTION
4065 WHEN others THEN
4066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4067 IF (PO_LOG.d_exc) THEN
4068 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
4069 END IF;
4070 RAISE;
4071 END sync_lines_ext;
4072
4073
4074 --------------------------------------------------------------------------------
4075 --Start of Comments
4076 --Name: sync_shipments_ext
4077 -- <Conc Mods Project>
4078 --Pre-reqs:
4079 -- none
4080 --Modifies: None
4081 --Function:
4082 -- This procedure syncs the all shipment_ext entities of Mod with the corresponding
4083 -- Base shipment_ext entites
4084 --Parameters:
4085 --IN:
4086 --p_draft_id
4087 -- draft_id of the Modification
4088 --IN OUT:
4089 --OUT:
4090 --Notes:
4091 --End of Comments
4092 --------------------------------------------------------------------------------
4093 PROCEDURE sync_shipments_ext(
4094 p_draft_id IN NUMBER
4095 ,x_return_status OUT NOCOPY VARCHAR2
4096 ,p_po_line_loc_s_no_lck_tbl IN PO_TBL_NUMBER --<Bug 16671702>
4097 ) IS
4098 d_api_name CONSTANT VARCHAR2(30) := 'sync_shipments_ext';
4099 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
4100 d_position NUMBER;
4101 d_api_version CONSTANT NUMBER := 1.0;
4102
4103 BEGIN
4104 --<Bug 16671702> : Will use p_po_line_loc_s_no_lck_tbl instead of l_po_line_loc_s_no_lck_tbl
4105
4106 d_position := 0;
4107 IF (PO_LOG.d_proc) THEN
4108 PO_LOG.proc_begin(d_module);
4109 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
4110 END IF;
4111
4112 --<Bug 16671702> : Cleaning up. p_po_line_loc_s_no_lck_tbl is already passed as parameter to this api
4113
4114 d_position := 20;
4115 IF (PO_LOG.d_stmt) THEN
4116 PO_LOG.stmt(d_module,d_position ,'line_loc_id for S and No locks excluding Price break', p_po_line_loc_s_no_lck_tbl);
4117 END IF;
4118
4119 FORALL i in 1..p_po_line_loc_s_no_lck_tbl.COUNT
4120 UPDATE po_line_locations_all_ext_b plld
4121 SET ( plld.c_ext_attr1,
4122 plld.c_ext_attr2 ,
4123 plld.c_ext_attr3 ,
4124 plld.c_ext_attr4 ,
4125 plld.c_ext_attr5 ,
4126 plld.c_ext_attr6 ,
4127 plld.c_ext_attr7 ,
4128 plld.c_ext_attr8 ,
4129 plld.c_ext_attr9 ,
4130 plld.c_ext_attr10 ,
4131 plld.c_ext_attr11 ,
4132 plld.c_ext_attr12 ,
4133 plld.c_ext_attr13 ,
4134 plld.c_ext_attr14 ,
4135 plld.c_ext_attr15 ,
4136 plld.c_ext_attr16 ,
4137 plld.c_ext_attr17 ,
4138 plld.c_ext_attr18 ,
4139 plld.c_ext_attr19 ,
4140 plld.c_ext_attr20 ,
4141 plld.c_ext_attr21 ,
4142 plld.c_ext_attr22 ,
4143 plld.c_ext_attr23 ,
4144 plld.c_ext_attr24 ,
4145 plld.c_ext_attr25 ,
4146 plld.c_ext_attr26 ,
4147 plld.c_ext_attr27 ,
4148 plld.c_ext_attr28 ,
4149 plld.c_ext_attr29 ,
4150 plld.c_ext_attr30 ,
4151 plld.c_ext_attr31 ,
4152 plld.c_ext_attr32 ,
4153 plld.c_ext_attr33 ,
4154 plld.c_ext_attr34 ,
4155 plld.c_ext_attr35 ,
4156 plld.c_ext_attr36 ,
4157 plld.c_ext_attr37 ,
4158 plld.c_ext_attr38 ,
4159 plld.c_ext_attr39 ,
4160 plld.c_ext_attr40 ,
4161 plld.n_ext_attr1 ,
4162 plld.n_ext_attr2 ,
4163 plld.n_ext_attr3 ,
4164 plld.n_ext_attr4 ,
4165 plld.n_ext_attr5 ,
4166 plld.n_ext_attr6 ,
4167 plld.n_ext_attr7 ,
4168 plld.n_ext_attr8 ,
4169 plld.n_ext_attr9 ,
4170 plld.n_ext_attr10 ,
4171 plld.n_ext_attr11 ,
4172 plld.n_ext_attr12 ,
4173 plld.n_ext_attr13 ,
4174 plld.n_ext_attr14 ,
4175 plld.n_ext_attr15 ,
4176 plld.n_ext_attr16 ,
4177 plld.n_ext_attr17 ,
4178 plld.n_ext_attr18 ,
4179 plld.n_ext_attr19 ,
4180 plld.n_ext_attr20 ,
4181 plld.uom_ext_attr1 ,
4182 plld.uom_ext_attr2 ,
4183 plld.uom_ext_attr3 ,
4184 plld.uom_ext_attr4,
4185 plld.uom_ext_attr5 ,
4186 plld.uom_ext_attr6 ,
4187 plld.uom_ext_attr7 ,
4188 plld.uom_ext_attr8 ,
4189 plld.uom_ext_attr9 ,
4190 plld.uom_ext_attr10 ,
4191 plld.uom_ext_attr11 ,
4192 plld.uom_ext_attr12 ,
4193 plld.uom_ext_attr13 ,
4194 plld.uom_ext_attr14 ,
4195 plld.uom_ext_attr15 ,
4196 plld.uom_ext_attr16 ,
4197 plld.uom_ext_attr17 ,
4198 plld.uom_ext_attr18 ,
4199 plld.uom_ext_attr19 ,
4200 plld.uom_ext_attr20 ,
4201 plld.d_ext_attr1 ,
4202 plld.d_ext_attr2 ,
4203 plld.d_ext_attr3 ,
4204 plld.d_ext_attr4 ,
4205 plld.d_ext_attr5 ,
4206 plld.d_ext_attr6 ,
4207 plld.d_ext_attr7 ,
4208 plld.d_ext_attr8 ,
4209 plld.d_ext_attr9 ,
4210 plld.d_ext_attr10 )
4211 = ( SELECT
4212 pllb.c_ext_attr1,
4213 pllb.c_ext_attr2 ,
4214 pllb.c_ext_attr3 ,
4215 pllb.c_ext_attr4 ,
4216 pllb.c_ext_attr5 ,
4217 pllb.c_ext_attr6 ,
4218 pllb.c_ext_attr7 ,
4219 pllb.c_ext_attr8 ,
4220 pllb.c_ext_attr9 ,
4221 pllb.c_ext_attr10 ,
4222 pllb.c_ext_attr11 ,
4223 pllb.c_ext_attr12 ,
4224 pllb.c_ext_attr13 ,
4225 pllb.c_ext_attr14 ,
4226 pllb.c_ext_attr15 ,
4227 pllb.c_ext_attr16 ,
4228 pllb.c_ext_attr17 ,
4229 pllb.c_ext_attr18 ,
4230 pllb.c_ext_attr19 ,
4231 pllb.c_ext_attr20 ,
4232 pllb.c_ext_attr21 ,
4233 pllb.c_ext_attr22 ,
4234 pllb.c_ext_attr23 ,
4235 pllb.c_ext_attr24 ,
4236 pllb.c_ext_attr25 ,
4237 pllb.c_ext_attr26 ,
4238 pllb.c_ext_attr27 ,
4239 pllb.c_ext_attr28 ,
4240 pllb.c_ext_attr29 ,
4241 pllb.c_ext_attr30 ,
4242 pllb.c_ext_attr31 ,
4243 pllb.c_ext_attr32 ,
4244 pllb.c_ext_attr33 ,
4245 pllb.c_ext_attr34 ,
4246 pllb.c_ext_attr35 ,
4247 pllb.c_ext_attr36 ,
4248 pllb.c_ext_attr37 ,
4249 pllb.c_ext_attr38 ,
4250 pllb.c_ext_attr39 ,
4251 pllb.c_ext_attr40 ,
4252 pllb.n_ext_attr1 ,
4253 pllb.n_ext_attr2 ,
4254 pllb.n_ext_attr3 ,
4255 pllb.n_ext_attr4 ,
4256 pllb.n_ext_attr5 ,
4257 pllb.n_ext_attr6 ,
4258 pllb.n_ext_attr7 ,
4259 pllb.n_ext_attr8 ,
4260 pllb.n_ext_attr9 ,
4261 pllb.n_ext_attr10 ,
4262 pllb.n_ext_attr11 ,
4263 pllb.n_ext_attr12 ,
4264 pllb.n_ext_attr13 ,
4265 pllb.n_ext_attr14 ,
4266 pllb.n_ext_attr15 ,
4267 pllb.n_ext_attr16 ,
4268 pllb.n_ext_attr17 ,
4269 pllb.n_ext_attr18 ,
4270 pllb.n_ext_attr19 ,
4271 pllb.n_ext_attr20 ,
4272 pllb.uom_ext_attr1 ,
4273 pllb.uom_ext_attr2 ,
4274 pllb.uom_ext_attr3 ,
4275 pllb.uom_ext_attr4 ,
4276 pllb.uom_ext_attr5 ,
4277 pllb.uom_ext_attr6 ,
4278 pllb.uom_ext_attr7 ,
4279 pllb.uom_ext_attr8 ,
4280 pllb.uom_ext_attr9 ,
4281 pllb.uom_ext_attr10 ,
4282 pllb.uom_ext_attr11 ,
4283 pllb.uom_ext_attr12 ,
4284 pllb.uom_ext_attr13 ,
4285 pllb.uom_ext_attr14 ,
4286 pllb.uom_ext_attr15 ,
4287 pllb.uom_ext_attr16 ,
4288 pllb.uom_ext_attr17 ,
4289 pllb.uom_ext_attr18 ,
4290 pllb.uom_ext_attr19 ,
4291 pllb.uom_ext_attr20 ,
4292 pllb.d_ext_attr1 ,
4293 pllb.d_ext_attr2 ,
4294 pllb.d_ext_attr3 ,
4295 pllb.d_ext_attr4 ,
4296 pllb.d_ext_attr5 ,
4297 pllb.d_ext_attr6 ,
4298 pllb.d_ext_attr7 ,
4299 pllb.d_ext_attr8 ,
4300 pllb.d_ext_attr9 ,
4301 pllb.d_ext_attr10
4302 FROM po_line_locations_all_ext_b pllb --base
4303 WHERE pllb.line_location_id = plld.line_location_id
4304 AND pllb.draft_id = -1
4305 AND pllb.attr_group_id = plld.attr_group_id
4306 )
4307 WHERE plld.line_location_id = p_po_line_loc_s_no_lck_tbl(i)
4308 AND plld.draft_id = p_draft_id;
4309
4310 d_position := 50;
4311 IF (PO_LOG.d_stmt) THEN
4312 PO_LOG.stmt(d_module,d_position ,'Updated po_line_locations_all_ext_b for the given p_drat_id');
4313 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
4314 END IF;
4315
4316 --update for tl tables
4317 FORALL i in 1..p_po_line_loc_s_no_lck_tbl.COUNT
4318 UPDATE po_line_locations_all_ext_tl plltd
4319 SET ( plltd.tl_ext_attr1 ,
4320 plltd.tl_ext_attr2 ,
4321 plltd.tl_ext_attr3 ,
4322 plltd.tl_ext_attr4 ,
4323 plltd.tl_ext_attr5 ,
4324 plltd.tl_ext_attr6 ,
4325 plltd.tl_ext_attr7 ,
4326 plltd.tl_ext_attr8 ,
4327 plltd.tl_ext_attr9 ,
4328 plltd.tl_ext_attr10 ,
4329 plltd.tl_ext_attr11 ,
4330 plltd.tl_ext_attr12 ,
4331 plltd.tl_ext_attr13 ,
4332 plltd.tl_ext_attr14 ,
4333 plltd.tl_ext_attr15 ,
4334 plltd.tl_ext_attr16 ,
4335 plltd.tl_ext_attr17 ,
4336 plltd.tl_ext_attr18 ,
4337 plltd.tl_ext_attr19 ,
4338 plltd.tl_ext_attr20 ,
4339 plltd.tl_ext_attr21 ,
4340 plltd.tl_ext_attr22 ,
4341 plltd.tl_ext_attr23 ,
4342 plltd.tl_ext_attr24 ,
4343 plltd.tl_ext_attr25 ,
4344 plltd.tl_ext_attr26 ,
4345 plltd.tl_ext_attr27 ,
4346 plltd.tl_ext_attr28 ,
4347 plltd.tl_ext_attr29 ,
4348 plltd.tl_ext_attr30 ,
4349 plltd.tl_ext_attr31 ,
4350 plltd.tl_ext_attr32 ,
4351 plltd.tl_ext_attr33 ,
4352 plltd.tl_ext_attr34 ,
4353 plltd.tl_ext_attr35 ,
4354 plltd.tl_ext_attr36 ,
4355 plltd.tl_ext_attr37 ,
4356 plltd.tl_ext_attr38 ,
4357 plltd.tl_ext_attr39 ,
4358 plltd.tl_ext_attr40 )
4359 = ( SELECT
4360 plltb.tl_ext_attr1 ,
4361 plltb.tl_ext_attr2 ,
4362 plltb.tl_ext_attr3 ,
4363 plltb.tl_ext_attr4 ,
4364 plltb.tl_ext_attr5 ,
4365 plltb.tl_ext_attr6 ,
4366 plltb.tl_ext_attr7 ,
4367 plltb.tl_ext_attr8 ,
4368 plltb.tl_ext_attr9 ,
4369 plltb.tl_ext_attr10 ,
4370 plltb.tl_ext_attr11 ,
4371 plltb.tl_ext_attr12 ,
4372 plltb.tl_ext_attr13 ,
4373 plltb.tl_ext_attr14 ,
4374 plltb.tl_ext_attr15 ,
4375 plltb.tl_ext_attr16 ,
4376 plltb.tl_ext_attr17 ,
4377 plltb.tl_ext_attr18 ,
4378 plltb.tl_ext_attr19 ,
4379 plltb.tl_ext_attr20 ,
4380 plltb.tl_ext_attr21 ,
4381 plltb.tl_ext_attr22 ,
4382 plltb.tl_ext_attr23 ,
4383 plltb.tl_ext_attr24 ,
4384 plltb.tl_ext_attr25 ,
4385 plltb.tl_ext_attr26 ,
4386 plltb.tl_ext_attr27 ,
4387 plltb.tl_ext_attr28 ,
4388 plltb.tl_ext_attr29 ,
4389 plltb.tl_ext_attr30 ,
4390 plltb.tl_ext_attr31 ,
4391 plltb.tl_ext_attr32 ,
4392 plltb.tl_ext_attr33 ,
4393 plltb.tl_ext_attr34 ,
4394 plltb.tl_ext_attr35 ,
4395 plltb.tl_ext_attr36 ,
4396 plltb.tl_ext_attr37 ,
4397 plltb.tl_ext_attr38 ,
4398 plltb.tl_ext_attr39 ,
4399 plltb.tl_ext_attr40
4400 FROM po_line_locations_all_ext_tl plltb --base
4401 WHERE plltb.line_location_id = plltd.line_location_id
4402 AND plltb.draft_id = -1
4403 AND plltb.attr_group_id = plltd.attr_group_id
4404 AND plltb.language = plltd.language
4405 )
4406 WHERE plltd.line_location_id = p_po_line_loc_s_no_lck_tbl(i)
4407 AND plltd.draft_id = p_draft_id;
4408
4409 d_position := 70;
4410 IF (PO_LOG.d_stmt) THEN
4411 PO_LOG.stmt(d_module,d_position ,'Updated po_line_locations_all_ext_tl for the given p_drat_id');
4412 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
4413 END IF;
4414
4415 d_position := 100;
4416 IF (PO_LOG.d_proc) THEN
4417 PO_LOG.proc_end(d_module);
4418 END IF;
4419
4420 EXCEPTION
4421 WHEN others THEN
4422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4423 IF (PO_LOG.d_exc) THEN
4424 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
4425 END IF;
4426 RAISE;
4427 END sync_shipments_ext;
4428
4429
4430 --------------------------------------------------------------------------------
4431 --Start of Comments
4432 --Name: sync_uda_address
4433 -- <Conc Mods Project>
4434 --Pre-reqs:
4435 -- none
4436 --Modifies: None
4437 --Function:
4438 -- This procedure syncs the all uda_address entities of Mod with the corresponding
4439 -- Base's uda_address entites
4440 --Parameters:
4441 --IN:
4442 --p_draft_id
4443 -- draft_id of the Modification
4444 --IN OUT:
4445 --OUT:
4446 --Notes:
4447 --End of Comments
4448 --------------------------------------------------------------------------------
4449 PROCEDURE sync_uda_address(
4450 p_draft_id IN NUMBER
4451 ,p_ignore_rev_number IN VARCHAR2 DEFAULT 'N'
4452 ,x_return_status OUT NOCOPY VARCHAR2
4453 ) IS
4454 l_po_header_id NUMBER;
4455 d_api_name CONSTANT VARCHAR2(30) := 'sync_uda_address';
4456 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
4457 d_position NUMBER;
4458 d_api_version CONSTANT NUMBER := 1.0;
4459 BEGIN
4460
4461 d_position := 0;
4462 IF (PO_LOG.d_proc) THEN
4463 PO_LOG.proc_begin(d_module);
4464 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
4465 END IF;
4466
4467 SELECT document_id
4468 INTO l_po_header_id
4469 FROM po_drafts
4470 WHERE draft_id = p_draft_id;
4471
4472 d_position := 20;
4473 IF (PO_LOG.d_stmt) THEN
4474 PO_LOG.stmt(d_module,d_position ,'Header Id', l_po_header_id);
4475 END IF;
4476
4477 UPDATE po_headers_all_ext_b phbd
4478 SET ( phbd.c_ext_attr1,
4479 phbd.c_ext_attr2 ,
4480 phbd.c_ext_attr3 ,
4481 phbd.c_ext_attr4 ,
4482 phbd.c_ext_attr5 ,
4483 phbd.c_ext_attr6 ,
4484 phbd.c_ext_attr7 ,
4485 phbd.c_ext_attr8 ,
4486 phbd.c_ext_attr9 ,
4487 phbd.c_ext_attr10 ,
4488 phbd.c_ext_attr11 ,
4489 phbd.c_ext_attr12 ,
4490 phbd.c_ext_attr13 ,
4491 phbd.c_ext_attr14 ,
4492 phbd.c_ext_attr15 ,
4493 phbd.c_ext_attr16 ,
4494 phbd.c_ext_attr17 ,
4495 phbd.c_ext_attr18 ,
4496 phbd.c_ext_attr19 ,
4497 phbd.c_ext_attr20 ,
4498 phbd.c_ext_attr21 ,
4499 phbd.c_ext_attr22 ,
4500 phbd.c_ext_attr23 ,
4501 phbd.c_ext_attr24 ,
4502 phbd.c_ext_attr25 ,
4503 phbd.c_ext_attr26 ,
4504 phbd.c_ext_attr27 ,
4505 phbd.c_ext_attr28 ,
4506 phbd.c_ext_attr29 ,
4507 phbd.c_ext_attr30 ,
4508 phbd.c_ext_attr31 ,
4509 phbd.c_ext_attr32 ,
4510 phbd.c_ext_attr33 ,
4511 phbd.c_ext_attr34 ,
4512 phbd.c_ext_attr35 ,
4513 phbd.c_ext_attr36 ,
4514 phbd.c_ext_attr37 ,
4515 phbd.c_ext_attr38 ,
4516 phbd.c_ext_attr39 ,
4517 phbd.c_ext_attr40 ,
4518 phbd.n_ext_attr1 ,
4519 phbd.n_ext_attr2 ,
4520 phbd.n_ext_attr3 ,
4521 phbd.n_ext_attr4 ,
4522 phbd.n_ext_attr5 ,
4523 phbd.n_ext_attr6 ,
4524 phbd.n_ext_attr7 ,
4525 phbd.n_ext_attr8 ,
4526 phbd.n_ext_attr9 ,
4527 phbd.n_ext_attr10 ,
4528 phbd.n_ext_attr11 ,
4529 phbd.n_ext_attr12 ,
4530 phbd.n_ext_attr13 ,
4531 phbd.n_ext_attr14 ,
4532 phbd.n_ext_attr15 ,
4533 phbd.n_ext_attr16 ,
4534 phbd.n_ext_attr17 ,
4535 phbd.n_ext_attr18 ,
4536 phbd.n_ext_attr19 ,
4537 phbd.n_ext_attr20 ,
4538 phbd.uom_ext_attr1 ,
4539 phbd.uom_ext_attr2 ,
4540 phbd.uom_ext_attr3 ,
4541 phbd.uom_ext_attr4,
4542 phbd.uom_ext_attr5 ,
4543 phbd.uom_ext_attr6 ,
4544 phbd.uom_ext_attr7 ,
4545 phbd.uom_ext_attr8 ,
4546 phbd.uom_ext_attr9 ,
4547 phbd.uom_ext_attr10 ,
4548 phbd.uom_ext_attr11 ,
4549 phbd.uom_ext_attr12 ,
4550 phbd.uom_ext_attr13 ,
4551 phbd.uom_ext_attr14 ,
4552 phbd.uom_ext_attr15 ,
4553 phbd.uom_ext_attr16 ,
4554 phbd.uom_ext_attr17 ,
4555 phbd.uom_ext_attr18 ,
4556 phbd.uom_ext_attr19 ,
4557 phbd.uom_ext_attr20 ,
4558 phbd.d_ext_attr1 ,
4559 phbd.d_ext_attr2 ,
4560 phbd.d_ext_attr3 ,
4561 phbd.d_ext_attr4 ,
4562 phbd.d_ext_attr5 ,
4563 phbd.d_ext_attr6 ,
4564 phbd.d_ext_attr7 ,
4565 phbd.d_ext_attr8 ,
4566 phbd.d_ext_attr9 ,
4567 phbd.d_ext_attr10 ,
4568 phbd.revision_num)
4569 = ( SELECT
4570 phb.c_ext_attr1,
4571 phb.c_ext_attr2 ,
4572 phb.c_ext_attr3 ,
4573 phb.c_ext_attr4 ,
4574 phb.c_ext_attr5 ,
4575 phb.c_ext_attr6 ,
4576 phb.c_ext_attr7 ,
4577 phb.c_ext_attr8 ,
4578 phb.c_ext_attr9 ,
4579 phb.c_ext_attr10 ,
4580 phb.c_ext_attr11 ,
4581 phb.c_ext_attr12 ,
4582 phb.c_ext_attr13 ,
4583 phb.c_ext_attr14 ,
4584 phb.c_ext_attr15 ,
4585 phb.c_ext_attr16 ,
4586 phb.c_ext_attr17 ,
4587 phb.c_ext_attr18 ,
4588 phb.c_ext_attr19 ,
4589 phb.c_ext_attr20 ,
4590 phb.c_ext_attr21 ,
4591 phb.c_ext_attr22 ,
4592 phb.c_ext_attr23 ,
4593 phb.c_ext_attr24 ,
4594 phb.c_ext_attr25 ,
4595 phb.c_ext_attr26 ,
4596 phb.c_ext_attr27 ,
4597 phb.c_ext_attr28 ,
4598 phb.c_ext_attr29 ,
4599 phb.c_ext_attr30 ,
4600 phb.c_ext_attr31 ,
4601 phb.c_ext_attr32 ,
4602 phb.c_ext_attr33 ,
4603 phb.c_ext_attr34 ,
4604 phb.c_ext_attr35 ,
4605 phb.c_ext_attr36 ,
4606 phb.c_ext_attr37 ,
4607 phb.c_ext_attr38 ,
4608 phb.c_ext_attr39 ,
4609 phb.c_ext_attr40 ,
4610 phb.n_ext_attr1 ,
4611 phb.n_ext_attr2 ,
4612 phb.n_ext_attr3 ,
4613 phb.n_ext_attr4 ,
4614 phb.n_ext_attr5 ,
4615 phb.n_ext_attr6 ,
4616 phb.n_ext_attr7 ,
4617 phb.n_ext_attr8 ,
4618 phb.n_ext_attr9 ,
4619 phb.n_ext_attr10 ,
4620 phb.n_ext_attr11 ,
4621 phb.n_ext_attr12 ,
4622 phb.n_ext_attr13 ,
4623 phb.n_ext_attr14 ,
4624 phb.n_ext_attr15 ,
4625 phb.n_ext_attr16 ,
4626 phb.n_ext_attr17 ,
4627 phb.n_ext_attr18 ,
4628 phb.n_ext_attr19 ,
4629 phb.n_ext_attr20 ,
4630 phb.uom_ext_attr1 ,
4631 phb.uom_ext_attr2 ,
4632 phb.uom_ext_attr3 ,
4633 phb.uom_ext_attr4 ,
4634 phb.uom_ext_attr5 ,
4635 phb.uom_ext_attr6 ,
4636 phb.uom_ext_attr7 ,
4637 phb.uom_ext_attr8 ,
4638 phb.uom_ext_attr9 ,
4639 phb.uom_ext_attr10 ,
4640 phb.uom_ext_attr11 ,
4641 phb.uom_ext_attr12 ,
4642 phb.uom_ext_attr13 ,
4643 phb.uom_ext_attr14 ,
4644 phb.uom_ext_attr15 ,
4645 phb.uom_ext_attr16 ,
4646 phb.uom_ext_attr17 ,
4647 phb.uom_ext_attr18 ,
4648 phb.uom_ext_attr19 ,
4649 phb.uom_ext_attr20 ,
4650 phb.d_ext_attr1 ,
4651 phb.d_ext_attr2 ,
4652 phb.d_ext_attr3 ,
4653 phb.d_ext_attr4 ,
4654 phb.d_ext_attr5 ,
4655 phb.d_ext_attr6 ,
4656 phb.d_ext_attr7 ,
4657 phb.d_ext_attr8 ,
4658 phb.d_ext_attr9 ,
4659 phb.d_ext_attr10 ,
4660 phb.revision_num
4661 FROM po_headers_all_ext_b phb--base
4662 WHERE phb.po_header_id = phbd.po_header_id
4663 AND phb.draft_id = -1
4664 AND phb.attr_group_id = phbd.attr_group_id
4665 AND phb.c_ext_attr39=phbd.c_ext_attr39
4666 )
4667
4668 WHERE phbd.po_header_id = l_po_header_id
4669 AND phbd.draft_id = p_draft_id
4670 -- include addresses
4671 AND phbd.c_ext_attr40 ='PO_UDA_ADDRESS_TYPES'
4672 -- include multi-row attribute groups
4673 AND EXISTS (SELECT 'Multi row attr grp'
4674 FROM ego_fnd_dsc_flx_ctx_ext ag
4675 WHERE phbd.attr_group_id = ag.attr_group_id
4676 AND ag.multi_row = 'Y')
4677 AND NOT EXISTS (SELECT 'only S or Null Locks'
4678 FROM po_entity_locks pel
4679 WHERE pel.lock_type IN ('P','F')
4680 AND pel.entity_pk1= phbd.po_header_id
4681 AND pel.entity_pk2= phbd.c_ext_attr39
4682 AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_ADDRESS_ENTITY
4683 AND pel.lock_by_draft_id = p_draft_id )
4684 AND EXISTS (SELECT 'base.rev_num>draft.rev_num'
4685 FROM po_headers_all_ext_b phb,
4686 po_drafts dft
4687 WHERE ( (p_ignore_rev_number ='Y') OR (NVL(phb.revision_num,0) > NVL(phbd.revision_num,0) OR NVL(dft.revision_num,0) = -1))
4688 AND phb.draft_id = -1
4689 AND phb.po_header_id=l_po_header_id
4690 and phbd.draft_id = dft.draft_id);
4691 -- End AND phd.revision_num>phbd.revision_num
4692
4693 d_position := 20;
4694 IF (PO_LOG.d_stmt) THEN
4695 PO_LOG.stmt(d_module,d_position ,'Updated UDA_ADDRESS');
4696 PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
4697 END IF;
4698
4699 d_position:=40;
4700 IF (PO_LOG.d_proc) THEN
4701 PO_LOG.proc_end(d_module);
4702 END IF;
4703
4704 EXCEPTION
4705 WHEN OTHERS THEN
4706 IF (PO_LOG.d_exc) THEN
4707 PO_LOG.exc(d_module,d_position,SQLCODE || SQLERRM);
4708 END IF;
4709 RAISE;
4710
4711 END sync_uda_address;
4712
4713
4714 -----------------------------------------------------------------------
4715 --Start of Comments
4716 --Name: sync_with_award_wf
4717 --Function:
4718 -- Sync of modifications, by updating the received and invoiced data from
4719 -- the award to modification
4720 --Parameters:
4721 --IN:
4722 --IN OUT:
4723 --OUT:
4724 --x_return_status
4725 -- Return status of the API.
4726 -- FND_API.G_RET_STS_SUCCESS if API is successful
4727 -- FND_API.G_RET_STS_ERR if there are user errors
4728 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
4729 --End of Comments
4730 ------------------------------------------------------------------------
4731 procedure sync_with_award_wf(itemtype in varchar2,
4732 itemkey in varchar2,
4733 actid in number,
4734 funcmode in varchar2,
4735 resultout out NOCOPY varchar2) is
4736
4737 x_progress varchar2(100);
4738 x_resultout varchar2(30);
4739
4740 l_doc_string varchar2(200);
4741
4742 l_draftid number;
4743 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
4744 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
4745 l_document_id NUMBER;
4746 l_return_status varchar2(1);
4747 l_errormessage varchar2(2000);
4748 l_return_status_mod varchar2(1);
4749 l_base_rev_num PO_HEADERS_ALL.revision_num%TYPE;
4750 l_mod_rev_num PO_HEADERS_ALL.revision_num%TYPE;
4751 BEGIN
4752
4753 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.sync_with_award_wf: 01';
4754
4755 g_wf_item_type := itemtype;
4756 g_wf_item_key := itemkey;
4757
4758 IF (g_po_wf_debug = 'Y') THEN
4759 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
4760 END IF;
4761
4762
4763 -- Do nothing in cancel or timeout mode
4764 --
4765 if (funcmode <> wf_engine.eng_run) then
4766
4767 resultout := wf_engine.eng_null;
4768 return;
4769
4770 end if;
4771
4772 l_draftid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
4773 itemkey => itemkey,
4774 aname => 'DRAFT_ID');
4775
4776 l_document_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
4777 itemkey => itemkey,
4778 aname => 'DOCUMENT_TYPE');
4779
4780 l_document_subtype := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
4781 itemkey => itemkey,
4782 aname => 'DOCUMENT_SUBTYPE');
4783
4784 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
4785 itemkey => itemkey,
4786 aname => 'DOCUMENT_ID');
4787
4788 sync_with_award_process(
4789 l_draftid,
4790 l_document_type,
4791 l_document_id,
4792 'N',
4793 l_return_status,
4794 l_errormessage
4795 );
4796
4797 -- <Conc Mods Project>
4798 IF l_draftid <> -1 THEN
4799 sync_mod_updatable_attrs(
4800 l_draftid,
4801 l_return_status_mod
4802 );
4803
4804 IF l_return_status_mod = FND_API.G_RET_STS_SUCCESS THEN
4805 -- <Bug 13590529>
4806 -- Sync OKC data only if revision number has changed on the base document
4807 SELECT revision_num
4808 INTO l_base_rev_num
4809 FROM po_headers_all
4810 WHERE po_header_id = l_document_id;
4811
4812 SELECT revision_num
4813 INTO l_mod_rev_num
4814 FROM po_headers_draft_all
4815 WHERE po_header_id = l_document_id
4816 AND draft_id = l_draftid;
4817
4818 IF NVL(l_base_rev_num, 0) > NVL(l_mod_rev_num, 0) THEN
4819 PO_CONTERMS_UTL_GRP.copy_conterms_mod(
4820 p_document_id => l_document_id,
4821 p_draft_id => l_draftid,
4822 p_to_mod => 'Y',
4823 p_mode => 'SYNC',
4824 x_return_status => l_return_status_mod
4825 );
4826 END IF;
4827 END IF;
4828
4829 ELSE
4830 l_return_status_mod := FND_API.G_RET_STS_SUCCESS;
4831 END IF;
4832
4833 IF l_return_status = FND_API.G_RET_STS_SUCCESS
4834 AND l_return_status_mod = FND_API.G_RET_STS_SUCCESS THEN
4835 resultout:='COMPLETE:'||'SUCCESS';
4836
4837 --commit the code upto this point,because po_drafts table is getting updated
4838 --for the row corresponding to draft_id=p_draft_id
4839 --and this table will again get updated for the same row in
4840 -- PO_DRAFTS_PVT.update_draft_status which is called from
4841 -- PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous,an autonoumous transaction,
4842 -- via a call to TRANSFER_DRAFT_TO_TXN.
4843 -- this results in deadlock ERROR
4844 --similarly other tables also create deadlock error if the code isnt commited till this point.
4845 -- hence commiting the code here to release locks
4846 COMMIT;
4847 else
4848 resultout:='COMPLETE:'||'FAILURE';
4849
4850 end if;
4851 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
4852 itemkey => itemkey,
4853 aname => 'ONLINE_REPORT_TEXT',
4854 avalue => l_errormessage);
4855 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.sync_with_award_wf: 02'||resultout;
4856 IF (g_po_wf_debug = 'Y') THEN
4857 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
4858 END IF;
4859
4860 EXCEPTION
4861 when others then
4862 raise;
4863 END;
4864
4865 -----------------------------------------------------------------------
4866 --Start of Comments
4867 --Name: sync_with_award
4868 --Function:
4869 -- Sync of modifications, by updating the received and invoiced data from
4870 -- the award to modification
4871 --Parameters:
4872 --IN:
4873 -- p_draft_id Draft Id
4874 -- p_document_type Document Type
4875 -- p_document_id Document ID
4876 -- p_reject_if_error Y/N indicate whether the mod has to be rejected
4877 -- in the case of sync error.
4878 --IN OUT:
4879 --OUT:
4880 --x_return_status
4881 -- Return status of the API.
4882 -- FND_API.G_RET_STS_SUCCESS if API is successful
4883 -- FND_API.G_RET_STS_ERR if there are user errors
4884 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
4885 --x_results
4886 -- Error messages concatenated.
4887 --End of Comments
4888 ------------------------------------------------------------------------
4889 procedure sync_with_award(p_draft_id IN NUMBER,
4890 p_document_type IN VARCHAR2,
4891 p_document_id IN NUMBER,
4892 p_reject_if_error IN VARCHAR2,
4893 x_return_status OUT NOCOPY VARCHAR2,
4894 x_results OUT NOCOPY VARCHAR2) is
4895 pragma AUTONOMOUS_TRANSACTION;
4896 l_return_status_mod VARCHAR2(1);
4897 BEGIN
4898 log_debug('sync_with_award: start ');
4899
4900 sync_with_award_process(
4901 p_draft_id
4902 ,p_document_type
4903 ,p_document_id
4904 ,p_reject_if_error
4905 ,x_return_status
4906 ,x_results);
4907
4908 log_debug('sync_with_award: End :'||'x_Return_status :'||
4909 x_return_status||
4910 ' x_results '||x_results);
4911
4912 if x_return_status = FND_API.G_RET_STS_SUCCESS then
4913 sync_mod_updatable_attrs(
4914 p_draft_id,
4915 l_return_status_mod
4916 );
4917
4918 IF l_return_status_mod = FND_API.G_RET_STS_SUCCESS THEN
4919 -- Sync OKC data
4920 PO_CONTERMS_UTL_GRP.copy_conterms_mod(
4921 p_document_id => p_document_id,
4922 p_draft_id => p_draft_id,
4923 p_to_mod => 'Y',
4924 p_mode => 'SYNC', -- <Conc Mods Project>
4925 x_return_status => l_return_status_mod
4926 );
4927 END IF;
4928
4929 x_return_status := l_return_status_mod;
4930 end if;
4931
4932 if x_return_status = FND_API.G_RET_STS_SUCCESS then
4933 commit;
4934 else
4935 rollback;
4936 end if;
4937
4938 END;
4939
4940 -------------------------------------------------------------------------------
4941 --Bug 13541195 : Auto Generation of change description not happening
4942 --Name: ACQUIRE_SYNC_SGD_LOCK
4943 --Procedure to acquire 'MOD_SYNC' lock
4944 --End of Comments
4945 -------------------------------------------------------------------------------
4946
4947 PROCEDURE ACQUIRE_SYNC_SGD_LOCK (itemtype IN VARCHAR2,
4948 itemkey IN VARCHAR2,
4949 actid IN NUMBER,
4950 funcmode IN VARCHAR2,
4951 resultout OUT NOCOPY VARCHAR2) is
4952
4953 l_draft_id NUMBER;
4954 l_org_id NUMBER;
4955 l_po_header_id PO_HEADERS_ALL.po_header_id%type;
4956 l_doc_sub_type VARCHAR2(10);
4957 l_doc_type VARCHAR2(10);
4958 x_progress varchar2(1000);
4959 l_request_id number;
4960 l_revision_number NUMBER;
4961
4962 PRAGMA AUTONOMOUS_TRANSACTION ;
4963
4964 BEGIN
4965
4966 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.ACQUIRE_SYNC_SGD_LOCK: 01';
4967
4968 IF (g_po_wf_debug = 'Y') THEN
4969 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
4970 END IF;
4971
4972 l_draft_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'DRAFT_ID');
4973
4974 SELECT document_id
4975 INTO l_po_header_id
4976 FROM po_drafts
4977 WHERE draft_id = l_draft_id;
4978
4979 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.ACQUIRE_SYNC_SGD_LOCK: l_draft_id: '||l_draft_id||', l_po_header_id: '||l_po_header_id||', l_doc_sub_type: '||l_doc_sub_type||', l_doc_type: '||l_doc_type;
4980
4981
4982 IF (g_po_wf_debug = 'Y') THEN
4983 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
4984 END IF;
4985
4986 --insert a row in po entity locks table
4987
4988 INSERT INTO po_entity_locks(
4989 po_entity_lock_id,
4990 entity_name,
4991 entity_pk1,
4992 lock_by_draft_id,
4993 lock_type,
4994 last_update_date,
4995 last_updated_by,
4996 creation_date,
4997 created_by
4998 )
4999 VALUES(
5000 po_entity_locks_s.NEXTVAL,
5001 G_MOD_SYNC_LOCK,
5002 l_po_header_id,
5003 l_draft_id,
5004 'F',
5005 SYSDATE,
5006 FND_GLOBAL.user_id,
5007 SYSDATE,
5008 FND_GLOBAL.user_id
5009 );
5010
5011 COMMIT;
5012 END ACQUIRE_SYNC_SGD_LOCK;
5013
5014 -------------------------------------------------------------------------------
5015 --Bug 13541195 : Auto Generation of change description not happening
5016 --Name: IS_LOCK_FOR_SGD_GEN_AVAILABLE
5017 --Procedure to check if lock for sgd generation is available
5018 --End of Comments
5019 -------------------------------------------------------------------------------
5020
5021 PROCEDURE IS_LOCK_FOR_SGD_GEN_AVAILABLE (itemtype IN VARCHAR2,
5022 itemkey IN VARCHAR2,
5023 actid IN NUMBER,
5024 funcmode IN VARCHAR2,
5025 resultout OUT NOCOPY VARCHAR2)
5026 IS
5027 l_is_lock_available VARCHAR2(1) := 'Y';
5028 l_draft_id NUMBER;
5029 l_po_header_id NUMBER;
5030 x_progress VARCHAR2(1000);
5031
5032 BEGIN
5033
5034 l_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
5035 itemkey => itemkey,
5036 aname => 'DRAFT_ID');
5037
5038 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_LOCK_FOR_SGD_GEN_AVAILABLE: Draft_id: ' || l_draft_id;
5039
5040 IF (g_po_wf_debug = 'Y') THEN
5041 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5042 END IF;
5043
5044 BEGIN
5045 SELECT document_id
5046 INTO l_po_header_id
5047 FROM po_drafts
5048 WHERE draft_id = l_draft_id;
5049
5050 SELECT 'N'
5051 INTO l_is_lock_available
5052 FROM po_entity_locks
5053 WHERE entity_pk1 = l_po_header_id
5054 AND entity_name = G_MOD_SYNC_LOCK
5055 AND lock_by_draft_id <> l_draft_id;
5056
5057 EXCEPTION
5058 WHEN No_Data_Found THEN
5059 IF (g_po_wf_debug = 'Y') THEN
5060 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_LOCK_FOR_SGD_GEN_AVAILABLE: in no data found exception';
5061 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
5062 END IF;
5063 END;
5064
5065 IF (g_po_wf_debug = 'Y') THEN
5066 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_LOCK_FOR_SGD_GEN_AVAILABLE value is : '|| l_is_lock_available;
5067 END IF;
5068
5069 resultout := wf_engine.eng_completed || ':' || l_is_lock_available;
5070
5071 EXCEPTION
5072 WHEN OTHERS THEN
5073 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_LOCK_FOR_SGD_GEN_AVAILABLE: Exception';
5074 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5075 RAISE;
5076 END;
5077
5078 -------------------------------------------------------------------------------
5079 --Bug 13541195 : Auto Generation of change description not happening
5080 --Name: IS_SGD_GENERATION_REQUIRED
5081 --Procedure to check if sgd generation is required
5082 --End of Comments
5083 -------------------------------------------------------------------------------
5084
5085 PROCEDURE IS_SGD_GENERATION_REQUIRED (itemtype IN VARCHAR2,
5086 itemkey IN VARCHAR2,
5087 actid IN NUMBER,
5088 funcmode IN VARCHAR2,
5089 resultout OUT NOCOPY VARCHAR2)
5090 IS
5091 l_po_header_id NUMBER;
5092 l_draft_id NUMBER;
5093 l_is_sgd_generation_req VARCHAR2(1) := 'N';
5094 x_progress VARCHAR2(1000);
5095 l_draft_rev_num NUMBER;
5096 l_header_base_rev_num NUMBER;
5097 draft_revision_num NUMBER;
5098 BEGIN
5099
5100 l_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
5101 itemkey => itemkey,
5102 aname => 'DRAFT_ID');
5103
5104 --Fetch the header id
5105 SELECT document_id
5106 INTO l_po_header_id
5107 FROM po_drafts
5108 WHERE draft_id = l_draft_id;
5109
5110 SELECT revision_num INTO l_draft_rev_num FROM po_headers_draft_all WHERE po_header_id = l_po_header_id AND draft_id = l_draft_id;
5111 SELECT revision_num INTO l_header_base_rev_num FROM po_headers_all WHERE po_header_id = l_po_header_id;
5112 SELECT revision_num INTO draft_revision_num FROM po_drafts WHERE draft_id = l_draft_id;
5113
5114 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_SGD_GENERATION_REQUIRED: Draft_id: '
5115 || l_draft_id || 'l_draft_rev_num : '|| l_draft_rev_num || 'l_header_base_rev_num '|| l_header_base_rev_num || 'draft_revision_num '|| draft_revision_num;
5116
5117 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5118
5119 -- Check if the base award version is greater than the draft version or the award has been update w/o mod
5120 IF((l_draft_rev_num < l_header_base_rev_num) OR draft_revision_num = -1 )
5121 THEN
5122 l_is_sgd_generation_req := 'Y';
5123 END IF;
5124
5125 resultout := wf_engine.eng_completed || ':' || l_is_sgd_generation_req;
5126 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_SGD_GENERATION_REQUIRED value is '|| l_is_sgd_generation_req ;
5127 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
5128
5129 EXCEPTION
5130 WHEN No_Data_Found THEN
5131 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_SGD_GENERATION_REQUIRED: in no data found exception';
5132 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
5133 WHEN OTHERS THEN
5134 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.IS_SGD_GENERATION_REQUIRED: Exception';
5135 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5136 RAISE;
5137 END;
5138
5139
5140 -------------------------------------------------------------------------------
5141 --Bug 13541195 : Auto Generation of change description not happening
5142 --Function to check if lock for sgd generation is available
5143 --Name: launch_sgd_concurrent_program
5144 -- This procedure is calls the sgd generation concurrent program
5145 --End of Comments
5146 -------------------------------------------------------------------------------
5147 procedure LAUNCH_SGD_CONCURRENT_PROGRAM (itemtype in varchar2,
5148 itemkey in varchar2,
5149 actid in number,
5150 funcmode in varchar2,
5151 resultout out NOCOPY varchar2 ) is
5152
5153 l_draft_id NUMBER;
5154 l_org_id NUMBER;
5155 l_po_header_id PO_HEADERS_ALL.po_header_id%type;
5156 l_doc_sub_type VARCHAR2(10);
5157 l_doc_type VARCHAR2(10);
5158 x_progress varchar2(1000);
5159 l_request_id number;
5160 l_revision_number NUMBER;
5161 l_template_code po_print_form_formats.DOC_FORMAT_TMPL_CODE%TYPE;
5162 l_cd_territory fnd_languages.iso_territory%TYPE;
5163 l_lang_code fnd_languages.language_code%TYPE;
5164 l_application_name VARCHAR(2):= 'PO';
5165 BEGIN
5166
5167 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.LAUNCH_SGD_CONCURRENT_PROGRAM: 01';
5168 IF (g_po_wf_debug = 'Y') THEN
5169 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
5170 END IF;
5171
5172 l_draft_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'DRAFT_ID');
5173
5174 SELECT document_id
5175 INTO l_po_header_id
5176 FROM po_drafts
5177 WHERE draft_id = l_draft_id;
5178
5179 l_doc_sub_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
5180 itemkey => itemkey,
5181 aname => 'DOCUMENT_SUBTYPE');
5182 l_doc_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
5183 itemkey => itemkey,
5184 aname => 'DOCUMENT_TYPE');
5185 l_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
5186 itemkey => itemkey,
5187 aname => 'ORG_ID');
5188
5189 SELECT revision_num into l_revision_number FROM po_headers_all WHERE po_header_id = l_po_header_id;
5190
5191 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.LAUNCH_SGD_CONCURRENT_PROGRAM: l_draft_id: '||l_draft_id||', l_po_header_id: '||
5192 l_po_header_id||', l_doc_sub_type: '||l_doc_sub_type||', l_doc_type: '||l_doc_type ||'l_org_id :'|| l_org_id ||
5193 'l_revision_number '|| l_revision_number;
5194
5195 IF (g_po_wf_debug = 'Y') THEN
5196 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
5197 END IF;
5198
5199
5200 select pf.DOC_FORMAT_TMPL_CODE, f.iso_territory, f.language_code
5201 into l_template_code,l_cd_territory,l_lang_code
5202 from po_print_form_formats pf, po_drafts h, po_headers_all h1, fnd_languages f
5203 where pf.FORM_SOURCE = 'PO' and pf.DOCUMENT_TYPE = 'PO_MOD_STD_FORM' and pf.STANDARD_FORM = h.CLM_STANDARD_FORM
5204 and pf.DOCUMENT_FORMAT = h.CLM_DOCUMENT_FORMAT and h1.po_header_id = h.document_id
5205 and pf.STYLE_ID = h1.STYLE_ID and h.draft_id = l_draft_id and f.language_code = USERENV('LANG');
5206
5207 --Submitting concurrent program 'Generate Change Description'
5208 l_request_id := fnd_request.submit_request(application =>'PO',
5209 program =>'POCHGDES',
5210 description => 'Genearting SGD',
5211 start_time => SYSDATE,
5212 sub_request => FALSE,
5213 argument1 => l_draft_id,
5214 argument2 => l_po_header_id,
5215 argument3 => l_revision_number,
5216 argument4 => l_application_name,
5217 argument5 => l_cd_territory,
5218 argument6 => l_template_code,
5219 argument7 => 'RTF',
5220 argument8 => l_lang_code,
5221 argument9 => l_org_id,
5222 argument10 =>l_doc_type,
5223 argument11 =>l_doc_sub_type,
5224 argument12 =>l_application_name );
5225
5226
5227 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.LAUNCH_SGD_CONCURRENT_PROGRAM: : Request id is - '|| l_request_id;
5228
5229 IF (g_po_wf_debug = 'Y') THEN
5230 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
5231 END IF;
5232
5233
5234 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
5235 itemkey => itemkey,
5236 aname => 'REQUEST_ID',
5237 avalue => l_request_id);
5238
5239 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5240
5241 EXCEPTION
5242 WHEN OTHERS THEN
5243 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.LAUNCH_SGD_CONCURRENT_PROGRAM: Error: '||sqlerrm;
5244 IF (g_po_wf_debug = 'Y') THEN
5245 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
5246 END IF;
5247 resultout := wf_engine.eng_completed || ':' || '';
5248
5249 END LAUNCH_SGD_CONCURRENT_PROGRAM;
5250
5251 -------------------------------------------------------------------------------
5252 --Bug 13541195 : Auto Generation of change description not happening
5253 --Procedure to ralease MOD_SYNC lock after SGD geneartion
5254 --Name: launch_sgd_concurrent_program
5255 -- This procedure is calls the sgd generation concurrent program
5256 --End of Comments
5257 -------------------------------------------------------------------------------
5258
5259 PROCEDURE RELEASE_SYNC_SGD_LOCK (itemtype IN VARCHAR2,
5260 itemkey IN VARCHAR2,
5261 actid IN NUMBER,
5262 funcmode IN VARCHAR2,
5263 resultout OUT NOCOPY VARCHAR2)
5264 IS
5265 l_is_lock_available VARCHAR2(1) := 'Y';
5266 l_draft_id NUMBER;
5267 l_po_header_id NUMBER;
5268 x_progress VARCHAR2(1000);
5269 x_itemtype varchar2(8) := 'POAPPAME';
5270
5271
5272 --Select the item keys of the work flow processes which are waiting for MOD_SYNC lock
5273 CURSOR waiting_process_wf_keys(l_draft_id IN Number) IS
5274 SELECT item_key FROM wf_item_activity_statuses_v
5275 WHERE user_key = (SELECT segment1 FROM po_headers_all
5276 WHERE po_header_id = (SELECT document_id
5277 FROM po_drafts
5278 WHERE draft_id = l_draft_id))
5279 AND activity_name = 'WAIT_FOR_LOCK_TO_BE_RELEASED' AND activity_status_code = 'NOTIFIED' ;
5280
5281 PRAGMA AUTONOMOUS_TRANSACTION ;
5282
5283 BEGIN
5284
5285 l_draft_id := po_wf_util_pkg.Getitemattrnumber (itemtype => itemtype,
5286 itemkey => itemkey,
5287 aname => 'DRAFT_ID');
5288
5289 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: Draft_id: '
5290 || l_draft_id;
5291
5292 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5293
5294 BEGIN
5295 SELECT document_id
5296 INTO l_po_header_id
5297 FROM po_drafts
5298 WHERE draft_id = l_draft_id;
5299
5300 delete
5301 FROM po_entity_locks
5302 WHERE lock_by_draft_id = l_draft_id
5303 AND entity_pk1 = l_po_header_id
5304 AND entity_name = G_MOD_SYNC_LOCK;
5305
5306 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: No Of rows deleted from po_entity_locks table : ' || SQL%ROWCOUNT;
5307 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5308
5309 FOR item_key IN waiting_process_wf_keys(l_draft_id) LOOP
5310 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: Item keys of waiting process : ' || item_key.item_key;
5311 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5312 --Execute complete activity on these process to resume
5313 wf_engine.CompleteActivity(x_itemtype,item_key.item_key,'WAIT_FOR_LOCK_TO_BE_RELEASED','ACTIVITY_PERFORMED');
5314 END LOOP;
5315
5316 COMMIT;
5317
5318 EXCEPTION
5319 WHEN No_Data_Found THEN
5320 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: in no data found exception';
5321 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
5322 END;
5323
5324 EXCEPTION
5325 WHEN OTHERS THEN
5326 x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: Exception';
5327 po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
5328 RAISE;
5329 END RELEASE_SYNC_SGD_LOCK;
5330
5331 END;