[Home] [Help]
PACKAGE BODY: APPS.PO_LINK_REQ_DIST_PVT
Source
1 PACKAGE BODY PO_LINK_REQ_DIST_PVT AS
2 /* $Header: PO_LINK_REQ_DIST_PVT.plb 120.17.12020000.4 2013/02/27 18:55:34 akyanama ship $ */
3
4
5 D_PKG_NAME CONSTANT VARCHAR2(50) :=
6 PO_LOG.GET_PACKAGE_BASE('PO_LINK_REQ_DIST_PVT');
7
8 G_GL_DATE DATE;
9 G_PREVIOUS_GL_DATE DATE ;
10 G_GL_PERIOD_NAME VARCHAR2(30);
11
12
13 PROCEDURE DEBUG
14 (
15 P_MODULE IN VARCHAR2,
16 P_PROGRESS IN NUMBER,
17 P_MSG IN VARCHAR2
18 )
19 IS
20 BEGIN
21 -- WRITE THE MESSAGE TO THE FND LOG, IF ENABLED.
22 IF PO_LOG.D_STMT THEN
23 PO_LOG.STMT(P_MODULE, P_PROGRESS, P_MSG);
24 END IF;
25
26
27 END DEBUG;
28
29
30 FUNCTION GET_GL_PERIOD_NAME RETURN VARCHAR2 IS
31
32 D_API_NAME CONSTANT VARCHAR2(30) := 'GET_GL_PERIOD_NAME';
33 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
34 D_POSITION NUMBER;
35
36 BEGIN
37 D_POSITION := 0;
38 IF (PO_LOG.D_PROC) THEN
39 PO_LOG.PROC_BEGIN(D_MODULE,'G_GL_PERIOD_NAME',G_GL_PERIOD_NAME);
40 END IF;
41
42 D_POSITION := 10;
43 IF (PO_LOG.D_PROC) THEN
44 PO_LOG.PROC_END(D_MODULE);
45 END IF;
46
47 RETURN G_GL_PERIOD_NAME;
48
49 END;
50
51
52 FUNCTION GET_GL_DATE(p_gl_date DATE, p_sob_id NUMBER) RETURN DATE IS
53
54 l_gl_date DATE;
55
56 D_API_NAME CONSTANT VARCHAR2(30) := 'GET_GL_DATE';
57 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
58 D_POSITION NUMBER;
59
60
61 BEGIN
62
63 D_POSITION := 0;
64 IF (PO_LOG.D_PROC) THEN
65
66 PO_LOG.PROC_BEGIN(D_MODULE,'p_gl_date,p_sob_id, G_PREVIOUS_GL_DATE :',p_gl_date||' ,'||p_sob_id||' ,'||G_PREVIOUS_GL_DATE);
67
68 END IF;
69
70 l_gl_date := p_gl_date;
71
72 IF (p_gl_date <> Nvl(G_PREVIOUS_GL_DATE, p_gl_date -1) ) THEN
73
74 G_PREVIOUS_GL_DATE := p_gl_date;
75
76 PO_PERIODS_SV.build_GL_Encumbered_Date(l_sob_id => p_sob_id
77 ,x_gl_date => l_gl_date
78 ,x_gl_period => G_GL_PERIOD_NAME);
79 G_GL_DATE := l_gl_date;
80
81 D_POSITION := 15;
82 DEBUG(D_MODULE, D_POSITION, 'l_gl_date: '||l_gl_date ||'G_GL_DATE: '||G_GL_DATE || ' G_GL_PERIOD_NAME:'||G_GL_PERIOD_NAME);
83
84 END IF;
85
86 D_POSITION := 20;
87 DEBUG(D_MODULE, D_POSITION, 'l_gl_date: '||l_gl_date ||'G_GL_DATE: '||G_GL_DATE || ' G_GL_PERIOD_NAME:'||G_GL_PERIOD_NAME);
88
89
90 D_POSITION := 30;
91 IF (PO_LOG.D_PROC) THEN
92 PO_LOG.PROC_END(D_MODULE);
93 END IF;
94
95
96 RETURN G_GL_DATE;
97
98 END;
99
100
101
102 --START OF COMMENTS
103 --NAME: CREATE_LINK_REQ_DIST_GT
104 --PRE-REQS:
105 -- NONE.
106 --MODIFIES:
107 -- NONE.
108 --LOCKS:
109 -- NONE.
110 --FUNCTION:
111 -- IT CREATES(DEFAULTS) THE NEW DISTRIBUTIONS FOR THE SELECTED SCHEDULES AND THE REQUISITIONS AND CALCULATES THE DEFAULT VALUES,
112 -- UPDATES THE DISTRIBUTION NUMBER
113 --PARAMETERS:
114 --IN:
115 --P_TOTAL_DIST_AMT_REQUIRED
116 -- TOTAL DISTRIBUTION AMOUNT REQUIRED FROM THE SCHEDULES
117 --P_TOTAL_FUNDS_TO_USE
118 -- TOTAL FUNDS TO USE FROM REQUISITIONS
119 --P_LINK_REQ_DIST_FU_TBL
120 -- COMBINATION OF DISTRIBUTION ID AND THE FUNDS TO USE FOR SELECTED REQUISITIONS DISTRIBUTIONS
121 --IN OUT:
122 --X_RESULT
123 -- SUCCESS FAILURE RESPONSE
124 --X_ERROR_MESSAGE
125 -- ERROR MESSAGE
126 --TESTING:
127 -- NONE.
128 --END OF COMMENTS
129
130 PROCEDURE CREATE_LINK_REQ_DIST_GT
131 (
132 -- STANDARD API PARAMS
133 P_API_VERSION IN NUMBER,
134 -- INPUT PARAMS
135 P_TOTAL_DIST_AMT_REQUIRED IN NUMBER,
136 P_TOTAL_FUNDS_TO_USE IN NUMBER,
137 -- TABLE PARAMS IN
138 X_RESULT IN OUT NOCOPY NUMBER,
139 X_ERROR_MESSAGE IN OUT NOCOPY VARCHAR2,
140 P_LINK_REQ_DIST_FU_TBL IN OUT NOCOPY PO_LINK_REQ_DIST_FU_TAB
141 -- SOME MORE INPUT PARAMS
142 )
143 IS
144 D_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_LINK_REQ_DIST_GT';
145 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
146 D_POSITION NUMBER;
147
148
149 L_ERROR_CODE VARCHAR2(2000);
150 L_DERIVED_TFU VARCHAR2(2000);
151 L_NUM_DISTRIBUTIONS NUMBER;
152
153 L_TEMP NUMBER;
154 L_DRAFT_ID NUMBER;
155 L_HEADER_ID NUMBER;
156 L_RATE NUMBER;
157 L_SOB NUMBER;
158 L_ORG_ID NUMBER;
159 L_CURRENCY_CODE VARCHAR2(5);
160 L_FUNC_CURRENCY_CODE VARCHAR2(5);
161 L_EXPENSE_ACCRUAL_CD VARCHAR2(20);
162 L_TOTAL_FUNDS_TO_USE NUMBER;
163
164
165 X_PRECISION NUMBER := NULL;
166 X_EXT_PRECISION NUMBER := NULL;
167 X_MIN_ACCT_UNIT NUMBER := NULL;
168
169 X_BASE_PRECISION NUMBER := NULL;
170 X_BASE_EXT_PRECISION NUMBER := NULL;
171 X_BASE_MIN_ACCT_UNIT NUMBER := NULL;
172
173 CURSOR C_SHIPMENT_LIST IS
174 SELECT INDEX_NUM2 DRAFT_ID,
175 INDEX_NUM1 LINELOCATION_ID
176 FROM PO_SESSION_GT
177 WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
178
179 CURSOR C_DIST_NUM_MAX_LIST IS
180 SELECT GT.LINE_LOCATION_ID, Nvl(MAX(POD.DISTRIBUTION_NUM),0) MAX_DIST_NUM
181 FROM PO_DISTRIBUTIONS_MERGE_V POD,
182 PO_DISTRIBUTIONS_GT GT
183 WHERE GT.LINE_LOCATION_ID = POD.LINE_LOCATION_ID(+)
184 GROUP BY GT.LINE_LOCATION_ID;
185
186
187 BEGIN
188
189 D_POSITION := 0;
190 IF (PO_LOG.D_PROC) THEN
191 PO_LOG.PROC_BEGIN(D_MODULE,'P_TOTAL_DIST_AMT_REQUIRED',P_TOTAL_DIST_AMT_REQUIRED);
192 PO_LOG.PROC_BEGIN(D_MODULE,'P_TOTAL_FUNDS_TO_USE',P_TOTAL_FUNDS_TO_USE);
193
194 END IF;
195 --DBMS_OUTPUT.PUT_LINE('1');
196
197
198 D_POSITION := 20;
199 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE HEADER ID AND DRAFT ID');
200
201 SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
202 FROM PO_SESSION_GT
203 WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
204
205
206 D_POSITION := 30;
207 DEBUG(D_MODULE, D_POSITION, 'HEADER ID: '||L_HEADER_ID||' DRAFT ID: '|| L_DRAFT_ID);
208 --DBMS_OUTPUT.PUT_LINE('2');
209
210 --
211 L_TOTAL_FUNDS_TO_USE := P_TOTAL_FUNDS_TO_USE;
212
213
214 IF (L_DRAFT_ID <> -1 ) THEN
215
216 D_POSITION := 50;
217 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE CURRENCY CODES, SOB, ORG ID ,RATE');
218 --DBMS_OUTPUT.PUT_LINE('4');
219
220
221 SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE, GLSOB.SET_OF_BOOKS_ID, PH.ORG_ID
222 INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE, L_SOB, L_ORG_ID
223 FROM PO_HEADERS_DRAFT_ALL PH,
224 GL_SETS_OF_BOOKS GLSOB,
225 ORG_ORGANIZATION_DEFINITIONS OOD
226 WHERE PH.PO_HEADER_ID = L_HEADER_ID
227 AND PH.DRAFT_ID = L_DRAFT_ID
228 AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
229 AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
230
231 ELSE
232
233 D_POSITION := 60;
234 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE CURRENCY CODES, SOB, ORG ID ,RATE');
235
236 SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE, GLSOB.SET_OF_BOOKS_ID, PH.ORG_ID
237 INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE, L_SOB, L_ORG_ID
238 FROM PO_HEADERS_ALL PH,
239 GL_SETS_OF_BOOKS GLSOB,
240 ORG_ORGANIZATION_DEFINITIONS OOD
241 WHERE PH.PO_HEADER_ID = L_HEADER_ID
242 AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
243 AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
244
245
246 END IF;
247
248 D_POSITION := 70;
249 DEBUG(D_MODULE, D_POSITION, 'L_CURRENCY_CODE: '||L_CURRENCY_CODE||',L_RATE: '|| L_RATE ||',L_FUNC_CURRENCY_CODE: '
250 || L_FUNC_CURRENCY_CODE||',L_SOB: '||
251 L_SOB||',L_ORG_ID: '|| L_ORG_ID);
252
253
254 BEGIN
255
256 D_POSITION := 80;
257 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE EXPENSE ACCRUAL CODES');
258
259 SELECT EXPENSE_ACCRUAL_CODE
260 INTO L_EXPENSE_ACCRUAL_CD
261 FROM PO_SYSTEM_PARAMETERS PSP
262 WHERE ORG_ID = L_ORG_ID
263 AND ROWNUM < 2 ;
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 L_EXPENSE_ACCRUAL_CD := NULL;
268 END;
269
270 D_POSITION := 90;
271 DEBUG(D_MODULE, D_POSITION, 'L_EXPENSE_ACCRUAL_CD: '||L_EXPENSE_ACCRUAL_CD);
272
273 FND_CURRENCY.GET_INFO (L_CURRENCY_CODE,
274 X_PRECISION,
275 X_EXT_PRECISION,
276 X_MIN_ACCT_UNIT);
277
278 D_POSITION := 150;
279 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE SESSION GT. X_PRECISION: '||X_PRECISION||',X_EXT_PRECISION: '
280 ||X_EXT_PRECISION||',X_MIN_ACCT_UNIT: '||X_MIN_ACCT_UNIT);
281
282 FND_CURRENCY.GET_INFO (L_FUNC_CURRENCY_CODE,
283 X_BASE_PRECISION,
284 X_BASE_EXT_PRECISION,
285 X_BASE_MIN_ACCT_UNIT);
286
287 D_POSITION := 155;
288 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE SESSION GT. X_BASE_PRECISION: '||X_BASE_PRECISION||',X_BASE_EXT_PRECISION: '
289 ||X_BASE_EXT_PRECISION||',X_BASE_MIN_ACCT_UNIT: '||X_BASE_MIN_ACCT_UNIT);
290
291
292 -- HOLDS FUNDES TO USE( FUNC CURR) BEFORE THE CURRENCY RATE
293 UPDATE PO_SESSION_GT GT
294 SET NUM1 = (SELECT FUNDS_TO_USE FROM TABLE(P_LINK_REQ_DIST_FU_TBL) TBL
295 WHERE TBL.DISTRIBUTION_ID = GT.INDEX_NUM1 AND GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID')
296 WHERE GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID';
297
298 D_POSITION := 160;
299 DEBUG(D_MODULE, D_POSITION, 'SESSION GT UPDATED WITH FUNDS TO USE.');
300
301 -- FUNCTIONAL CURRENCY IS DIFFERENT FROM THE DOCUMENT CURRENY
302 IF L_CURRENCY_CODE <> L_FUNC_CURRENCY_CODE THEN
303
304 D_POSITION := 170;
305 DEBUG(D_MODULE, D_POSITION, 'FUNCTIONAL CURRENCY AND DOCUMENT CURRENCY ARE DIFFERENT, HENCE THE CONVERSION TAKING PLACE');
306 --DBMS_OUTPUT.PUT_LINE('8');
307 L_TOTAL_FUNDS_TO_USE := 0;
308
309 FOR I IN 1..P_LINK_REQ_DIST_FU_TBL.COUNT
310 LOOP
311 P_LINK_REQ_DIST_FU_TBL(I).FUNDS_TO_USE := ROUND(P_LINK_REQ_DIST_FU_TBL(I).FUNDS_TO_USE / NVL(L_RATE,1), X_PRECISION);
312
313 L_TOTAL_FUNDS_TO_USE := L_TOTAL_FUNDS_TO_USE + P_LINK_REQ_DIST_FU_TBL(I).FUNDS_TO_USE;
314
315 END LOOP;
316
317 END IF;
318
319 -- If the total funds to use is greater than total distribution amount required, we'll prorate the funds to use of requisitions based on
320 -- the total distribution amount rquited. We'l divide the TDAR in the ratio of the exitsing funds to use on requisition dists.
321 -- This below condition will never satify, as we 'll be updating the funds to use of req dists, hence total funds to use at java layer
322 -- whenever the TFU > TDAR. Even in the final apportioning logic for Dist Amount. We can remove both L_DERIVED_TFU and L_TOTAL_FUNDS_TO_USE
323 -- in the formula. We are keeping the code as is, just for the understanding of the apportioning logic
324 IF L_TOTAL_FUNDS_TO_USE > P_TOTAL_DIST_AMT_REQUIRED THEN
325 L_DERIVED_TFU := P_TOTAL_DIST_AMT_REQUIRED;
326 ELSE
327 L_DERIVED_TFU := L_TOTAL_FUNDS_TO_USE;
328 END IF;
329
330 G_GL_DATE := NULL ;
331 G_PREVIOUS_GL_DATE := NULL ;
332 G_GL_PERIOD_NAME := NULL ;
333
334 D_POSITION := 190;
335 DEBUG(D_MODULE, D_POSITION, 'L_DERIVED_TFU: '||L_DERIVED_TFU||',L_TOTAL_FUNDS_TO_USE: '||L_TOTAL_FUNDS_TO_USE);
336 DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
337 -- Insert into GT tables for Modification
338 IF (L_DRAFT_ID <> -1 ) THEN
339
340 INSERT INTO PO_DISTRIBUTIONS_GT --<SHARED PROC FPJ>
341 (PO_DISTRIBUTION_ID,
342 DRAFT_ID,
343 --WHO COLUMNS
344 ---------------------------------------------------------------
345 LAST_UPDATE_DATE,
346 LAST_UPDATED_BY,
347 LAST_UPDATE_LOGIN,
348 CREATION_DATE,
349 CREATED_BY,
350 REQUEST_ID,
351 PROGRAM_APPLICATION_ID,
352 PROGRAM_ID,
353 PROGRAM_UPDATE_DATE,
354 ---------------------------------------------------------------
355 PO_HEADER_ID,
356 PO_LINE_ID,
357 LINE_LOCATION_ID,
358 PO_RELEASE_ID,
359 REQ_DISTRIBUTION_ID,
360 SET_OF_BOOKS_ID,
361 CODE_COMBINATION_ID,
362 DELIVER_TO_LOCATION_ID,
363 DELIVER_TO_PERSON_ID,
364 QUANTITY_ORDERED,
365 QUANTITY_DELIVERED,
366 QUANTITY_BILLED,
367 QUANTITY_CANCELLED,
368 AMOUNT_ORDERED,
369 AMOUNT_DELIVERED,
370 AMOUNT_CANCELLED,
371 AMOUNT_BILLED,
372 RATE_DATE,
373 RATE,
374 ACCRUED_FLAG,
375 ENCUMBERED_FLAG,
376 GL_ENCUMBERED_PERIOD_NAME,
377 GL_ENCUMBERED_DATE,
378 DISTRIBUTION_NUM,
379 DESTINATION_TYPE_CODE,
380 DESTINATION_ORGANIZATION_ID,
381 DESTINATION_SUBINVENTORY,
382 BUDGET_ACCOUNT_ID,
383 ACCRUAL_ACCOUNT_ID,
384 VARIANCE_ACCOUNT_ID,
385 DEST_CHARGE_ACCOUNT_ID,
386 DEST_VARIANCE_ACCOUNT_ID,
387 WIP_ENTITY_ID,
388 WIP_LINE_ID,
389 WIP_REPETITIVE_SCHEDULE_ID,
390 WIP_OPERATION_SEQ_NUM,
391 WIP_RESOURCE_SEQ_NUM,
392 BOM_RESOURCE_ID,
393 PREVENT_ENCUMBRANCE_FLAG,
394 PROJECT_ID,
395 TASK_ID,
396 END_ITEM_UNIT_NUMBER,
397 EXPENDITURE_TYPE,
398 PROJECT_ACCOUNTING_CONTEXT,
399 DESTINATION_CONTEXT,
400 EXPENDITURE_ORGANIZATION_ID,
401 EXPENDITURE_ITEM_DATE,
402 ACCRUE_ON_RECEIPT_FLAG,
403 KANBAN_CARD_ID,
404 TAX_RECOVERY_OVERRIDE_FLAG,
405 RECOVERY_RATE,
406 AWARD_ID,
407 OKE_CONTRACT_LINE_ID,
408 OKE_CONTRACT_DELIVERABLE_ID,
409 ORG_ID,
410 DISTRIBUTION_TYPE,
411 PARTIAL_FUNDED_FLAG,
412 FUNDED_VALUE,
413 QUANTITY_FUNDED,
414 AMOUNT_FUNDED ,
415 GROUP_LINE_ID,
416 CLM_MISC_LOA,
417 CLM_DEFENCE_FUNDING,
418 CLM_FMS_CASE_NUMBER,
419 CLM_AGENCY_ACCT_IDENTIFIER,
420 PAR_DRAFT_ID, -- FOR LINKING PAR
421 PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
422 )
423
424 SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
425 POH.DRAFT_ID,
426 --DEFAULT WHO COLUMNS
427 ---------------------------------------------------------------
428 SYSDATE,
429 FND_GLOBAL.USER_ID,
430 FND_GLOBAL.LOGIN_ID,
431 SYSDATE,
432 FND_GLOBAL.USER_ID,
433 FND_GLOBAL.CONC_REQUEST_ID,
434 FND_GLOBAL.PROG_APPL_ID,
435 FND_GLOBAL.CONC_PROGRAM_ID,
436 SYSDATE,
437 ---------------------------------------------------------------
438
439 PLL.PO_HEADER_ID,
440 POL.PO_LINE_ID,
441 PLL.LINE_LOCATION_ID,
442 PLL.PO_RELEASE_ID,
443 PRD.DISTRIBUTION_ID,
444 NVL(L_SOB, PRD.SET_OF_BOOKS_ID),
445
446 PRD.CODE_COMBINATION_ID,
447 PRL.DELIVER_TO_LOCATION_ID,
448 DECODE(PLL.DROP_SHIP_FLAG,'Y',NULL,PRL.TO_PERSON_ID),
449 NULL, -- QUANTITY ORDERED
450 0,
451 0,
452 0,
453
454 Round(((L_DERIVED_TFU * (DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) DIST_AMT,
455 0, -- AMOUNT_DELIVERED
456 0, -- AMOUNT_CANCELLED
457 0, -- AMOUNT_BILLED
458
459 POH.RATE_DATE,
460 POH.RATE,
461 DECODE((NVL(PRL.PCARD_FLAG,'N')) ,'N','N','Y'), --X_ACCRUED_FLAG,
462 'N',--ENCUMBERED_FLAG
463 -- if the profile option po_autocreate_date is set to req gl date
464 -- use req's gl date to build encum gl date, otherwise use sysdate.
465 GET_GL_PERIOD_NAME,
466 GET_GL_DATE(
467 DECODE( FND_PROFILE.VALUE('PO_AUTOCREATE_DATE')
468 , 'REQ GL DATE', PRD.GL_ENCUMBERED_DATE
469 , TRUNC(SYSDATE) ), L_SOB),
470 --L_PERIOD_NAME,
471 -1, --PDI.DISTRIBUTION_NUM,
472 PRL.DESTINATION_TYPE_CODE,
473 PRL.DESTINATION_ORGANIZATION_ID,
474 PRL.DESTINATION_SUBINVENTORY,
475 PRD.BUDGET_ACCOUNT_ID,
476 PRD.ACCRUAL_ACCOUNT_ID,
477 PRD.VARIANCE_ACCOUNT_ID,
478
479 NULL, --PDI.DEST_CHARGE_ACCOUNT_ID,
480 NULL, --PDI.DEST_VARIANCE_ACCOUNT_ID,
481
482 PRL.WIP_ENTITY_ID,
483 PRL.WIP_LINE_ID,
484 PRL.WIP_REPETITIVE_SCHEDULE_ID,
485 PRL.WIP_OPERATION_SEQ_NUM,
486 PRL.WIP_RESOURCE_SEQ_NUM,
487 PRL.BOM_RESOURCE_ID,
488 'N', --PREVENT_ENCUMBRANCE_FLAG
489 PRD.PROJECT_ID,
490 PRD.TASK_ID,
491 PRD.END_ITEM_UNIT_NUMBER,
492 PRD.EXPENDITURE_TYPE,
493 PRD.PROJECT_ACCOUNTING_CONTEXT,
494 PRL.DESTINATION_CONTEXT,
495 PRD.EXPENDITURE_ORGANIZATION_ID,
496 PRD.EXPENDITURE_ITEM_DATE,
497 DECODE(RECEIPT_REQUIRED_FLAG, 'N','N',DECODE(L_EXPENSE_ACCRUAL_CD,'PERIOD END' ,'N','Y')), --ACCRUE_ON_RECEIPT_FLAG
498 PRL.KANBAN_CARD_ID,
499 PRD.TAX_RECOVERY_OVERRIDE_FLAG,
500 DECODE(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, NULL),
501 PRD.AWARD_ID,
502 PRD.OKE_CONTRACT_LINE_ID,
503 PRD.OKE_CONTRACT_DELIVERABLE_ID,
504 L_ORG_ID,
505 PLL.SHIPMENT_TYPE,
506 'N', --PRD.PARTIAL_FUNDED_FLAG,
507 NULL, -- FUNDED VALUE
508 NULL, -- QUANTITY FUNDED
509 Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) AMOUNT_FUNDED,
510 NULL, --PDI.GROUP_LINE_ID,
511 PRD.CLM_MISC_LOA,
512 PRD.CLM_DEFENCE_FUNDING,
513 PRD.CLM_FMS_CASE_NUMBER,
514 PRD.CLM_AGENCY_ACCT_IDENTIFIER,
515 PRD.PAR_DRAFT_ID, -- FOR LINKING PAR
516 PRD.PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
517 FROM
518 TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL,
519 PO_REQUISITION_LINES_ALL PRL,
520 PO_REQ_DISTRIBUTIONS_ALL PRD,
521
522 PO_LINE_LOCATIONS_MERGE_V PLL,
523 PO_LINES_MERGE_V POL ,
524 PO_HEADERS_DRAFT_ALL POH,
525
526 (
527 SELECT
528 POLL.LINE_LOCATION_ID ,
529 POLL.DRAFT_ID,
530 (
531 (DECODE(POLL.MATCHING_BASIS
532 , 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
533 , ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
534 )
535 -
536 Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
537 , 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
538 , ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
539 )
540 )
541
542 FROM PO_DISTRIBUTIONS_MERGE_V POD
543 WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
544 AND POLL.DRAFT_ID = POD.DRAFT_ID
545 AND Nvl(POD.DELETE_FLAG, 'N') = 'N'),0)
546 )DAR
547 FROM PO_LINE_LOCATIONS_MERGE_V POLL
548 WHERE EXISTS
549 (SELECT 1 FROM PO_SESSION_GT PGT
550 WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
551 AND PGT.INDEX_NUM2 = POLL.DRAFT_ID
552 AND PGT.INDEX_NUM1 = POLL.LINE_LOCATION_ID)
553
554 ) DAR_REF
555
556
557 WHERE PRD.DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID AND
558 PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
559 EXISTS (SELECT 1 FROM PO_SESSION_GT PGT
560 WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
561 AND PGT.INDEX_NUM2 = PLL.DRAFT_ID
562 AND PGT.INDEX_NUM1 = PLL.LINE_LOCATION_ID)
563 AND PLL.DRAFT_ID = POL.DRAFT_ID
564 AND PLL.PO_LINE_ID = POL.PO_LINE_ID
565 AND POL.DRAFT_ID = POH.DRAFT_ID
566 AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
567
568 AND DAR_REF.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
569 AND DAR_REF.DRAFT_ID = PLL.DRAFT_ID ;
570
571 D_POSITION := 200;
572 DEBUG(D_MODULE, D_POSITION, 'FOR MOD, INSERTED INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
573
574 ELSE
575 -- For Award
576 INSERT INTO PO_DISTRIBUTIONS_GT --<SHARED PROC FPJ>
577 (PO_DISTRIBUTION_ID,
578 DRAFT_ID,
579 --WHO COLUMNS
580 ---------------------------------------------------------------
581 LAST_UPDATE_DATE,
582 LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN,
584 CREATION_DATE,
585 CREATED_BY,
586 REQUEST_ID,
587 PROGRAM_APPLICATION_ID,
588 PROGRAM_ID,
589 PROGRAM_UPDATE_DATE,
590 ---------------------------------------------------------------
591 PO_HEADER_ID,
592 PO_LINE_ID,
593 LINE_LOCATION_ID,
594 PO_RELEASE_ID,
595 REQ_DISTRIBUTION_ID,
596 SET_OF_BOOKS_ID,
597 CODE_COMBINATION_ID,
598 DELIVER_TO_LOCATION_ID,
599 DELIVER_TO_PERSON_ID,
600 QUANTITY_ORDERED,
601 QUANTITY_DELIVERED,
602 QUANTITY_BILLED,
603 QUANTITY_CANCELLED,
604 AMOUNT_ORDERED,
605 AMOUNT_DELIVERED,
606 AMOUNT_CANCELLED,
607 AMOUNT_BILLED,
608 RATE_DATE,
609 RATE,
610 ACCRUED_FLAG,
611 ENCUMBERED_FLAG,
612 GL_ENCUMBERED_PERIOD_NAME,
613 GL_ENCUMBERED_DATE,
614 DISTRIBUTION_NUM,
615 DESTINATION_TYPE_CODE,
616 DESTINATION_ORGANIZATION_ID,
617 DESTINATION_SUBINVENTORY,
618 BUDGET_ACCOUNT_ID,
619 ACCRUAL_ACCOUNT_ID,
620 VARIANCE_ACCOUNT_ID,
621 DEST_CHARGE_ACCOUNT_ID,
622 DEST_VARIANCE_ACCOUNT_ID,
623 WIP_ENTITY_ID,
624 WIP_LINE_ID,
625 WIP_REPETITIVE_SCHEDULE_ID,
626 WIP_OPERATION_SEQ_NUM,
627 WIP_RESOURCE_SEQ_NUM,
628 BOM_RESOURCE_ID,
629 PREVENT_ENCUMBRANCE_FLAG,
630 PROJECT_ID,
631 TASK_ID,
632 END_ITEM_UNIT_NUMBER,
633 EXPENDITURE_TYPE,
634 PROJECT_ACCOUNTING_CONTEXT,
635 DESTINATION_CONTEXT,
636 EXPENDITURE_ORGANIZATION_ID,
637 EXPENDITURE_ITEM_DATE,
638 ACCRUE_ON_RECEIPT_FLAG,
639 KANBAN_CARD_ID,
640 TAX_RECOVERY_OVERRIDE_FLAG,
641 RECOVERY_RATE,
642 AWARD_ID,
643 OKE_CONTRACT_LINE_ID,
644 OKE_CONTRACT_DELIVERABLE_ID,
645 ORG_ID,
646 DISTRIBUTION_TYPE,
647 --PARTIAL FUNDING ATTRIBUTES
648 PARTIAL_FUNDED_FLAG,
649 FUNDED_VALUE,
650 QUANTITY_FUNDED,
651 AMOUNT_FUNDED ,
652 GROUP_LINE_ID,
653 CLM_MISC_LOA,
654 CLM_DEFENCE_FUNDING,
655 CLM_FMS_CASE_NUMBER,
656 CLM_AGENCY_ACCT_IDENTIFIER
657 )
658
659 SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
660 NVL(POH.DRAFT_ID,-1),
661 --DEFAULT WHO COLUMNS
662 ---------------------------------------------------------------
663 SYSDATE,
664 FND_GLOBAL.USER_ID,
665 FND_GLOBAL.LOGIN_ID,
666 SYSDATE,
667 FND_GLOBAL.USER_ID,
668 FND_GLOBAL.CONC_REQUEST_ID,
669 FND_GLOBAL.PROG_APPL_ID,
670 FND_GLOBAL.CONC_PROGRAM_ID,
671 SYSDATE,
672 ---------------------------------------------------------------
673
674 PLL.PO_HEADER_ID,
675 POL.PO_LINE_ID,
676 PLL.LINE_LOCATION_ID,
677 PLL.PO_RELEASE_ID,
678 PRD.DISTRIBUTION_ID,
679 NVL(L_SOB, PRD.SET_OF_BOOKS_ID),
680
681 PRD.CODE_COMBINATION_ID,
682 PRL.DELIVER_TO_LOCATION_ID,
683 DECODE(PLL.DROP_SHIP_FLAG,'Y',NULL,PRL.TO_PERSON_ID),
684 NULL, -- QUANTITY ORDERED
685 0,
686 0,
687 0,
688
689 Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) DIST_AMT,
690 0, -- AMOUNT_DELIVERED
691 0, -- AMOUNT_CANCELLED
692 0, -- AMOUNT_BILLED
693
694 POH.RATE_DATE,
695 POH.RATE,
696 DECODE( (NVL(PRL.PCARD_FLAG,'N')) ,'N','N','Y'), --X_ACCRUED_FLAG,
697 'N',--ENCUMBERED_FLAG
698 -- if the profile option po_autocreate_date is set to req gl date
699 -- use req's gl date to build encum gl date, otherwise use sysdate.
700 GET_GL_PERIOD_NAME,
701 GET_GL_DATE(
702 DECODE( FND_PROFILE.VALUE('PO_AUTOCREATE_DATE')
703 , 'REQ GL DATE', PRD.GL_ENCUMBERED_DATE
704 , TRUNC(SYSDATE) ), L_SOB),
705 --L_PERIOD_NAME,
706 -1, --PDI.DISTRIBUTION_NUM,
707 PRL.DESTINATION_TYPE_CODE,
708 PRL.DESTINATION_ORGANIZATION_ID,
709 PRL.DESTINATION_SUBINVENTORY,
710 PRD.BUDGET_ACCOUNT_ID,
711 PRD.ACCRUAL_ACCOUNT_ID,
712 PRD.VARIANCE_ACCOUNT_ID,
713
714 NULL, --PDI.DEST_CHARGE_ACCOUNT_ID,
715 NULL, --PDI.DEST_VARIANCE_ACCOUNT_ID,
716
717 PRL.WIP_ENTITY_ID,
718 PRL.WIP_LINE_ID,
719 PRL.WIP_REPETITIVE_SCHEDULE_ID,
720 PRL.WIP_OPERATION_SEQ_NUM,
721 PRL.WIP_RESOURCE_SEQ_NUM,
722 PRL.BOM_RESOURCE_ID,
723 'N', --PREVENT_ENCUMBRANCE_FLAG
724 PRD.PROJECT_ID,
725 PRD.TASK_ID,
726 PRD.END_ITEM_UNIT_NUMBER,
727 PRD.EXPENDITURE_TYPE,
728 PRD.PROJECT_ACCOUNTING_CONTEXT,
729 PRL.DESTINATION_CONTEXT,
730 PRD.EXPENDITURE_ORGANIZATION_ID,
731 PRD.EXPENDITURE_ITEM_DATE,
732 DECODE(RECEIPT_REQUIRED_FLAG, 'N','N',DECODE(L_EXPENSE_ACCRUAL_CD,'PERIOD END' ,'N','Y')), --ACCRUE_ON_RECEIPT_FLAG
733 PRL.KANBAN_CARD_ID,
734 PRD.TAX_RECOVERY_OVERRIDE_FLAG,
735 DECODE(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, NULL),
736 PRD.AWARD_ID,
737 PRD.OKE_CONTRACT_LINE_ID,
738 PRD.OKE_CONTRACT_DELIVERABLE_ID,
739 L_ORG_ID,
740 PLL.SHIPMENT_TYPE,
741 'N', --PRD.PARTIAL_FUNDED_FLAG,
742 NULL, -- FUNDED VALUE
743 NULL, -- QUANTITY FUNDED
744 Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) AMOUNT_FUNDED,
745 NULL, --PDI.GROUP_LINE_ID,
746 PRD.CLM_MISC_LOA,
747 PRD.CLM_DEFENCE_FUNDING,
748 PRD.CLM_FMS_CASE_NUMBER,
749 PRD.CLM_AGENCY_ACCT_IDENTIFIER
750 FROM
751 TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL,
752 PO_REQUISITION_LINES_ALL PRL,
753 PO_REQ_DISTRIBUTIONS_ALL PRD,
754
755 PO_LINE_LOCATIONS_ALL PLL,
756 PO_LINES_ALL POL ,
757 PO_HEADERS_ALL POH,
758
759 (
760 SELECT
761 POLL.LINE_LOCATION_ID ,
762 POLL.DRAFT_ID,
763 (
764 (DECODE(POLL.MATCHING_BASIS
765 , 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
766 , ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
767 )
768 -
769 Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
770 , 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
771 , ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
772 )
773 )
774
775 FROM PO_DISTRIBUTIONS_ALL POD
776 WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID),0)
777 )DAR
778 FROM PO_LINE_LOCATIONS_ALL POLL
779 WHERE EXISTS
780 (SELECT 1 FROM PO_SESSION_GT PGT
781 WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
782 AND PGT.INDEX_NUM1 = POLL.LINE_LOCATION_ID)
783
784 ) DAR_REF
785
786
787 WHERE PRD.DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID
788 AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
789 AND EXISTS (SELECT 1 FROM PO_SESSION_GT PGT
790 WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
791 AND PGT.INDEX_NUM1 = PLL.LINE_LOCATION_ID)
792 AND PLL.PO_LINE_ID = POL.PO_LINE_ID
793 AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
794 AND DAR_REF.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID;
795
796 D_POSITION := 210;
797 DEBUG(D_MODULE, D_POSITION, 'FOR BASE DOC, INSERTED INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
798
799
800 END IF;
801
802 D_POSITION := 220;
803 DEBUG(D_MODULE, D_POSITION, 'Calibrate distribution amount for schedule');
804 -- Calibrate the last distributions amount for the total schdule amount.
805 -- Last New Dist Amt = Total Amt for Schedule - Sum of rest of new dists
806 IF (L_DRAFT_ID <> -1 ) THEN
807
808 FOR CALIBRATE_SCH_CUR IN
809 (
810 SELECT POLL.LINE_LOCATION_ID , Sum(AMOUNT_ORDERED) TOTAL_AMT, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID,
811 Max(
812 (DECODE(POLL.MATCHING_BASIS
813 , 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
814 , ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
815 )
816 -
817 Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
818 , 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
819 , ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
820 )
821 )
822
823 FROM PO_DISTRIBUTIONS_MERGE_V POD
824 WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
825 AND POLL.DRAFT_ID = POD.DRAFT_ID
826 AND Nvl(POD.DELETE_FLAG, 'N') = 'N'),0)
827 )DAR
828 FROM PO_LINE_LOCATIONS_MERGE_V POLL, PO_DISTRIBUTIONS_GT GT
829 WHERE GT.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
830 AND GT.DRAFT_ID = POLL.DRAFT_ID
831 GROUP BY POLL.LINE_LOCATION_ID
832
833 ) LOOP
834 UPDATE PO_DISTRIBUTIONS_GT T
835 SET T.AMOUNT_ORDERED = Round((L_DERIVED_TFU * CALIBRATE_SCH_CUR.DAR / P_TOTAL_DIST_AMT_REQUIRED),X_PRECISION) - (CALIBRATE_SCH_CUR.TOTAL_AMT - T.AMOUNT_ORDERED)
836 WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_SCH_CUR.DISTRIBUTION_ID
837 AND T.LINE_LOCATION_ID = CALIBRATE_SCH_CUR.LINE_LOCATION_ID ;
838
839 D_POSITION := 221;
840 DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
841 DEBUG(D_MODULE, D_POSITION, ' L_DERIVED_TFU:'||L_DERIVED_TFU||' CALIBRATE_SCH_CUR.DAR: '|| CALIBRATE_SCH_CUR.DAR||' CALIBRATE_SCH_CUR.TOTAL_AMT:'||CALIBRATE_SCH_CUR.TOTAL_AMT) ;
842
843
844
845 END LOOP;
846
847 ELSE
848
849 FOR CALIBRATE_SCH_CUR IN
850 (
851 SELECT POLL.LINE_LOCATION_ID , Sum(AMOUNT_ORDERED) TOTAL_AMT, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID,
852 Max(
853 (DECODE(POLL.MATCHING_BASIS
854 , 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
855 , ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
856 )
857 -
858 Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
859 , 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
860 , ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
861 )
862 )
863
864 FROM PO_DISTRIBUTIONS_ALL POD
865 WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID),0)
866 ) DAR
867 FROM PO_LINE_LOCATIONS_ALL POLL, PO_DISTRIBUTIONS_GT GT
868 WHERE GT.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
869 GROUP BY POLL.LINE_LOCATION_ID
870
871 ) LOOP
872
873 UPDATE PO_DISTRIBUTIONS_GT T
874 SET T.AMOUNT_ORDERED = Round((L_DERIVED_TFU * CALIBRATE_SCH_CUR.DAR / P_TOTAL_DIST_AMT_REQUIRED),X_PRECISION) - (CALIBRATE_SCH_CUR.TOTAL_AMT - T.AMOUNT_ORDERED)
875 WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_SCH_CUR.DISTRIBUTION_ID
876 AND T.LINE_LOCATION_ID = CALIBRATE_SCH_CUR.LINE_LOCATION_ID ;
877
878 D_POSITION := 221;
879 DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
880 DEBUG(D_MODULE, D_POSITION, ' L_DERIVED_TFU:'||L_DERIVED_TFU||' CALIBRATE_SCH_CUR.DAR: '|| CALIBRATE_SCH_CUR.DAR||' CALIBRATE_SCH_CUR.TOTAL_AMT:'||CALIBRATE_SCH_CUR.TOTAL_AMT) ;
881
882
883
884 END LOOP;
885
886 END IF;
887
888 D_POSITION := 222;
889 DEBUG(D_MODULE, D_POSITION, 'Calibrate distribution amount for requisition distribution');
890
891 -- Calibrate the last distributions amount for the funds to use of requisition dist.
892 -- Last New Dist Amt = Funds to use per req dist - Sum of rest of new dists
893 FOR CALIBRATE_REQDIST_CUR IN
894 (
895 SELECT REQ_DISTRIBUTION_ID, Sum(AMOUNT_ORDERED) TOTAL_REQ_AMT, Max(P_TBL.FUNDS_TO_USE) FUNDS_TO_USE, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID
896 FROM PO_DISTRIBUTIONS_GT GT,TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL
897 WHERE GT.REQ_DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID
898 GROUP BY REQ_DISTRIBUTION_ID
899 ) LOOP
900 UPDATE PO_DISTRIBUTIONS_GT T
901 SET T.AMOUNT_ORDERED = CALIBRATE_REQDIST_CUR.FUNDS_TO_USE - (CALIBRATE_REQDIST_CUR.TOTAL_REQ_AMT - T.AMOUNT_ORDERED)
902 WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_REQDIST_CUR.DISTRIBUTION_ID
903 AND T.REQ_DISTRIBUTION_ID = CALIBRATE_REQDIST_CUR.REQ_DISTRIBUTION_ID ;
904
905 D_POSITION := 222;
906 DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
907 DEBUG(D_MODULE, D_POSITION, ' CALIBRATE_REQDIST_CUR.TOTAL_REQ_AMT: '|| CALIBRATE_REQDIST_CUR.TOTAL_REQ_AMT||' CALIBRATE_REQDIST_CUR.FUNDS_TO_USE:'||CALIBRATE_REQDIST_CUR.FUNDS_TO_USE) ;
908
909
910 END LOOP;
911
912
913
914 D_POSITION := 225;
915 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE NEW GT TABLE FOR AMOUNT ORDERED AND QUANTITY ORDERED');
916 -- Update the Amount_ordered, Quantity_ordered based on Line type.
917 UPDATE PO_DISTRIBUTIONS_GT GT
918 SET QUANTITY_ORDERED = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', NULL, Trunc((NVL(GT.AMOUNT_ORDERED,0) / POL.PRICE_OVERRIDE),Nvl(FND_PROFILE.VALUE('PO_QUANTITY_PRECISION'),5)) )
919 FROM PO_LINE_LOCATIONS_MERGE_V POL
920 WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
921 AND GT.DRAFT_ID = POL.DRAFT_ID),
922 AMOUNT_ORDERED = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', AMOUNT_ORDERED, NULL)
923 FROM PO_LINE_LOCATIONS_MERGE_V POL
924 WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
925 AND GT.DRAFT_ID = POL.DRAFT_ID);
926
927 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE NEW GT TABLE FOR FUNDED VALUE');
928
929 -- Calculate Funded Value(Func curr) based on rate and apportioned amount.
930 UPDATE PO_DISTRIBUTIONS_GT GT
931 SET FUNDED_VALUE = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', Round(GT.AMOUNT_ORDERED * NVL(L_RATE,1), X_BASE_PRECISION), Round((QUANTITY_ORDERED * POL.PRICE_OVERRIDE * NVL(L_RATE,1)),X_BASE_PRECISION) )
932 FROM PO_LINE_LOCATIONS_MERGE_V POL
933 WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
934 AND GT.DRAFT_ID = POL.DRAFT_ID);
935
936
937 D_POSITION := 230;
938 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE NEW GT TABLE FOR DISTRIBUTION NUMBER AND QUANTITY FUNDED');
939
940 FOR DIST_NUM_C IN C_DIST_NUM_MAX_LIST
941 LOOP
942 --UPDATE PO_DISTRIBUTIONS INTERFACE
943 UPDATE PO_DISTRIBUTIONS_GT GT
944 SET DISTRIBUTION_NUM = DIST_NUM_C.MAX_DIST_NUM + ROWNUM,
945 QUANTITY_FUNDED = QUANTITY_ORDERED,
946 AMOUNT_FUNDED = AMOUNT_ORDERED
947 WHERE GT.LINE_LOCATION_ID = DIST_NUM_C.LINE_LOCATION_ID;
948
949 END LOOP;
950
951 D_POSITION := 240;
952 IF (PO_LOG.D_PROC) THEN
953 PO_LOG.PROC_END(D_MODULE);
954 END IF;
955
956
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959
960 DEBUG(D_MODULE, D_POSITION, 'DEFAULTING DISTRIBUTION FAILED WITH NO DATA FOUND ERROR');
961 RAISE;
962 WHEN TOO_MANY_ROWS THEN
963
964 DEBUG(D_MODULE, D_POSITION, 'DEFAULTING DISTRIBUTION FAILED WITH TOO MANY ROWS ERROR');
965 RAISE;
966 WHEN OTHERS THEN
967
968 DEBUG(D_MODULE, D_POSITION, 'ERROR : ' || SUBSTRB(SQLERRM,1,200));
969 RAISE;
970
971 END CREATE_LINK_REQ_DIST_GT;
972
973
974
975
976
977 --START OF COMMENTS
978 --NAME: VALIDATE_LINKED_DISTRIBUTIONS
979 --PRE-REQS:
980 -- NONE.
981 --MODIFIES:
982 -- NONE.
983 --LOCKS:
984 -- NONE.
985 --FUNCTION:
986 -- IT VALIDATES THE NEW DISTRIBUTIONS FOR CALCULATED VALUES, QUANTITY, DISTRIBUTION AMOUNT, FUNDED VALUE,
987 -- WITH THE SPECIFIC SOURCE REQUISITION IT WAS CREATED.
988 --PARAMETERS:
989 --IN:
990 --X_RESULTS
991 -- HOLDS THE VALIDATION MESSAGES
992 --TESTING:
993 -- NONE.
994 --END OF COMMENTS
995
996 PROCEDURE VALIDATE_LINKED_DISTRIBUTIONS
997 (
998 X_RESULTS IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
999 )
1000
1001
1002
1003
1004 IS
1005
1006 D_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_LINKED_DISTRIBUTIONS';
1007 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
1008 D_POSITION NUMBER;
1009
1010 L_LINE_NUM VARCHAR2(30);
1011 L_SHIPMENT_NUM VARCHAR2(30);
1012 L_DISTRIBUTION_NUM VARCHAR2(30);
1013 L_QUANTITY_ORDERED NUMBER;
1014 L_AMOUNT_ORDERED NUMBER;
1015 L_MATCHING_BASIS VARCHAR2(30);
1016
1017 L_DRAFT_ID NUMBER;
1018 L_HEADER_ID NUMBER;
1019
1020 L_CURRENCY_CODE VARCHAR2(5);
1021 L_FUNC_CURRENCY_CODE VARCHAR2(5);
1022 L_RATE NUMBER;
1023
1024 CURSOR FUNDED_VALUE_VALIDATION_CUR
1025 IS
1026 SELECT REQ_DISTRIBUTION_ID,
1027 SUM(NEW_DIST_GT.AMOUNT_ORDERED) TOTAL_AMOUNT_ORDERED,
1028 SUM(NEW_DIST_GT.FUNDED_VALUE) TOTAL_FUNDED_VALUE,
1029 MIN(REQ_DIST_GT.NUM1) FUNDS_TO_USE,
1030 MIN(PO_REQS_INQ_SV.GET_REQ_AMOUNT ('J' , REQ_DISTRIBUTION_ID)) REQ_DIST_AMOUNT,
1031 Min(DV.FUNDS_REMAINING) FUNDS_REMAINING
1032 FROM PO_SESSION_GT REQ_DIST_GT,
1033 PO_DISTRIBUTIONS_GT NEW_DIST_GT,
1034 PO_CLMREQ_DIST_DETAILS_V DV
1035 WHERE REQ_DIST_GT.INDEX_NUM1 = NEW_DIST_GT.REQ_DISTRIBUTION_ID
1036 AND REQ_DIST_GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID'
1037 AND DV.distribution_id = NEW_DIST_GT.REQ_DISTRIBUTION_ID
1038 GROUP BY NEW_DIST_GT.REQ_DISTRIBUTION_ID;
1039
1040
1041 GT_ZERO_VALIDATION_REFCUR SYS_REFCURSOR;
1042 UNIQUE_DIST_NUM_REFCUR SYS_REFCURSOR;
1043
1044
1045 X_BASE_PRECISION NUMBER := NULL;
1046 X_BASE_EXT_PRECISION NUMBER := NULL;
1047 X_BASE_MIN_ACCT_UNIT NUMBER := NULL;
1048
1049 L_AT_SOURCING_REQS NUMBER;
1050
1051
1052 BEGIN
1053
1054 D_POSITION := 0;
1055 IF (PO_LOG.D_PROC) THEN
1056 PO_LOG.PROC_BEGIN(D_MODULE,'1',1);
1057 END IF;
1058
1059 IF (X_RESULTS IS NULL) THEN
1060 X_RESULTS := PO_MULTI_MOD_VAL_RESULTS_TYPE.NEW_INSTANCE();
1061 END IF;
1062
1063
1064 SELECT COUNT(DISTINCT (PRL.REQUISITION_LINE_ID))
1065 INTO L_AT_SOURCING_REQS
1066 FROM PO_DISTRIBUTIONS_GT GT,
1067 PO_REQUISITION_LINES_ALL PRL,
1068 PO_REQ_DISTRIBUTIONS_ALL PRD
1069 WHERE GT.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID
1070 AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
1071 AND NVL(PRL.AT_SOURCING_FLAG,'N') = 'Y';
1072
1073 D_POSITION := 5;
1074 DEBUG(D_MODULE, D_POSITION, 'L_AT_SOURCING_REQS: '||L_AT_SOURCING_REQS);
1075
1076 IF L_AT_SOURCING_REQS > 0 THEN
1077
1078 X_RESULTS.ADD_RESULT(
1079 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1080 , P_MULTI_MOD_REQUEST_ID => 1
1081 , P_RESULT_TYPE => 'ERROR'
1082 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1083 , P_MESSAGE_APPLICATION => 'PO'
1084 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_REQ_AT_SOURCING
1085 );
1086
1087
1088 END IF;
1089
1090 SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
1091 FROM PO_SESSION_GT
1092 WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
1093
1094 D_POSITION := 10;
1095 DEBUG(D_MODULE, D_POSITION, 'CALLING VALIDATE_LINKED_DISTRIBUTIONS');
1096
1097
1098 IF (L_DRAFT_ID <> -1 ) THEN
1099
1100 D_POSITION := 50;
1101 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE CURRENCY CODES, SOB, ORG ID ,RATE');
1102 --DBMS_OUTPUT.PUT_LINE('4');
1103
1104
1105 SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE
1106 INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE
1107 FROM PO_HEADERS_DRAFT_ALL PH,
1108 GL_SETS_OF_BOOKS GLSOB,
1109 ORG_ORGANIZATION_DEFINITIONS OOD
1110 WHERE PH.PO_HEADER_ID = L_HEADER_ID
1111 AND PH.DRAFT_ID = L_DRAFT_ID
1112 AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
1113 AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
1114
1115 FND_CURRENCY.GET_INFO (L_FUNC_CURRENCY_CODE,
1116 X_BASE_PRECISION,
1117 X_BASE_EXT_PRECISION,
1118 X_BASE_MIN_ACCT_UNIT);
1119
1120 D_POSITION := 55;
1121 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE SESSION GT. X_BASE_PRECISION: '||X_BASE_PRECISION||',X_BASE_EXT_PRECISION: '
1122 ||X_BASE_EXT_PRECISION||',X_BASE_MIN_ACCT_UNIT: '||X_BASE_MIN_ACCT_UNIT);
1123
1124
1125 FOR FUND_VAL_GT_DA_CUR IN
1126 (
1127 SELECT GT.FUNDED_VALUE,
1128 DECODE(POL.MATCHING_BASIS, 'AMOUNT', (GT.AMOUNT_ORDERED * NVL(L_RATE,1)), Round(GT.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE * NVL(L_RATE,1),X_BASE_PRECISION)) DIST_AMT,
1129 Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1130 PLL.SHIPMENT_NUM,
1131 GT.DISTRIBUTION_NUM
1132 FROM PO_DISTRIBUTIONS_GT GT,
1133 PO_LINE_LOCATIONS_MERGE_V PLL,
1134 PO_LINES_MERGE_V POL
1135 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1136 GT.DRAFT_ID = PLL.DRAFT_ID AND
1137 POL.PO_LINE_ID = PLL.PO_LINE_ID AND
1138 PLL.DRAFT_ID = POL.DRAFT_ID
1139
1140 ) LOOP
1141
1142 D_POSITION := 51;
1143 DEBUG(D_MODULE, D_POSITION, 'FUNDED_VALUE :'||FUND_VAL_GT_DA_CUR.FUNDED_VALUE||',DIST_AMT :'||FUND_VAL_GT_DA_CUR.DIST_AMT);
1144
1145 -- Validate for the funded value of new distributions should not be greater than the distribution amount.
1146 IF FUND_VAL_GT_DA_CUR.FUNDED_VALUE > FUND_VAL_GT_DA_CUR.DIST_AMT THEN
1147
1148 X_RESULTS.ADD_RESULT(
1149 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1150 , P_MULTI_MOD_REQUEST_ID => 1
1151 , P_RESULT_TYPE => 'ERROR'
1152 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1153 , P_MESSAGE_APPLICATION => 'PO'
1154 , P_MESSAGE_NAME => 'PO_LINKREQ_FV_GRT_DA_ERR'
1155 , P_TOKEN1_NAME => 'LINE_NUM'
1156 , P_TOKEN1_VALUE => FUND_VAL_GT_DA_CUR.LINE_NUM
1157 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1158 , P_TOKEN2_VALUE => FUND_VAL_GT_DA_CUR.SHIPMENT_NUM
1159 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1160 , P_TOKEN3_VALUE => FUND_VAL_GT_DA_CUR.DISTRIBUTION_NUM
1161
1162 );
1163
1164 D_POSITION := 52;
1165 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1166
1167 END IF;
1168
1169 END LOOP;
1170
1171 ELSE
1172
1173 D_POSITION := 60;
1174 DEBUG(D_MODULE, D_POSITION, 'FETCHING THE CURRENCY CODES, SOB, ORG ID ,RATE');
1175
1176 SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE
1177 INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE
1178 FROM PO_HEADERS_ALL PH,
1179 GL_SETS_OF_BOOKS GLSOB,
1180 ORG_ORGANIZATION_DEFINITIONS OOD
1181 WHERE PH.PO_HEADER_ID = L_HEADER_ID
1182 AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
1183 AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
1184
1185 FND_CURRENCY.GET_INFO (L_FUNC_CURRENCY_CODE,
1186 X_BASE_PRECISION,
1187 X_BASE_EXT_PRECISION,
1188 X_BASE_MIN_ACCT_UNIT);
1189
1190 D_POSITION := 65;
1191 DEBUG(D_MODULE, D_POSITION, 'UPDATING THE SESSION GT. X_BASE_PRECISION: '||X_BASE_PRECISION||',X_BASE_EXT_PRECISION: '
1192 ||X_BASE_EXT_PRECISION||',X_BASE_MIN_ACCT_UNIT: '||X_BASE_MIN_ACCT_UNIT);
1193
1194
1195 FOR FUND_VAL_GT_DA_CUR IN
1196 (
1197 SELECT GT.FUNDED_VALUE,
1198 DECODE(POL.MATCHING_BASIS, 'AMOUNT', (GT.AMOUNT_ORDERED * NVL(L_RATE,1)), Round(GT.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE * NVL(L_RATE,1),X_BASE_PRECISION)) DIST_AMT,
1199 Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1200 PLL.SHIPMENT_NUM,
1201 GT.DISTRIBUTION_NUM
1202 FROM PO_DISTRIBUTIONS_GT GT,
1203 PO_LINE_LOCATIONS_ALL PLL,
1204 PO_LINES_ALL POL
1205 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1206 POL.PO_LINE_ID = PLL.PO_LINE_ID
1207
1208 ) LOOP
1209
1210 D_POSITION := 51;
1211 DEBUG(D_MODULE, D_POSITION, 'FUNDED_VALUE :'||FUND_VAL_GT_DA_CUR.FUNDED_VALUE||',DIST_AMT :'||FUND_VAL_GT_DA_CUR.DIST_AMT);
1212
1213 -- Validate for the funded value of new distributions should not be greater than the distribution amount.
1214 IF FUND_VAL_GT_DA_CUR.FUNDED_VALUE > FUND_VAL_GT_DA_CUR.DIST_AMT THEN
1215
1216 X_RESULTS.ADD_RESULT(
1217 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1218 , P_MULTI_MOD_REQUEST_ID => 1
1219 , P_RESULT_TYPE => 'ERROR'
1220 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1221 , P_MESSAGE_APPLICATION => 'PO'
1222 , P_MESSAGE_NAME => 'PO_LINKREQ_FV_GRT_DA_ERR'
1223 , P_TOKEN1_NAME => 'LINE_NUM'
1224 , P_TOKEN1_VALUE => FUND_VAL_GT_DA_CUR.LINE_NUM
1225 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1226 , P_TOKEN2_VALUE => FUND_VAL_GT_DA_CUR.SHIPMENT_NUM
1227 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1228 , P_TOKEN3_VALUE => FUND_VAL_GT_DA_CUR.DISTRIBUTION_NUM
1229
1230 );
1231
1232 D_POSITION := 52;
1233 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1234
1235 END IF;
1236
1237 END LOOP;
1238
1239
1240 END IF;
1241
1242
1243 FOR FUNDED_VALUES_C IN FUNDED_VALUE_VALIDATION_CUR
1244 LOOP
1245
1246 D_POSITION := 20;
1247 DEBUG(D_MODULE, D_POSITION, 'TOTAL_FUNDED_VALUE :'||FUNDED_VALUES_C.TOTAL_FUNDED_VALUE||',FUNDS_TO_USE :'||
1248 FUNDED_VALUES_C.FUNDS_TO_USE||',TOTAL_AMOUNT_ORDERED :'||FUNDED_VALUES_C.TOTAL_AMOUNT_ORDERED||',REQ_DIST_AMOUNT :'
1249 ||FUNDED_VALUES_C.REQ_DIST_AMOUNT||' FUNDS_REMAINING :'||FUNDED_VALUES_C.FUNDS_REMAINING);
1250
1251 -- Validate for the Total funded value of new distributions should not be greater than the Funds to use for that requistion.
1252 IF FUNDED_VALUES_C.TOTAL_FUNDED_VALUE > FUNDED_VALUES_C.FUNDS_TO_USE THEN
1253
1254 X_RESULTS.ADD_RESULT(
1255 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1256 , P_MULTI_MOD_REQUEST_ID => 1
1257 , P_RESULT_TYPE => 'ERROR'
1258 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1259 , P_MESSAGE_APPLICATION => 'PO'
1260 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_FUNDED_VAL_MISMATCH
1261 );
1262
1263 END IF;
1264
1265 D_POSITION := 30;
1266 DEBUG(D_MODULE, D_POSITION, 'TOTAL FUNDED VALUE SUCCESFULLY VALIDATED');
1267
1268 -- Validate for the Total funded value of new distributions should not be greater than the Funds to use for that requistion.
1269 IF FUNDED_VALUES_C.TOTAL_FUNDED_VALUE > FUNDED_VALUES_C.FUNDS_REMAINING THEN
1270
1271 X_RESULTS.ADD_RESULT(
1272 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1273 , P_MULTI_MOD_REQUEST_ID => 1
1274 , P_RESULT_TYPE => 'ERROR'
1275 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1276 , P_MESSAGE_APPLICATION => 'PO'
1277 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_FUNDS_CONSUMED
1278 );
1279
1280 END IF;
1281
1282 END LOOP;
1283
1284 D_POSITION := 50;
1285 DEBUG(D_MODULE, D_POSITION, 'VALIDATING FOR UNIQUE DISTRIBUTION NUMBER');
1286
1287
1288 -- Checks for duplicate distribution number in new distributions by comparing the existing distributions.
1289 IF (L_DRAFT_ID <> -1 ) THEN
1290
1291 OPEN UNIQUE_DIST_NUM_REFCUR FOR
1292 SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1293 PLL.SHIPMENT_NUM,
1294 PLD.DISTRIBUTION_NUM
1295 FROM
1296 PO_DISTRIBUTIONS_GT GT,
1297 PO_LINES_MERGE_V POL,
1298 PO_LINE_LOCATIONS_MERGE_V PLL,
1299 PO_DISTRIBUTIONS_MERGE_V PLD
1300 WHERE GT.LINE_LOCATION_ID = PLD.LINE_LOCATION_ID AND
1301 GT.DRAFT_ID = PLD.DRAFT_ID AND
1302 PLD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1303 PLL.DRAFT_ID = PLD.DRAFT_ID AND
1304 PLL.PO_LINE_ID = POL.PO_LINE_ID AND
1305 PLL.DRAFT_ID = POL.DRAFT_ID AND
1306 PLD.DISTRIBUTION_NUM = GT.DISTRIBUTION_NUM;
1307
1308 IF (UNIQUE_DIST_NUM_REFCUR%NOTFOUND) THEN
1309 D_POSITION := 70;
1310 DEBUG(D_MODULE, D_POSITION, 'VALIDATION SUCCESS');
1311
1312 ELSE
1313 LOOP
1314 FETCH UNIQUE_DIST_NUM_REFCUR INTO L_LINE_NUM, L_SHIPMENT_NUM, L_DISTRIBUTION_NUM ;
1315 EXIT WHEN UNIQUE_DIST_NUM_REFCUR%NOTFOUND;
1316
1317 X_RESULTS.ADD_RESULT(
1318 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1319 , P_MULTI_MOD_REQUEST_ID => 1
1320 , P_RESULT_TYPE => 'ERROR'
1321 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1322 , P_MESSAGE_APPLICATION => 'PO'
1323 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_UNIQUE_DIST_NUM
1324 , P_TOKEN1_NAME => 'LINE_NUM'
1325 , P_TOKEN1_VALUE => L_LINE_NUM
1326 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1327 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1328 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1329 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1330
1331 );
1332
1333 D_POSITION := 60;
1334 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1335
1336 END LOOP;
1337
1338 END IF;
1339 CLOSE UNIQUE_DIST_NUM_REFCUR;
1340
1341 ELSE
1342
1343 OPEN UNIQUE_DIST_NUM_REFCUR FOR
1344 SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1345 PLL.SHIPMENT_NUM,
1346 PLD.DISTRIBUTION_NUM
1347 FROM
1348 PO_DISTRIBUTIONS_GT GT,
1349 PO_LINES_ALL POL,
1350 PO_LINE_LOCATIONS_ALL PLL,
1351 PO_DISTRIBUTIONS_ALL PLD
1352 WHERE GT.LINE_LOCATION_ID = PLD.LINE_LOCATION_ID AND
1353 PLD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1354 PLL.PO_LINE_ID = POL.PO_LINE_ID AND
1355 PLD.DISTRIBUTION_NUM = GT.DISTRIBUTION_NUM;
1356
1357 IF (UNIQUE_DIST_NUM_REFCUR%NOTFOUND) THEN
1358 D_POSITION := 70;
1359 DEBUG(D_MODULE, D_POSITION, 'VALIDATION SUCCESS');
1360
1361 ELSE
1362 LOOP
1363 FETCH UNIQUE_DIST_NUM_REFCUR INTO L_LINE_NUM, L_SHIPMENT_NUM, L_DISTRIBUTION_NUM ;
1364 EXIT WHEN UNIQUE_DIST_NUM_REFCUR%NOTFOUND;
1365
1366 X_RESULTS.ADD_RESULT(
1367 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1368 , P_MULTI_MOD_REQUEST_ID => 1
1369 , P_RESULT_TYPE => 'ERROR'
1370 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1371 , P_MESSAGE_APPLICATION => 'PO'
1372 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_UNIQUE_DIST_NUM
1373 , P_TOKEN1_NAME => 'LINE_NUM'
1374 , P_TOKEN1_VALUE => L_LINE_NUM
1375 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1376 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1377 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1378 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1379
1380 );
1381
1382 D_POSITION := 60;
1383 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1384
1385 END LOOP;
1386
1387 END IF;
1388 CLOSE UNIQUE_DIST_NUM_REFCUR;
1389
1390 END IF;
1391
1392 -- Checks for duplicate distribution number within the newly created distributions.
1393 IF (L_DRAFT_ID <> -1 ) THEN
1394
1395 OPEN UNIQUE_DIST_NUM_REFCUR FOR
1396 SELECT Min(Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM)) LINE_NUM,
1397 PLL.SHIPMENT_NUM,
1398 GT.DISTRIBUTION_NUM
1399 FROM
1400 PO_DISTRIBUTIONS_GT GT,
1401 PO_LINES_MERGE_V POL,
1402 PO_LINE_LOCATIONS_MERGE_V PLL
1403 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1404 GT.DRAFT_ID = PLL.DRAFT_ID AND
1405 PLL.PO_LINE_ID = POL.PO_LINE_ID AND
1406 PLL.DRAFT_ID = POL.DRAFT_ID
1407 GROUP BY POL.LINE_NUM, PLL.SHIPMENT_NUM, GT.DISTRIBUTION_NUM
1408 HAVING Count(1) > 1;
1409
1410 IF (UNIQUE_DIST_NUM_REFCUR%NOTFOUND) THEN
1411 D_POSITION := 70;
1412 DEBUG(D_MODULE, D_POSITION, 'VALIDATION SUCCESS');
1413
1414 ELSE
1415 LOOP
1416 FETCH UNIQUE_DIST_NUM_REFCUR INTO L_LINE_NUM, L_SHIPMENT_NUM, L_DISTRIBUTION_NUM ;
1417 EXIT WHEN UNIQUE_DIST_NUM_REFCUR%NOTFOUND;
1418
1419 X_RESULTS.ADD_RESULT(
1420 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1421 , P_MULTI_MOD_REQUEST_ID => 1
1422 , P_RESULT_TYPE => 'ERROR'
1423 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1424 , P_MESSAGE_APPLICATION => 'PO'
1425 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_UNIQUE_DIST_NUM
1426 , P_TOKEN1_NAME => 'LINE_NUM'
1427 , P_TOKEN1_VALUE => L_LINE_NUM
1428 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1429 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1430 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1431 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1432
1433 );
1434
1435 D_POSITION := 60;
1436 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1437
1438 END LOOP;
1439
1440 END IF;
1441 CLOSE UNIQUE_DIST_NUM_REFCUR;
1442
1443 ELSE
1444
1445 OPEN UNIQUE_DIST_NUM_REFCUR FOR
1446 SELECT Min(Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM)) LINE_NUM,
1447 PLL.SHIPMENT_NUM,
1448 GT.DISTRIBUTION_NUM
1449 FROM
1450 PO_DISTRIBUTIONS_GT GT,
1451 PO_LINES_ALL POL,
1452 PO_LINE_LOCATIONS_ALL PLL
1453 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1454 PLL.PO_LINE_ID = POL.PO_LINE_ID
1455 GROUP BY POL.LINE_NUM, PLL.SHIPMENT_NUM, GT.DISTRIBUTION_NUM
1456 HAVING Count(1) > 1;
1457
1458 IF (UNIQUE_DIST_NUM_REFCUR%NOTFOUND) THEN
1459 D_POSITION := 70;
1460 DEBUG(D_MODULE, D_POSITION, 'VALIDATION SUCCESS');
1461
1462 ELSE
1463 LOOP
1464 FETCH UNIQUE_DIST_NUM_REFCUR INTO L_LINE_NUM, L_SHIPMENT_NUM, L_DISTRIBUTION_NUM;
1465 EXIT WHEN UNIQUE_DIST_NUM_REFCUR%NOTFOUND;
1466
1467
1468 X_RESULTS.ADD_RESULT(
1469 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1470 , P_MULTI_MOD_REQUEST_ID => 1
1471 , P_RESULT_TYPE => 'ERROR'
1472 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1473 , P_MESSAGE_APPLICATION => 'PO'
1474 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_UNIQUE_DIST_NUM
1475 , P_TOKEN1_NAME => 'LINE_NUM'
1476 , P_TOKEN1_VALUE => L_LINE_NUM
1477 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1478 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1479 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1480 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1481
1482 );
1483 D_POSITION := 60;
1484 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
1485
1486 END LOOP;
1487
1488 END IF;
1489 CLOSE UNIQUE_DIST_NUM_REFCUR;
1490
1491 END IF;
1492
1493 D_POSITION := 80;
1494 DEBUG(D_MODULE, D_POSITION, 'VALIDATING FOR ZERO AMOUNT AND QUANTITY');
1495
1496 -- Check for any ZERO value for Amount and Quantity
1497 IF (L_DRAFT_ID <> -1 ) THEN
1498
1499 OPEN GT_ZERO_VALIDATION_REFCUR FOR
1500 SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1501 PLL.SHIPMENT_NUM,
1502 GT.DISTRIBUTION_NUM,
1503 GT.QUANTITY_ORDERED,
1504 GT.AMOUNT_ORDERED,
1505 PLL.MATCHING_BASIS
1506
1507 FROM
1508 PO_DISTRIBUTIONS_GT GT,
1509 PO_LINES_MERGE_V POL,
1510 PO_LINE_LOCATIONS_MERGE_V PLL
1511 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1512 GT.DRAFT_ID = PLL.DRAFT_ID AND
1513 PLL.PO_LINE_ID = POL.PO_LINE_ID AND
1514 PLL.DRAFT_ID = POL.DRAFT_ID ;
1515 ELSE
1516 OPEN GT_ZERO_VALIDATION_REFCUR FOR
1517 SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
1518 PLL.SHIPMENT_NUM,
1519 GT.DISTRIBUTION_NUM,
1520 GT.QUANTITY_ORDERED,
1521 GT.AMOUNT_ORDERED,
1522 PLL.MATCHING_BASIS
1523
1524 FROM
1525 PO_DISTRIBUTIONS_GT GT,
1526 PO_LINES_ALL POL,
1527 PO_LINE_LOCATIONS_ALL PLL
1528 WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1529 PLL.PO_LINE_ID = POL.PO_LINE_ID ;
1530
1531 END IF;
1532
1533 LOOP
1534 FETCH GT_ZERO_VALIDATION_REFCUR INTO L_LINE_NUM, L_SHIPMENT_NUM, L_DISTRIBUTION_NUM, L_QUANTITY_ORDERED, L_AMOUNT_ORDERED, L_MATCHING_BASIS;
1535 EXIT WHEN GT_ZERO_VALIDATION_REFCUR%NOTFOUND;
1536
1537 IF (L_MATCHING_BASIS = 'QUANTITY' AND (L_QUANTITY_ORDERED IS NULL OR L_QUANTITY_ORDERED <= 0)) THEN
1538 X_RESULTS.ADD_RESULT(
1539 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1540 , P_MULTI_MOD_REQUEST_ID => 1
1541 , P_RESULT_TYPE => 'ERROR'
1542 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1543 , P_MESSAGE_APPLICATION => 'PO'
1544 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_ENTER_VALUE_GT_ZERO
1545 , P_TOKEN1_NAME => 'LINE_NUM'
1546 , P_TOKEN1_VALUE => L_LINE_NUM
1547 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1548 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1549 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1550 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1551
1552 );
1553
1554 END IF;
1555
1556 D_POSITION := 90;
1557 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FOR QUANTITY SUCCESS') ;
1558
1559
1560 IF (L_MATCHING_BASIS = 'AMOUNT' AND (L_AMOUNT_ORDERED IS NULL OR L_AMOUNT_ORDERED <= 0)) THEN
1561 X_RESULTS.ADD_RESULT(
1562 P_MULTI_MOD_VAL_RESULT_ID => PO_MULTI_MOD_VAL_RESULTS_S.NEXTVAL
1563 , P_MULTI_MOD_REQUEST_ID => 1
1564 , P_RESULT_TYPE => 'ERROR'
1565 , P_VALIDATION_TYPE => 'PRE_SUBMIT'
1566 , P_MESSAGE_APPLICATION => 'PO'
1567 , P_MESSAGE_NAME => PO_MESSAGE_S.PO_LINKREQ_ENTER_VALUE_GT_ZERO
1568 , P_TOKEN1_NAME => 'LINE_NUM'
1569 , P_TOKEN1_VALUE => L_LINE_NUM
1570 , P_TOKEN2_NAME => 'SHIPMENT_NUM'
1571 , P_TOKEN2_VALUE => L_SHIPMENT_NUM
1572 , P_TOKEN3_NAME => 'DISTRIBUTION_NUM'
1573 , P_TOKEN3_VALUE => L_DISTRIBUTION_NUM
1574
1575 );
1576
1577 END IF;
1578
1579 D_POSITION := 100;
1580 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FOR AMOUNT SUCCESS');
1581
1582 END LOOP;
1583 CLOSE GT_ZERO_VALIDATION_REFCUR;
1584
1585 IF (PO_LOG.D_PROC) THEN
1586 PO_LOG.PROC_END(D_MODULE);
1587 END IF;
1588
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591
1592 DEBUG(D_MODULE, D_POSITION, 'ERROR : ' || SUBSTRB(SQLERRM,1,200));
1593 RAISE;
1594
1595
1596 END VALIDATE_LINKED_DISTRIBUTIONS;
1597
1598
1599
1600 /* ============================================================================
1601 NAME: CALCULATE_LOCAL
1602 DESC: THIS PROCEDURE SERVE AS A HOOK TO THE FUNCTION OF LOCALIZATION TEAM.
1603
1604 ==========================================================================*/
1605
1606 PROCEDURE CALCULATE_LOCAL(DOCUMENT_TYPE VARCHAR2,
1607 LEVEL_TYPE VARCHAR2,
1608 LEVEL_ID NUMBER
1609
1610 ) IS
1611
1612 L_CURSOR INTEGER;
1613 SQLSTMT VARCHAR2(2000);
1614 L_EXECUTE INTEGER;
1615 L_RETURN NUMBER;
1616
1617 D_API_NAME CONSTANT VARCHAR2(30) := 'CALCULATE_LOCAL';
1618 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
1619 D_POSITION NUMBER;
1620
1621 BEGIN
1622
1623 -- ADDED CALL TO JG_GLOBE_UTIL_PKG.PROCESS_PO_GLOBE_EVENT
1624
1625 D_POSITION := 0;
1626 IF (PO_LOG.D_PROC) THEN
1627 PO_LOG.PROC_BEGIN(D_MODULE,'LEVEL_TYPE',LEVEL_TYPE);
1628 PO_LOG.PROC_BEGIN(D_MODULE,'LEVEL_ID',LEVEL_ID);
1629 END IF;
1630
1631 ---------------------------------------------------
1632 -- EXECUTE DYNAMICALLY THE REGIONAL PROCEDURE
1633 ---------------------------------------------------
1634 SQLSTMT := 'BEGIN JG_GLOBE_UTIL_PKG.PROCESS_PO_GLOBE_EVENT(:P_DOCUMENT_TYPE,:P_LEVEL_TYPE,:P_LEVEL_ID); END;';
1635
1636 -- CREATE THE SQL STATEMENT
1637 L_CURSOR := DBMS_SQL.OPEN_CURSOR;
1638
1639 -- PARSE THE SQL STATEMENT
1640 DBMS_SQL.PARSE (L_CURSOR, SQLSTMT, DBMS_SQL.NATIVE);
1641
1642 -- DEFINE THE VARIABLES
1643 DBMS_SQL.BIND_VARIABLE(L_CURSOR, ':P_DOCUMENT_TYPE', DOCUMENT_TYPE);
1644 DBMS_SQL.BIND_VARIABLE(L_CURSOR, ':P_LEVEL_TYPE', LEVEL_TYPE);
1645 DBMS_SQL.BIND_VARIABLE(L_CURSOR, ':P_LEVEL_ID', LEVEL_ID);
1646
1647 L_EXECUTE := DBMS_SQL.EXECUTE(L_CURSOR);
1648 -- CLOSE THE CURSOR
1649 DBMS_SQL.CLOSE_CURSOR(L_CURSOR);
1650
1651 D_POSITION := 010;
1652 IF (PO_LOG.D_PROC) THEN
1653 PO_LOG.PROC_END(D_MODULE);
1654 END IF;
1655
1656
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 DEBUG(D_MODULE, D_POSITION, 'ERROR : ' || SUBSTRB(SQLERRM,1,200));
1660 RAISE;
1661
1662 END CALCULATE_LOCAL;
1663
1664
1665 --START OF COMMENTS
1666 --NAME: DEFAULT_ACRN_VALUES
1667 --PRE-REQS:
1668 -- NONE.
1669 --MODIFIES:
1670 -- PO_DISTRIBUTIONS_ALL OR PO_DISTRIBUTIONS_DRAFT_ALL.
1671 --LOCKS:
1672 -- NONE.
1673 --FUNCTION:
1674 -- THIS PROCEDURE CALLS THE API PO_ACCOUNT_HELPER.BUILD_ACRN TO POPULATE
1675 -- THE ACRN VALUES IN THE PO_DISTRIBUTIONS_DRAFT_ALL..
1676 --PARAMETERS:
1677 --IN:
1678 -- HEADER ID
1679 -- DRAFT ID
1680 --TESTING:
1681 -- NONE.
1682 --END OF COMMENTS
1683
1684 PROCEDURE DEFAULT_ACRN_VALUES(
1685 L_HEADER_ID NUMBER,
1686 L_DRAFT_ID NUMBER
1687 )
1688 IS
1689 L_DIST_ID_TBL PO_TBL_NUMBER;
1690 L_CHARGE_ACC_TBL PO_TBL_NUMBER;
1691 L_ACRN_TBL PO_TBL_VARCHAR30;
1692 X_ACRN_TBL PO_TBL_VARCHAR30;
1693 X_RETURN_STATUS VARCHAR2(1);
1694 X_RETURN_MESSAGE PO_TBL_VARCHAR2000;
1695 X_MESSAGE_TOKEN PO_TBL_VARCHAR2000;
1696 L_PROGRESS VARCHAR2(3) := '000';
1697 L_API_NAME VARCHAR2(30) := 'DEFAULT_ACRN_VALUES';
1698 L_MODULE VARCHAR2(40) := 'PO.PLSQL.PO_LINK_REQ_DIST_PVT';
1699 --ACRN CHANGES Bug 13541656
1700 L_LOA_TBL PO_TBL_VARCHAR30;
1701 L_CHANGE_STAT_TBL PO_TBL_VARCHAR30;
1702 BEGIN
1703
1704 SELECT DISTINCT PO_DISTRIBUTION_ID, CODE_COMBINATION_ID, '',CLM_MISC_LOA,'NEW'
1705 BULK COLLECT INTO L_DIST_ID_TBL, L_CHARGE_ACC_TBL, L_ACRN_TBL,
1706 L_LOA_TBL,L_CHANGE_STAT_TBL
1707 FROM ((SELECT PDD.PO_DISTRIBUTION_ID, PDD.CODE_COMBINATION_ID
1708 ,PDD.CLM_MISC_LOA
1709 FROM PO_DISTRIBUTIONS_GT PDD
1710 WHERE PDD.PO_HEADER_ID = L_HEADER_ID
1711 AND PDD.DRAFT_ID = L_DRAFT_ID )
1712 UNION ALL
1713 (SELECT PLD.PO_DISTRIBUTION_ID, PLD.CODE_COMBINATION_ID,
1714 PLD.CLM_MISC_LOA
1715 FROM PO_DISTRIBUTIONS_MERGE_V PLD
1716 WHERE PLD.PO_HEADER_ID = L_HEADER_ID
1717 AND PLD.DRAFT_ID = L_DRAFT_ID)
1718 )
1719 ORDER BY PO_DISTRIBUTION_ID ;
1720
1721
1722 -- CALLING BUIL_ACRN API
1723
1724 IF (PO_LOG.D_STMT) THEN
1725 PO_LOG.STMT(L_MODULE||L_API_NAME, L_PROGRESS, 'CALLING BUILD_ACRN');
1726 END IF;
1727
1728 PO_ACCOUNT_HELPER.BUILD_ACRN
1729 (PO_AUTOCREATE_PARAMS.G_PO_HEADER_ID, L_DIST_ID_TBL, L_CHARGE_ACC_TBL, L_ACRN_TBL,
1730 L_LOA_TBL,L_CHANGE_STAT_TBL,'N',
1731 X_ACRN_TBL, X_RETURN_STATUS, X_RETURN_MESSAGE, X_MESSAGE_TOKEN);
1732
1733 L_ACRN_TBL := X_ACRN_TBL;
1734
1735 IF (PO_LOG.D_STMT) THEN
1736 PO_LOG.STMT(L_MODULE||L_API_NAME, L_PROGRESS, 'AFTER BUILD_ACRN');
1737 END IF;
1738
1739
1740 IF (L_DRAFT_ID <> -1 ) THEN
1741 FOR J IN 1..L_DIST_ID_TBL.COUNT
1742 LOOP
1743 UPDATE PO_DISTRIBUTIONS_DRAFT_ALL
1744 SET ACRN = L_ACRN_TBL(J)
1745 WHERE PO_DISTRIBUTION_ID = L_DIST_ID_TBL(J)
1746 AND L_ACRN_TBL(J) <> 'N/A';
1747
1748 END LOOP;
1749 ELSE
1750 FOR J IN 1..L_DIST_ID_TBL.COUNT
1751 LOOP
1752 UPDATE PO_DISTRIBUTIONS_ALL
1753 SET ACRN = L_ACRN_TBL(J)
1754 WHERE PO_DISTRIBUTION_ID = L_DIST_ID_TBL(J)
1755 AND L_ACRN_TBL(J) <> 'N/A';
1756
1757 END LOOP;
1758 END IF;
1759
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 IF (PO_LOG.D_EXC) THEN
1763 PO_LOG.EXC(L_MODULE, L_PROGRESS, SQLCODE||SQLERRM);
1764 END IF;
1765
1766 RAISE;
1767
1768
1769 END DEFAULT_ACRN_VALUES;
1770 -- END ACRN
1771
1772 --START OF COMMENTS
1773 --NAME: MERGE_LINKREQDIST_TO_TXN_TBL
1774 --PRE-REQS:
1775 -- NONE.
1776 --MODIFIES:
1777 -- NONE.
1778 --LOCKS:
1779 -- NONE.
1780 --FUNCTION:
1781 -- IT WILL MERGER(MOVE) THE NEWLY CREATED DISTRIBUTIONS INTO
1782 -- PO_DISTRIBUTIONS_ALL OR PO_DISTRIBUTIONS_DRAFT_ALL BASED ON THE DRAFT ID.
1783 -- THEN UPDATES THE VALUES BY CALLING METHODS, DEFAULT_ACRN_VALUES,
1784 -- UPDATE_AWARD_DISTRIBUTIONS, CALIBRATE_LAST_DIST_AMOUNT, CALIBRATE_LAST_DIST_QUANTITY AND CALCULATE_LOCAL
1785 -- AND DEFAULTS ACRN VALUES.
1786 --PARAMETERS:
1787 --TESTING:
1788 -- NONE.
1789 --END OF COMMENTS
1790
1791 PROCEDURE MERGE_LINKREQDIST_TO_TXN_TBL
1792 IS
1793
1794 D_API_NAME CONSTANT VARCHAR2(30) := 'MERGE_LINKREQDIST_TO_TXN_TBL';
1795 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
1796 D_POSITION NUMBER;
1797
1798 L_DRAFT_ID NUMBER;
1799 L_HEADER_ID NUMBER;
1800 L_ORG_ID NUMBER;
1801 l_is_ACRN_enabled VARCHAR2(1);
1802
1803
1804 L_LINE_LOC_ID_TBL PO_TBL_NUMBER;
1805 L_LINE_ID_TBL PO_TBL_NUMBER;
1806 L_DIST_ID_TBL PO_TBL_NUMBER;
1807
1808 L_JL_INSTALLED VARCHAR2(30);
1809
1810 BEGIN
1811
1812 D_POSITION := 0;
1813 IF (PO_LOG.D_PROC) THEN
1814 PO_LOG.PROC_BEGIN(D_MODULE,'1',1);
1815 END IF;
1816
1817 SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
1818 FROM PO_SESSION_GT
1819 WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
1820
1821
1822 D_POSITION := 10;
1823 DEBUG(D_MODULE, D_POSITION, 'L_DRAFT_ID: '||L_DRAFT_ID||'L_HEADER_ID: '||L_HEADER_ID);
1824
1825
1826 SELECT DISTINCT PO_LINE_ID
1827 BULK COLLECT INTO L_LINE_ID_TBL
1828 FROM PO_DISTRIBUTIONS_GT;
1829
1830 D_POSITION := 20;
1831 DEBUG(D_MODULE, D_POSITION, 'LINE IDS RETRIEVED TO L_LINE_ID_TBL');
1832
1833
1834 SELECT INDEX_NUM1
1835 BULK COLLECT INTO L_LINE_LOC_ID_TBL
1836 FROM PO_SESSION_GT
1837 WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
1838
1839 D_POSITION := 30;
1840 DEBUG(D_MODULE, D_POSITION, 'LINE LOCATION IDS RETRIEVED TO L_LINE_LOC_ID_TBL');
1841
1842
1843 SELECT PO_DISTRIBUTION_ID
1844 BULK COLLECT INTO L_DIST_ID_TBL
1845 FROM PO_DISTRIBUTIONS_GT;
1846
1847 D_POSITION := 30;
1848 DEBUG(D_MODULE, D_POSITION, 'DISTRIBUTION IDS RETRIEVED TO L_DIST_ID_TBL');
1849
1850 BEGIN
1851 D_POSITION := 40;
1852 DEBUG(D_MODULE, D_POSITION, 'CHECK WHETHER THE REGIONAL PACKAGE IS INSTALLED');
1853
1854 ---------------------------------------------------
1855 -- CHECK WHETHER THE REGIONAL PACKAGE IS INSTALLED
1856 ---------------------------------------------------
1857 SELECT DISTINCT 'PACKAGE INSTALLED'
1858 INTO L_JL_INSTALLED
1859 FROM USER_OBJECTS
1860 WHERE OBJECT_NAME = 'JG_GLOBE_UTIL_PKG'
1861 AND OBJECT_TYPE = 'PACKAGE BODY';
1862
1863 D_POSITION := 50;
1864 DEBUG(D_MODULE, D_POSITION, 'CALLING CALCULATE_LOCAL. L_JL_INSTALLED: '||L_JL_INSTALLED);
1865
1866 FOR I IN 1..L_DIST_ID_TBL.COUNT
1867 LOOP
1868 CALCULATE_LOCAL('PO', 'DISTRIBUTION', L_DIST_ID_TBL(I));
1869 END LOOP;
1870
1871 EXCEPTION
1872 WHEN NO_DATA_FOUND THEN
1873
1874 ----------------------------------------
1875 -- REGIONAL PROCEDURE IS NOT INSTALLED
1876 ----------------------------------------
1877 D_POSITION := 60;
1878 DEBUG(D_MODULE, D_POSITION, 'NO_DATA_FOUND: '||SQLERRM);
1879
1880 END;
1881
1882 IF (L_DRAFT_ID <> -1 ) THEN
1883
1884 D_POSITION := 80;
1885 DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_DRAFT_ALL');
1886
1887 INSERT INTO PO_DISTRIBUTIONS_DRAFT_ALL --<SHARED PROC FPJ>
1888 (PO_DISTRIBUTION_ID,
1889 DRAFT_ID,
1890 --WHO COLUMNS
1891 ---------------------------------------------------------------
1892 LAST_UPDATE_DATE,
1893 LAST_UPDATED_BY,
1894 LAST_UPDATE_LOGIN,
1895 CREATION_DATE,
1896 CREATED_BY,
1897 REQUEST_ID,
1898 PROGRAM_APPLICATION_ID,
1899 PROGRAM_ID,
1900 PROGRAM_UPDATE_DATE,
1901 ---------------------------------------------------------------
1902 PO_HEADER_ID,
1903 PO_LINE_ID,
1904 LINE_LOCATION_ID,
1905 PO_RELEASE_ID,
1906 REQ_DISTRIBUTION_ID,
1907 SET_OF_BOOKS_ID,
1908 CODE_COMBINATION_ID,
1909 DELIVER_TO_LOCATION_ID,
1910 DELIVER_TO_PERSON_ID,
1911 QUANTITY_ORDERED,
1912 QUANTITY_DELIVERED,
1913 QUANTITY_BILLED,
1914 QUANTITY_CANCELLED,
1915 AMOUNT_ORDERED,
1916 AMOUNT_DELIVERED,
1917 AMOUNT_CANCELLED,
1918 AMOUNT_BILLED,
1919 RATE_DATE,
1920 RATE,
1921 ACCRUED_FLAG,
1922 ENCUMBERED_FLAG,
1923 GL_ENCUMBERED_DATE,
1924 GL_ENCUMBERED_PERIOD_NAME,
1925 DISTRIBUTION_NUM,
1926 DESTINATION_TYPE_CODE,
1927 DESTINATION_ORGANIZATION_ID,
1928 DESTINATION_SUBINVENTORY,
1929 BUDGET_ACCOUNT_ID,
1930 ACCRUAL_ACCOUNT_ID,
1931 VARIANCE_ACCOUNT_ID,
1932
1933 --< SHARED PROC FPJ START >
1934 DEST_CHARGE_ACCOUNT_ID,
1935 DEST_VARIANCE_ACCOUNT_ID,
1936 --< SHARED PROC FPJ END >
1937
1938 WIP_ENTITY_ID,
1939 WIP_LINE_ID,
1940 WIP_REPETITIVE_SCHEDULE_ID,
1941 WIP_OPERATION_SEQ_NUM,
1942 WIP_RESOURCE_SEQ_NUM,
1943 BOM_RESOURCE_ID,
1944 PREVENT_ENCUMBRANCE_FLAG,
1945 PROJECT_ID,
1946 TASK_ID,
1947 END_ITEM_UNIT_NUMBER,
1948 EXPENDITURE_TYPE,
1949 PROJECT_ACCOUNTING_CONTEXT,
1950 DESTINATION_CONTEXT,
1951 EXPENDITURE_ORGANIZATION_ID,
1952 EXPENDITURE_ITEM_DATE,
1953 ACCRUE_ON_RECEIPT_FLAG,
1954 KANBAN_CARD_ID,
1955 TAX_RECOVERY_OVERRIDE_FLAG,
1956 RECOVERY_RATE,
1957 AWARD_ID,
1958 OKE_CONTRACT_LINE_ID,
1959 OKE_CONTRACT_DELIVERABLE_ID,
1960 ORG_ID,
1961 DISTRIBUTION_TYPE,
1962 TAX_ATTRIBUTE_UPDATE_CODE,
1963 --PARTIAL FUNDING ATTRIBUTES
1964 PARTIAL_FUNDED_FLAG,
1965 FUNDED_VALUE,
1966 QUANTITY_FUNDED,
1967 AMOUNT_FUNDED ,
1968 CHANGE_IN_FUNDED_VALUE,
1969 GROUP_LINE_ID,
1970 CLM_MISC_LOA,
1971 CLM_DEFENCE_FUNDING,
1972 CLM_FMS_CASE_NUMBER,
1973 CLM_AGENCY_ACCT_IDENTIFIER,
1974 CHANGE_STATUS,
1975 QUANTITY_FINANCED,
1976 AMOUNT_FINANCED,
1977 QUANTITY_RECOUPED,
1978 AMOUNT_RECOUPED,
1979 RETAINAGE_WITHHELD_AMOUNT,
1980 RETAINAGE_RELEASED_AMOUNT,
1981 PAR_DRAFT_ID, -- FOR LINKING PAR
1982 PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
1983 )
1984 SELECT PO_DISTRIBUTION_ID,
1985 DRAFT_ID,
1986 --WHO COLUMNS
1987 ---------------------------------------------------------------
1988 LAST_UPDATE_DATE,
1989 LAST_UPDATED_BY,
1990 LAST_UPDATE_LOGIN,
1991 CREATION_DATE,
1992 CREATED_BY,
1993 REQUEST_ID,
1994 PROGRAM_APPLICATION_ID,
1995 PROGRAM_ID,
1996 PROGRAM_UPDATE_DATE,
1997 ---------------------------------------------------------------
1998 PO_HEADER_ID,
1999 PO_LINE_ID,
2000 LINE_LOCATION_ID,
2001 PO_RELEASE_ID,
2002 REQ_DISTRIBUTION_ID,
2003 SET_OF_BOOKS_ID,
2004 CODE_COMBINATION_ID,
2005 DELIVER_TO_LOCATION_ID,
2006 DELIVER_TO_PERSON_ID,
2007 QUANTITY_ORDERED,
2008 QUANTITY_DELIVERED,
2009 QUANTITY_BILLED,
2010 QUANTITY_CANCELLED,
2011 AMOUNT_ORDERED,
2012 AMOUNT_DELIVERED,
2013 AMOUNT_CANCELLED,
2014 AMOUNT_BILLED,
2015 RATE_DATE,
2016 RATE,
2017 ACCRUED_FLAG,
2018 ENCUMBERED_FLAG,
2019 GL_ENCUMBERED_DATE,
2020 GL_ENCUMBERED_PERIOD_NAME,
2021 DISTRIBUTION_NUM,
2022 DESTINATION_TYPE_CODE,
2023 DESTINATION_ORGANIZATION_ID,
2024 DESTINATION_SUBINVENTORY,
2025 BUDGET_ACCOUNT_ID,
2026 ACCRUAL_ACCOUNT_ID,
2027 VARIANCE_ACCOUNT_ID,
2028 DEST_CHARGE_ACCOUNT_ID,
2029 DEST_VARIANCE_ACCOUNT_ID,
2030 WIP_ENTITY_ID,
2031 WIP_LINE_ID,
2032 WIP_REPETITIVE_SCHEDULE_ID,
2033 WIP_OPERATION_SEQ_NUM,
2034 WIP_RESOURCE_SEQ_NUM,
2035 BOM_RESOURCE_ID,
2036 PREVENT_ENCUMBRANCE_FLAG,
2037 PROJECT_ID,
2038 TASK_ID,
2039 END_ITEM_UNIT_NUMBER,
2040 EXPENDITURE_TYPE,
2041 PROJECT_ACCOUNTING_CONTEXT,
2042 DESTINATION_CONTEXT,
2043 EXPENDITURE_ORGANIZATION_ID,
2044 EXPENDITURE_ITEM_DATE,
2045 ACCRUE_ON_RECEIPT_FLAG,
2046 KANBAN_CARD_ID,
2047 TAX_RECOVERY_OVERRIDE_FLAG, --
2048 RECOVERY_RATE,
2049 AWARD_ID,
2050 OKE_CONTRACT_LINE_ID,
2051 OKE_CONTRACT_DELIVERABLE_ID,
2052 ORG_ID,
2053 DISTRIBUTION_TYPE,
2054 'CREATE',
2055 --PARTIAL FUNDING ATTRIBUTES
2056 PARTIAL_FUNDED_FLAG,
2057 FUNDED_VALUE,
2058 QUANTITY_FUNDED,
2059 AMOUNT_FUNDED ,
2060 FUNDED_VALUE,
2061 GROUP_LINE_ID,
2062 CLM_MISC_LOA,
2063 CLM_DEFENCE_FUNDING,
2064 CLM_FMS_CASE_NUMBER,
2065 CLM_AGENCY_ACCT_IDENTIFIER,
2066 'NEW',
2067 0, --QUANTITY_FINANCED
2068 0, --AMOUNT_FINANCED
2069 0, --QUANTITY_RECOUPED
2070 0, --QUANTITY_RECOUPED
2071 0, --RETAINAGE_WITHHELD_AMOUNT
2072 0, --RETAINAGE_RELEASED_AMOUNT
2073 PAR_DRAFT_ID, -- FOR LINKING PAR
2074 PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
2075 FROM PO_DISTRIBUTIONS_GT;
2076
2077 --INSERT INTO PO_DISTRIBUTIONS_DRAFT_ALL SELECT * FROM PO_DISTRIBUTIONS_GT;
2078
2079 PO_AUTOCREATE_PARAMS.G_DRAFT_ID := L_DRAFT_ID;
2080
2081 D_POSITION := 90;
2082 DEBUG(D_MODULE, D_POSITION, 'CALLING UPDATE_AWARD_DISTRIBUTIONS');
2083
2084 FOR I IN 1..L_LINE_ID_TBL.COUNT
2085 LOOP
2086
2087
2088 PO_AUTO_DIST_PROCESS_PVT.UPDATE_AWARD_DISTRIBUTIONS
2089 (
2090 P_INTF_HEADER_ID => L_HEADER_ID,
2091 P_INTF_LINE_ID => L_LINE_ID_TBL(I),
2092 P_TABLE_TYPE => 'ALL',
2093 P_PO_LINE_ID => NULL
2094 );
2095
2096
2097 END LOOP;
2098
2099 D_POSITION := 100;
2100 DEBUG(D_MODULE, D_POSITION, 'CALLING CALIBRATE_LAST_DIST_AMOUNT AND CALIBRATE_LAST_DIST_QUANTITY');
2101
2102
2103 -- FOR I IN 1..L_LINE_LOC_ID_TBL.COUNT
2104 -- LOOP
2105
2106 -- /*CORRECT LAST DISTRIBUTION AMOUNT FOR ANY CONVERSION AND
2107 -- ROUNDING INACCURACIES TO ENSURE THAT THE DISTRIBUTION AMOUNTS ADD UP
2108 -- TO THEIR CORRESPONDING SHIPMENT AMOUNT.*/
2109
2110 -- PO_AUTO_DIST_PROCESS_PVT.CALIBRATE_LAST_DIST_AMOUNT
2111 -- (
2112 -- P_LINE_LOCATION_ID => L_LINE_LOC_ID_TBL(I)
2113 -- );
2114
2115 -- -- CORRECT LAST DISTRIBUTION QUANTITY FOR ANY CONVERSION AND
2116 -- -- ROUNDING INACCURACIES TO ENSURE THAT THE DISTRIBUTION QUANTITY ADD UP
2117 -- -- TO THEIR CORRESPONDING SHIPMENT QUANTITY.
2118
2119 -- PO_AUTO_DIST_PROCESS_PVT.CALIBRATE_LAST_DIST_QUANTITY
2120 -- (
2121 -- P_LINE_LOCATION_ID => L_LINE_LOC_ID_TBL(I)
2122 -- );
2123
2124 -- END LOOP;
2125
2126 ELSE
2127
2128 D_POSITION := 110;
2129 DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_ALL');
2130
2131 INSERT INTO PO_DISTRIBUTIONS_ALL (
2132 PO_DISTRIBUTION_ID ,
2133 LAST_UPDATE_DATE ,
2134 LAST_UPDATED_BY ,
2135 PO_HEADER_ID ,
2136 PO_LINE_ID ,
2137 LINE_LOCATION_ID ,
2138 SET_OF_BOOKS_ID ,
2139 CODE_COMBINATION_ID ,
2140 QUANTITY_ORDERED ,
2141 LAST_UPDATE_LOGIN ,
2142 CREATION_DATE ,
2143 CREATED_BY ,
2144 PO_RELEASE_ID ,
2145 QUANTITY_DELIVERED ,
2146 QUANTITY_BILLED ,
2147 QUANTITY_CANCELLED ,
2148 REQ_HEADER_REFERENCE_NUM ,
2149 REQ_LINE_REFERENCE_NUM ,
2150 REQ_DISTRIBUTION_ID ,
2151 DELIVER_TO_LOCATION_ID ,
2152 DELIVER_TO_PERSON_ID ,
2153 RATE_DATE ,
2154 RATE ,
2155 AMOUNT_BILLED ,
2156 ACCRUED_FLAG ,
2157 ENCUMBERED_FLAG ,
2158 ENCUMBERED_AMOUNT ,
2159 UNENCUMBERED_QUANTITY ,
2160 UNENCUMBERED_AMOUNT ,
2161 FAILED_FUNDS_LOOKUP_CODE ,
2162 GL_ENCUMBERED_DATE ,
2163 GL_ENCUMBERED_PERIOD_NAME ,
2164 GL_CANCELLED_DATE ,
2165 DESTINATION_TYPE_CODE ,
2166 DESTINATION_ORGANIZATION_ID ,
2167 DESTINATION_SUBINVENTORY ,
2168 ATTRIBUTE_CATEGORY ,
2169 ATTRIBUTE1 ,
2170 ATTRIBUTE2 ,
2171 ATTRIBUTE3 ,
2172 ATTRIBUTE4 ,
2173 ATTRIBUTE5 ,
2174 ATTRIBUTE6 ,
2175 ATTRIBUTE7 ,
2176 ATTRIBUTE8 ,
2177 ATTRIBUTE9 ,
2178 ATTRIBUTE10 ,
2179 ATTRIBUTE11 ,
2180 ATTRIBUTE12 ,
2181 ATTRIBUTE13 ,
2182 ATTRIBUTE14 ,
2183 ATTRIBUTE15 ,
2184 WIP_ENTITY_ID ,
2185 WIP_OPERATION_SEQ_NUM ,
2186 WIP_RESOURCE_SEQ_NUM ,
2187 WIP_REPETITIVE_SCHEDULE_ID ,
2188 WIP_LINE_ID ,
2189 BOM_RESOURCE_ID ,
2190 BUDGET_ACCOUNT_ID ,
2191 ACCRUAL_ACCOUNT_ID ,
2192 VARIANCE_ACCOUNT_ID ,
2193 PREVENT_ENCUMBRANCE_FLAG ,
2194 GOVERNMENT_CONTEXT ,
2195 DESTINATION_CONTEXT ,
2196 DISTRIBUTION_NUM ,
2197 SOURCE_DISTRIBUTION_ID ,
2198 REQUEST_ID ,
2199 PROGRAM_APPLICATION_ID ,
2200 PROGRAM_ID ,
2201 PROGRAM_UPDATE_DATE ,
2202 PROJECT_ID ,
2203 TASK_ID ,
2204 EXPENDITURE_TYPE ,
2205 PROJECT_ACCOUNTING_CONTEXT ,
2206 EXPENDITURE_ORGANIZATION_ID ,
2207 GL_CLOSED_DATE ,
2208 ACCRUE_ON_RECEIPT_FLAG ,
2209 EXPENDITURE_ITEM_DATE ,
2210 ORG_ID ,
2211 KANBAN_CARD_ID ,
2212 AWARD_ID ,
2213 MRC_RATE_DATE ,
2214 MRC_RATE ,
2215 MRC_ENCUMBERED_AMOUNT ,
2216 MRC_UNENCUMBERED_AMOUNT ,
2217 END_ITEM_UNIT_NUMBER ,
2218 TAX_RECOVERY_OVERRIDE_FLAG ,
2219 RECOVERABLE_TAX ,
2220 NONRECOVERABLE_TAX ,
2221 RECOVERY_RATE ,
2222 OKE_CONTRACT_LINE_ID ,
2223 OKE_CONTRACT_DELIVERABLE_ID ,
2224 AMOUNT_ORDERED ,
2225 AMOUNT_DELIVERED ,
2226 AMOUNT_CANCELLED ,
2227 DISTRIBUTION_TYPE ,
2228 AMOUNT_TO_ENCUMBER ,
2229 INVOICE_ADJUSTMENT_FLAG ,
2230 DEST_CHARGE_ACCOUNT_ID ,
2231 DEST_VARIANCE_ACCOUNT_ID ,
2232 TAX_ATTRIBUTE_UPDATE_CODE ,
2233 PARTIAL_FUNDED_FLAG,
2234 FUNDED_VALUE,
2235 QUANTITY_FUNDED,
2236 AMOUNT_FUNDED ,
2237 CHANGE_IN_FUNDED_VALUE,
2238 GROUP_LINE_ID ,
2239 CLM_MISC_LOA,
2240 CLM_DEFENCE_FUNDING,
2241 CLM_FMS_CASE_NUMBER,
2242 CLM_AGENCY_ACCT_IDENTIFIER,
2243 QUANTITY_FINANCED,
2244 AMOUNT_FINANCED,
2245 QUANTITY_RECOUPED,
2246 AMOUNT_RECOUPED,
2247 RETAINAGE_WITHHELD_AMOUNT,
2248 RETAINAGE_RELEASED_AMOUNT
2249
2250 )
2251 SELECT
2252 PO_DISTRIBUTION_ID ,
2253 LAST_UPDATE_DATE ,
2254 LAST_UPDATED_BY ,
2255 PO_HEADER_ID ,
2256 PO_LINE_ID ,
2257 LINE_LOCATION_ID ,
2258 SET_OF_BOOKS_ID ,
2259 CODE_COMBINATION_ID ,
2260 QUANTITY_ORDERED ,
2261 LAST_UPDATE_LOGIN ,
2262 CREATION_DATE ,
2263 CREATED_BY ,
2264 PO_RELEASE_ID ,
2265 QUANTITY_DELIVERED ,
2266 QUANTITY_BILLED ,
2267 QUANTITY_CANCELLED ,
2268 REQ_HEADER_REFERENCE_NUM ,
2269 REQ_LINE_REFERENCE_NUM ,
2270 REQ_DISTRIBUTION_ID ,
2271 DELIVER_TO_LOCATION_ID ,
2272 DELIVER_TO_PERSON_ID ,
2273 RATE_DATE ,
2274 RATE ,
2275 AMOUNT_BILLED ,
2276 ACCRUED_FLAG ,
2277 ENCUMBERED_FLAG ,
2278 ENCUMBERED_AMOUNT ,
2279 UNENCUMBERED_QUANTITY ,
2280 UNENCUMBERED_AMOUNT ,
2281 FAILED_FUNDS_LOOKUP_CODE ,
2282 GL_ENCUMBERED_DATE ,
2283 GL_ENCUMBERED_PERIOD_NAME ,
2284 GL_CANCELLED_DATE ,
2285 DESTINATION_TYPE_CODE ,
2286 DESTINATION_ORGANIZATION_ID ,
2287 DESTINATION_SUBINVENTORY ,
2288 ATTRIBUTE_CATEGORY ,
2289 ATTRIBUTE1 ,
2290 ATTRIBUTE2 ,
2291 ATTRIBUTE3 ,
2292 ATTRIBUTE4 ,
2293 ATTRIBUTE5 ,
2294 ATTRIBUTE6 ,
2295 ATTRIBUTE7 ,
2296 ATTRIBUTE8 ,
2297 ATTRIBUTE9 ,
2298 ATTRIBUTE10 ,
2299 ATTRIBUTE11 ,
2300 ATTRIBUTE12 ,
2301 ATTRIBUTE13 ,
2302 ATTRIBUTE14 ,
2303 ATTRIBUTE15 ,
2304 WIP_ENTITY_ID ,
2305 WIP_OPERATION_SEQ_NUM ,
2306 WIP_RESOURCE_SEQ_NUM ,
2307 WIP_REPETITIVE_SCHEDULE_ID ,
2308 WIP_LINE_ID ,
2309 BOM_RESOURCE_ID ,
2310 BUDGET_ACCOUNT_ID ,
2311 ACCRUAL_ACCOUNT_ID ,
2312 VARIANCE_ACCOUNT_ID ,
2313 PREVENT_ENCUMBRANCE_FLAG ,
2314 GOVERNMENT_CONTEXT ,
2315 DESTINATION_CONTEXT ,
2316 DISTRIBUTION_NUM ,
2317 SOURCE_DISTRIBUTION_ID ,
2318 REQUEST_ID ,
2319 PROGRAM_APPLICATION_ID ,
2320 PROGRAM_ID ,
2321 PROGRAM_UPDATE_DATE ,
2322 PROJECT_ID ,
2323 TASK_ID ,
2324 EXPENDITURE_TYPE ,
2325 PROJECT_ACCOUNTING_CONTEXT ,
2326 EXPENDITURE_ORGANIZATION_ID ,
2327 GL_CLOSED_DATE ,
2328 ACCRUE_ON_RECEIPT_FLAG ,
2329 EXPENDITURE_ITEM_DATE ,
2330 ORG_ID ,
2331 KANBAN_CARD_ID ,
2332 AWARD_ID ,
2333 MRC_RATE_DATE ,
2334 MRC_RATE ,
2335 MRC_ENCUMBERED_AMOUNT ,
2336 MRC_UNENCUMBERED_AMOUNT ,
2337 END_ITEM_UNIT_NUMBER ,
2338 TAX_RECOVERY_OVERRIDE_FLAG ,
2339 RECOVERABLE_TAX ,
2340 NONRECOVERABLE_TAX ,
2341 RECOVERY_RATE ,
2342 OKE_CONTRACT_LINE_ID ,
2343 OKE_CONTRACT_DELIVERABLE_ID ,
2344 AMOUNT_ORDERED ,
2345 AMOUNT_DELIVERED ,
2346 AMOUNT_CANCELLED ,
2347 DISTRIBUTION_TYPE ,
2348 AMOUNT_TO_ENCUMBER ,
2349 NULL, --INVOICE_ADJUSTMENT_FLAG ,
2350 DEST_CHARGE_ACCOUNT_ID ,
2351 DEST_VARIANCE_ACCOUNT_ID ,
2352 'CREATE' ,
2353 PARTIAL_FUNDED_FLAG,
2354 FUNDED_VALUE,
2355 QUANTITY_FUNDED,
2356 AMOUNT_FUNDED ,
2357 FUNDED_VALUE,
2358 GROUP_LINE_ID ,
2359 CLM_MISC_LOA,
2360 CLM_DEFENCE_FUNDING,
2361 CLM_FMS_CASE_NUMBER,
2362 CLM_AGENCY_ACCT_IDENTIFIER,
2363 0, --QUANTITY_FINANCED
2364 0, --AMOUNT_FINANCED
2365 0, --QUANTITY_RECOUPED
2366 0, --QUANTITY_RECOUPED
2367 0, --RETAINAGE_WITHHELD_AMOUNT
2368 0 --RETAINAGE_RELEASED_AMOUNT
2369
2370 FROM PO_DISTRIBUTIONS_GT;
2371
2372 D_POSITION := 120;
2373 DEBUG(D_MODULE, D_POSITION, 'CALLING UPDATE_AWARD_DISTRIBUTIONS');
2374
2375 FOR I IN 1..L_LINE_ID_TBL.COUNT
2376 LOOP
2377
2378
2379 PO_INTERFACE_S.UPDATE_AWARD_DISTRIBUTIONS
2380 (
2381 P_TABLE_TYPE => 'ALL',
2382 P_PO_LINE_ID => L_LINE_ID_TBL(I)
2383 );
2384
2385 END LOOP;
2386
2387 D_POSITION := 130;
2388 DEBUG(D_MODULE, D_POSITION, 'CALLING CALIBRATE_LAST_DIST_AMOUNT AND CALIBRATE_LAST_DIST_QUANTITY');
2389
2390
2391 -- FOR I IN 1..L_LINE_LOC_ID_TBL.COUNT
2392 -- LOOP
2393
2394 -- /*CORRECT LAST DISTRIBUTION AMOUNT FOR ANY CONVERSION AND
2395 -- ROUNDING INACCURACIES TO ENSURE THAT THE DISTRIBUTION AMOUNTS ADD UP
2396 -- TO THEIR CORRESPONDING SHIPMENT AMOUNT.*/
2397
2398 -- PO_INTERFACE_S.CALIBRATE_LAST_DIST_AMOUNT
2399 -- (
2400 -- P_LINE_LOCATION_ID => L_LINE_LOC_ID_TBL(I)
2401 -- );
2402 -- -- CORRECT LAST DISTRIBUTION QUANTITY FOR ANY CONVERSION AND
2403 -- -- ROUNDING INACCURACIES TO ENSURE THAT THE DISTRIBUTION QUANTITY ADD UP
2404 -- -- TO THEIR CORRESPONDING SHIPMENT QUANTITY.
2405 -- PO_INTERFACE_S.CALIBRATE_LAST_DIST_QUANTITY
2406 -- (
2407 -- P_LINE_LOCATION_ID => L_LINE_LOC_ID_TBL(I)
2408 -- );
2409
2410
2411 -- END LOOP;
2412
2413 END IF;
2414
2415 D_POSITION := 140;
2416 DEBUG(D_MODULE, D_POSITION, 'CALLING DEFAULT_ACRN_VALUES');
2417 SELECT DISTINCT ORG_ID
2418 INTO L_ORG_ID FROM PO_DISTRIBUTIONS_GT
2419 where rownum <2;
2420 l_is_ACRN_enabled := PO_PARTIAL_FUNDING_PKG.Is_ACRN_enabled(l_org_id);
2421 IF(l_is_ACRN_enabled='Y') THEN
2422 DEFAULT_ACRN_VALUES(L_HEADER_ID,L_DRAFT_ID);
2423 END IF;
2424
2425
2426 D_POSITION := 140 ;
2427 IF (PO_LOG.D_PROC) THEN
2428 PO_LOG.PROC_END(D_MODULE);
2429 END IF;
2430
2431
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434
2435 DEBUG(D_MODULE, D_POSITION, 'ERROR : ' || SUBSTRB(SQLERRM,1,200));
2436 RAISE;
2437
2438 END MERGE_LINKREQDIST_TO_TXN_TBL;
2439
2440
2441
2442 --START OF COMMENTS
2443 --NAME: REMOVE_FROM_REQ_POOL
2444 --PRE-REQS:
2445 -- NONE.
2446 --MODIFIES:
2447 -- NONE.
2448 --LOCKS:
2449 -- NONE.
2450 --PROCEDURE:
2451 -- It Update the req_pool_flag of selected requisition lines along with the lines within whole structure, to null value, so that
2452 -- it wont be picked up in BWC requisitions for auto create.
2453 --PARAMETERS:
2454 --IN:
2455 --p_requisition_line_id
2456 -- List of selected requisition lines
2457 --IN OUT:
2458 --x_return_status
2459 -- SUCCESS FAILURE RESPONSE
2460 --x_error_msg
2461 -- ERROR MESSAGE
2462 --TESTING:
2463 -- NONE.
2464 --END OF COMMENTS
2465
2466 PROCEDURE REMOVE_FROM_REQ_POOL
2467 (
2468 p_requisition_line_id IN PO_TBL_NUMBER,
2469 p_requisition_header_id IN PO_TBL_NUMBER,
2470 x_return_status OUT NOCOPY VARCHAR2,
2471 x_error_msg OUT NOCOPY VARCHAR2
2472 ) IS
2473
2474 D_API_NAME CONSTANT VARCHAR2(30) := 'REMOVE_FROM_REQ_POOL';
2475 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
2476 D_POSITION NUMBER;
2477
2478 BEGIN
2479
2480 D_POSITION := 0;
2481 IF (PO_LOG.D_PROC) THEN
2482 PO_LOG.PROC_BEGIN(D_MODULE,'1',1);
2483 END IF;
2484
2485 x_return_status := C_RESULT_TYPE_SUCCESS;
2486
2487
2488 --looping thru the line_id array and updating the reqs_in_pool_flag for the entire structure(including slins and options) of the selected line
2489 FOR i in 1..p_requisition_line_id.COUNT LOOP
2490
2491 UPDATE po_requisition_lines_all
2492
2493 SET reqs_in_pool_flag = null,
2494 last_update_date = SYSDATE,
2495 last_updated_by = FND_GLOBAL.USER_ID,
2496 last_update_login = FND_GLOBAL.LOGIN_ID
2497
2498 WHERE ((requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i) )
2499 OR (group_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i))
2500 OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
2501 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
2502 OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2503 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
2504 OR (clm_base_line_num = p_requisition_line_id(i))
2505 OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
2506 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
2507 OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
2508 WHERE clm_base_line_num = p_requisition_line_id(i)))
2509 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2510 WHERE clm_base_line_num = p_requisition_line_id(i)))
2511 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2512 WHERE clm_base_line_num = p_requisition_line_id(i)))
2513 OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2514 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2515 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i))))
2516 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2517 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2518 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2519 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)) )))
2520 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2521 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2522 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2523 WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)) )))
2524 );
2525
2526 D_POSITION := 20;
2527 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
2528
2529 END LOOP;
2530
2531 D_POSITION := 30;
2532 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' End proc: remove_from_req_pool');
2533
2534
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 x_return_status := C_RESULT_TYPE_FAILURE;
2538
2539 D_POSITION := 40;
2540 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Unexpected error occured');
2541 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' sqlerrm: '||sqlerrm);
2542
2543 RAISE; --raise exception
2544
2545
2546 END REMOVE_FROM_REQ_POOL;
2547
2548 --START OF COMMENTS
2549 --NAME: MERGE_DIST_PARENTS_TO_DRAFT
2550 --PROCEDURE:
2551 -- If on a Modification, unmodified Lines/Shipments from the Base Award are linked,
2552 -- this API will merge these into Draft with CHANGE_STATUS as 'NOCHANGE'
2553 --END OF COMMENTS
2554 PROCEDURE merge_dist_parents_to_draft
2555 IS
2556 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_DIST_PARENTS_TO_DRAFT';
2557 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2558 d_position NUMBER;
2559
2560 l_draft_id NUMBER;
2561 po_line_loc_id_tbl PO_TBL_NUMBER;
2562 po_line_id_tbl PO_TBL_NUMBER;
2563 draft_id_tbl PO_TBL_NUMBER;
2564 delete_flag_tbl PO_TBL_VARCHAR1;
2565 x_record_already_exist_tbl PO_TBL_VARCHAR1;
2566
2567 BEGIN
2568
2569 d_position := 0;
2570 IF (PO_LOG.d_proc) THEN
2571 PO_LOG.proc_begin(d_module,'1',1);
2572 END IF;
2573
2574 SELECT index_num2
2575 INTO l_draft_id
2576 FROM po_session_gt
2577 WHERE index_char1 = 'LINKREQDIST_LINELOCID'
2578 AND ROWNUM < 2;
2579
2580 IF l_draft_id = -1 THEN
2581 debug(d_module, d_position, 'Returning as Document is not Modification');
2582 RETURN;
2583 END IF;
2584
2585 d_position := 10;
2586 debug(d_module, d_position, 'Merging Shipments');
2587
2588 -- Fetch the matching txn line_locations, not yet in draft
2589 SELECT PLL.line_location_id,
2590 l_draft_id,
2591 'N'
2592 BULK COLLECT
2593 INTO po_line_loc_id_tbl,
2594 draft_id_tbl,
2595 delete_flag_tbl
2596 FROM po_line_locations_all PLL
2597 WHERE EXISTS(
2598 SELECT 'Shipment exists in txn'
2599 FROM po_distributions_gt GT
2600 WHERE PLL.line_location_id = GT.line_location_id)
2601 AND NOT EXISTS(
2602 SELECT 'Shipment exists in draft'
2603 FROM po_line_locations_draft_all PLLD
2604 WHERE PLLD.line_location_id = PLL.line_location_id
2605 AND PLLD.draft_id = l_draft_id);
2606
2607 d_position := 20;
2608 debug(d_module, d_position, 'No of Matched Shipment Records: ' || po_line_loc_id_tbl.Count);
2609
2610 -- Transfer these Shipment records to Draft, and update change_status appropriately
2611 IF po_line_loc_id_tbl.Count > 0 THEN
2612
2613 PO_LINE_LOCATIONS_DRAFT_PKG.sync_draft_from_txn(
2614 p_line_location_id_tbl => po_line_loc_id_tbl,
2615 p_draft_id_tbl => draft_id_tbl,
2616 p_delete_flag_tbl => delete_flag_tbl,
2617 x_record_already_exist_tbl => x_record_already_exist_tbl);
2618
2619 FORALL i IN 1..po_line_loc_id_tbl.COUNT
2620 UPDATE po_line_locations_draft_all PLLD
2621 SET PLLD.change_status = 'NOCHANGE'
2622 WHERE PLLD.line_location_id = po_line_loc_id_tbl(i)
2623 AND PLLD.draft_id = l_draft_id;
2624
2625 END IF;
2626
2627 d_position := 30;
2628 debug(d_module, d_position, 'Merging Lines');
2629
2630 -- Fetch the matching txn lines, not yet in draft
2631 SELECT PL.po_line_id,
2632 l_draft_id,
2633 'N'
2634 BULK COLLECT
2635 INTO po_line_id_tbl,
2636 draft_id_tbl,
2637 delete_flag_tbl
2638 FROM po_lines_all PL
2639 WHERE EXISTS(
2640 SELECT 'Line exists in txn'
2641 FROM po_distributions_gt GT
2642 WHERE PL.po_line_id = GT.po_line_id)
2643 AND NOT EXISTS(
2644 SELECT 'Line exists in draft'
2645 FROM po_lines_draft_all PLD
2646 WHERE PLD.po_line_id = PL.po_line_id
2647 AND PLD.draft_id = l_draft_id);
2648
2649 d_position := 20;
2650 debug(d_module, d_position, 'No of Matched Line Records: ' || po_line_id_tbl.Count);
2651
2652 -- Transfer these Line records to Draft, and update change_status appropriately
2653 IF po_line_id_tbl.Count > 0 THEN
2654
2655 PO_LINES_DRAFT_PKG.sync_draft_from_txn(
2656 p_po_line_id_tbl => po_line_id_tbl,
2657 p_draft_id_tbl => draft_id_tbl,
2658 p_delete_flag_tbl => delete_flag_tbl,
2659 x_record_already_exist_tbl => x_record_already_exist_tbl);
2660
2661 FORALL i IN 1..po_line_id_tbl.COUNT
2662 UPDATE po_lines_draft_all PLD
2663 SET PLD.change_status = 'NOCHANGE'
2664 WHERE PLD.po_line_id = po_line_id_tbl(i)
2665 AND PLD.draft_id = l_draft_id;
2666
2667 END IF;
2668
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671 DEBUG(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200));
2672 RAISE;
2673
2674 END merge_dist_parents_to_draft;
2675
2676
2677 --START OF COMMENTS
2678 --NAME: CREATE_LINK_REQ_DIST_GT
2679 --PRE-REQS:
2680 -- NONE.
2681 --MODIFIES:
2682 -- NONE.
2683 --LOCKS:
2684 -- NONE.
2685 --FUNCTION:
2686 -- IT VALIDATES THE NEW DISTRIBUTIONS FOR CALCULATED VALUES, QUANTITY,
2687 -- DISTRIBUTION AMOUNT, FUNDED VALUE,
2688 -- WITH THE SPECIFIC SOURCE REQUISITION IT WAS CREATED. ON SUCCESSFULL VALIDATION IT WILL
2689 -- MERGER(MOVE) THE NEWLY CREATED DISTRIBUTIONS INTO
2690 -- PO_DISTRIBUTIONS_ALL OR PO_DISTRIBUTIONS_DRAFT_ALL BASED ON THE DRAFT ID. THEN UPDATES THE
2691 -- VALUES, LIKE AWARD, CALIBRATE QUANTITY/AMOUNT, CALCULATE LOCAL
2692 -- AND DEFAULTS ACRN VALUES.
2693 --PARAMETERS:
2694 --IN:
2695 --X_RESULTS
2696 -- HOLDS THE VALIDATION MESSAGES
2697 --OUT:
2698 --X_RESULT_TYPE
2699 -- RETURNS THE SUCCESS FAILURE STATUS
2700 --TESTING:
2701 -- NONE.
2702 --END OF COMMENTS
2703
2704 PROCEDURE CREATE_AND_VAL_LINKED_REQ_DIST
2705 (
2706 -- STANDARD API PARAMS
2707 X_RESULT_TYPE OUT NOCOPY VARCHAR2,
2708 X_RESULTS IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
2709
2710 )
2711 IS
2712 D_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_AND_VAL_LINKED_REQ_DIST';
2713 D_MODULE CONSTANT VARCHAR2(2000) := D_PKG_NAME || D_API_NAME || '.';
2714 D_POSITION NUMBER;
2715 L_REQ_LINE_ID_TBL PO_TBL_NUMBER;
2716 L_REQ_HEADER_ID_TBL PO_TBL_NUMBER;
2717
2718 L_RESULT_TYPE VARCHAR2(20);
2719
2720 BEGIN
2721
2722 D_POSITION := 0;
2723 IF (PO_LOG.D_PROC) THEN
2724 PO_LOG.PROC_BEGIN(D_MODULE,'1',1);
2725 END IF;
2726
2727 D_POSITION := 10;
2728 DEBUG(D_MODULE, D_POSITION, 'CALLING VALIDATE_LINKED_DISTRIBUTIONS');
2729
2730 VALIDATE_LINKED_DISTRIBUTIONS(
2731 X_RESULTS => X_RESULTS
2732 );
2733
2734 D_POSITION := 20;
2735 DEBUG(D_MODULE, D_POSITION, 'VALIDATE_LINKED_DISTRIBUTIONS COMPLETED');
2736
2737 IF X_RESULTS.RESULT_TYPE.COUNT > 0 THEN
2738 X_RESULT_TYPE := C_RESULT_TYPE_FAILURE;
2739
2740 D_POSITION := 30;
2741 DEBUG(D_MODULE, D_POSITION, 'VALIDATION FAILED');
2742
2743 ELSIF X_RESULTS.RESULT_TYPE.COUNT = 0 THEN
2744 X_RESULT_TYPE := C_RESULT_TYPE_SUCCESS;
2745
2746 D_POSITION := 40;
2747 DEBUG(D_MODULE, D_POSITION, 'VALIDATION SUCCESSFULL. MERGE STARTING');
2748
2749
2750 MERGE_LINKREQDIST_TO_TXN_TBL;
2751
2752 MERGE_DIST_PARENTS_TO_DRAFT; -- Merge untouched parent entities into Draft
2753
2754 D_POSITION := 50;
2755 DEBUG(D_MODULE, D_POSITION, 'MERGE SUCCESFULL. DELETING ALL GT TABLES,
2756 FOR SELECTED SCHEDULES, REQUISITION AND NEW DISTRIBUTIONS FROM
2757 PO_SESSION_GT AND PO_DISTRIBUTIONS_GT');
2758
2759 SELECT DISTINCT reqLine.REQUISITION_HEADER_ID, reqLine.REQUISITION_LINE_ID
2760 BULK COLLECT INTO L_REQ_HEADER_ID_TBL, L_REQ_LINE_ID_TBL
2761 FROM PO_DISTRIBUTIONS_GT GT, po_req_distributions_all reqDist, po_requisition_lines_all reqLine
2762 WHERE GT.REQ_DISTRIBUTION_ID = reqDist.DISTRIBUTION_ID AND
2763 reqDist.requisition_line_id = reqLine.requisition_line_id;
2764
2765 D_POSITION := 55;
2766 DEBUG(D_MODULE, D_POSITION, 'Update the requisition line to set the req pool flag to null');
2767
2768 REMOVE_FROM_REQ_POOL(
2769 p_requisition_header_id => L_REQ_HEADER_ID_TBL,
2770 p_requisition_line_id => L_REQ_LINE_ID_TBL,
2771 x_return_status => X_RESULT_TYPE,
2772 x_error_msg => X_RESULT_TYPE
2773 ) ;
2774
2775 D_POSITION := 55;
2776 DEBUG(D_MODULE, D_POSITION, 'Update the requisition line to line id, line location id, draft if and linked po cound');
2777
2778 -- Bug: 13948625, Changed the logic to uopdated linked_po_count- distribution level
2779 -- Added reqDist.info_line_id IS NOT NULL for Info Funded lines,it will not update for PricedLine which
2780 -- has info funded slins for D.O.D
2781 FOR req_cur IN (
2782 SELECT Nvl(reqDist.info_line_id,REQUISITION_LINE_ID) REQ_ID
2783 FROM PO_DISTRIBUTIONS_GT GT, po_req_distributions_all reqDist
2784 WHERE GT.REQ_DISTRIBUTION_ID = reqDist.DISTRIBUTION_ID
2785
2786 ) LOOP
2787 UPDATE po_requisition_lines_all
2788 SET PO_LINE_ID = -1,
2789 LINE_LOCATION_ID = -1,
2790 PO_DRAFT_ID = NULL,
2791 LINKED_PO_COUNT = Nvl(LINKED_PO_COUNT,0) + 1
2792 WHERE REQUISITION_LINE_ID = req_cur.REQ_ID;
2793
2794 END LOOP;
2795
2796 D_POSITION := 58;
2797 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Updated no. of rows: '|| sql%rowcount);
2798
2799 DELETE PO_DISTRIBUTIONS_GT;
2800 DELETE PO_SESSION_GT WHERE INDEX_CHAR1 = 'LINKREQDIST_REQDISTID';
2801 DELETE PO_SESSION_GT WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
2802
2803 D_POSITION := 59;
2804 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Session data deleted');
2805
2806 X_RESULT_TYPE := C_RESULT_TYPE_SUCCESS;
2807
2808 END IF;
2809
2810 D_POSITION := 60;
2811 IF (PO_LOG.D_PROC) THEN
2812 PO_LOG.PROC_END(D_MODULE);
2813 END IF;
2814
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817
2818 X_RESULT_TYPE := C_RESULT_TYPE_FAILURE;
2819
2820 D_POSITION := 70;
2821 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Unexpected error occured');
2822 DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' sqlerrm: '||sqlerrm);
2823
2824 RAISE; --raise exception
2825
2826
2827 END CREATE_AND_VAL_LINKED_REQ_DIST;
2828
2829
2830 END PO_LINK_REQ_DIST_PVT;