[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRACCR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRACCR_XMLP_PKG AS
2 /* $Header: CSTRACCRB.pls 120.1.12010000.3 2008/11/14 13:44:43 smsasidh ship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 /*SRW.MESSAGE(500
7 ,'BOM_CSTRACCR_XMLP_PKG >> ' || TO_CHAR(SYSDATE
8 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
9 RETURN (TRUE);
10 END AFTERREPORT;
11
12 FUNCTION AFTERPFORM RETURN BOOLEAN IS
13 BEGIN
14 LP_AGE_DAYS:=nvl(P_AGE_DAYS,0);
15 qty_precision:=bom_common_xmlp_pkg.get_precision(P_qty_precision);
16 LP_WRITTEN_OFF:=nvl(P_WRITTEN_OFF,'N');
17 LP_CHART_OF_ACCOUNTS_ID:=nvl(P_CHART_OF_ACCOUNTS_ID,101);
18 dummy1:=P_PERIODVALIDTRIGGER;
19
20 dummy2:=P_COST_GROUPVALIDTRIGGER;
21
22 dummy3:=P_COST_TYPEVALIDTRIGGER;
23
24 dummy4:=P_LEGAL_ENTITYVALIDTRIGGER;
25 RETURN (TRUE);
26 END AFTERPFORM;
27
28 PROCEDURE DELETE_TABLE IS
29 BEGIN
30 /*SRW.MESSAGE(101
31 ,'delete_table() << ' || TO_CHAR(SYSDATE
32 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
33 LOCK TABLE CST_PAC_ACCRUAL_RECONCILE_TEMP IN EXCLUSIVE MODE NOWAIT;
34 DELETE FROM CST_PAC_ACCRUAL_RECONCILE_TEMP
35 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY
36 AND COST_TYPE_ID = P_COST_TYPE
37 AND COST_GROUP_ID = P_COST_GROUP;
38 DELETE FROM CST_PAC_ACCRUAL_ACCOUNTS_TEMP
39 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY;
40 /*SRW.MESSAGE(101
41 ,'delete_table() >> ' || TO_CHAR(SYSDATE
42 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
43 EXCEPTION
44 WHEN OTHERS THEN
45 /*SRW.MESSAGE(101
46 ,SQLERRM)*/NULL;
47 /*SRW.MESSAGE(101
48 ,'delete_table() >X ' || TO_CHAR(SYSDATE
49 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
50 RAISE;
51 END DELETE_TABLE;
52
53 FUNCTION NET_ACCRUAL_BALFORMULA(T_AMOUNT IN NUMBER
54 ,IPV IN NUMBER) RETURN NUMBER IS
55 BEGIN
56 RETURN (T_AMOUNT + NVL(IPV
57 ,0));
58 END NET_ACCRUAL_BALFORMULA;
59
60 FUNCTION SORT_COLUMNFORMULA(ITEM_RAW IN VARCHAR2
61 ,ITEM_SEG IN VARCHAR2
62 ,VENDOR_NAME IN VARCHAR2
63 ,ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
64 BEGIN
65 /*SRW.REFERENCE(ITEM_RAW)*/NULL;
66 /*SRW.REFERENCE(ITEM_SEG)*/NULL;
67 /*SRW.REFERENCE(VENDOR_NAME)*/NULL;
68 IF (P_SORT_OPTION = 'VENDOR') THEN
69 RETURN (VENDOR_NAME);
70 ELSE
71 RETURN (ITEM_PSEG);
72 END IF;
73 RETURN NULL;
74 END SORT_COLUMNFORMULA;
75
76 FUNCTION ITEM_PSEGFORMULA(ITEM_RAW IN VARCHAR2
77 ,ITEM_SEG IN VARCHAR2
78 ,ITEM_ID IN NUMBER
79 ,ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
80 BEGIN
81 /*SRW.REFERENCE(ITEM_RAW)*/NULL;
82 /*SRW.REFERENCE(ITEM_SEG)*/NULL;
83 IF ITEM_ID IS NULL THEN
84 RETURN (NULL);
85 ELSE
86 RETURN (ITEM_PSEG);
87 END IF;
88 RETURN NULL;
89 END ITEM_PSEGFORMULA;
90
91 FUNCTION ITEM_VENDORFORMULA(ITEM_RAW IN VARCHAR2
92 ,ITEM_SEG IN VARCHAR2
93 ,VENDOR_NAME IN VARCHAR2) RETURN VARCHAR2 IS
94 BEGIN
95 /*SRW.REFERENCE(ITEM_RAW)*/NULL;
96 /*SRW.REFERENCE(ITEM_SEG)*/NULL;
97 /*SRW.REFERENCE(VENDOR_NAME)*/NULL;
98 IF (P_SORT_OPTION = 'ITEM') THEN
99 RETURN (VENDOR_NAME);
100 ELSE
101 RETURN (ITEM_SEG);
102 END IF;
103 RETURN NULL;
104 END ITEM_VENDORFORMULA;
105
106 FUNCTION ACCT_PSEGFORMULA(ACCT_RAW IN VARCHAR2
107 ,ACCT_SEG IN VARCHAR2
108 ,ACCT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
109 BEGIN
110 /*SRW.REFERENCE(ACCT_RAW)*/NULL;
111 /*SRW.REFERENCE(ACCT_SEG)*/NULL;
112 RETURN (ACCT_PSEG);
113 END ACCT_PSEGFORMULA;
114
115 PROCEDURE INSERT_AP_DATA IS
116 l_disp_field VARCHAR2(80);
117 BEGIN
118 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
119 (REQUEST_ID
120 ,PROGRAM_APPLICATION_ID
121 ,PROGRAM_ID
122 ,PROGRAM_UPDATE_DATE
123 ,PERIOD_NAME
124 ,TRANSACTION_ORGANIZATION_ID
125 ,ITEM_MASTER_ORGANIZATION_ID
126 ,ACCRUAL_ACCOUNT_ID
127 ,ACCRUAL_CODE
128 ,PO_TRANSACTION_TYPE
129 ,TRANSACTION_DATE
130 ,INVOICE_NUM
131 ,RECEIPT_NUM
132 ,PO_TRANSACTION_ID
133 ,INV_TRANSACTION_ID
134 ,INV_TRANSACTION_TYPE_ID
135 ,WIP_TRANSACTION_ID
136 ,WIP_TRANSACTION_TYPE_ID
137 ,INVENTORY_ITEM_ID
138 ,PO_UNIT_OF_MEASURE
139 ,PRIMARY_UNIT_OF_MEASURE
140 ,TRANSACTION_QUANTITY
141 ,NET_PO_LINE_QUANTITY
142 ,PO_HEADER_ID
143 ,PO_NUM
144 ,PO_LINE_NUM
145 ,PO_LINE_ID
146 ,PO_DISTRIBUTION_ID
147 ,VENDOR_ID
148 ,VENDOR_NAME
149 ,VENDOR_NAME_ALT
150 ,TRANSACTION_UNIT_PRICE
151 ,INVOICE_ID
152 ,INVOICE_LINE_NUM
153 ,AVG_RECEIPT_PRICE
154 ,TRANSACTION_AMOUNT
155 ,LINE_MATCH_ORDER
156 ,TRANSACTION_SOURCE_CODE
157 ,WRITE_OFF_FLAG
158 ,WRITE_OFF_ID
159 ,DESTINATION_TYPE_CODE
160 ,REASON_ID
161 ,COMMENTS
162 ,LINE_LOCATION_ID
163 ,COST_TYPE_ID
164 ,COST_GROUP_ID
165 ,LEGAL_ENTITY_ID
166 ,PERIOD_ID)
167 SELECT /*+ LEADING(xah) */ -- rgangara for perf bug 7563374
168 P_CONC_REQUEST_ID,
169 P_APPL_ID,
170 P_PROGRAM_ID,
171 sysdate,
172 NULL,
173 P_ORG_ID,
174 P_MASTER_ORG_ID,
175 XAL.CODE_COMBINATION_ID,
176 'AP: not yet processed',
177 NULL,
178 XAH.ACCOUNTING_DATE,
179 API.INVOICE_NUM,
180 NULL,
181 NULL,
182 NULL,
183 NULL,
184 NULL,
185 NULL,
186 POL.ITEM_ID,
187 POL.UNIT_MEAS_LOOKUP_CODE,
188 POL.UNIT_MEAS_LOOKUP_CODE,
189 NVL(AID.QUANTITY_INVOICED
190 ,0),
191 NULL,
192 POH.PO_HEADER_ID,
193 POH.SEGMENT1,
194 POL.LINE_NUM,
195 POL.PO_LINE_ID,
196 POD.PO_DISTRIBUTION_ID,
197 API.VENDOR_ID,
198 POV.VENDOR_NAME,
199 POV.VENDOR_NAME_ALT,
200 ROUND(DECODE(API.EXCHANGE_RATE
201 ,NULL
202 ,NVL(((NVL(XAL.ACCOUNTED_DR
203 ,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
204 ,1))
205 ,0)
206 ,NVL(((NVL(XAL.ACCOUNTED_DR
207 ,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
208 ,1))
209 ,0) * API.EXCHANGE_RATE)
210 ,P_EXT_PREC),
211 AID.INVOICE_ID,
212 AID.DISTRIBUTION_LINE_NUMBER,
213 NULL,
214 ROUND(DECODE(API.INVOICE_CURRENCY_CODE
215 ,P_CURRENCY_CODE
216 ,(NVL(XAL.ENTERED_DR
217 ,(-1) * XAL.ENTERED_CR))
218 ,(NVL(XAL.ACCOUNTED_DR
219 ,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT,
220 NULL,
221 'AP',
222 'N',
223 NULL,
224 POD.DESTINATION_TYPE_CODE,
225 NULL,
226 NULL,
227 POD.LINE_LOCATION_ID,
228 P_COST_TYPE,
229 P_COST_GROUP,
230 P_LEGAL_ENTITY,
231 P_PERIOD
232 FROM
233 AP_INVOICES_ALL API,
234 AP_INVOICE_DISTRIBUTIONS_ALL AID,
235 PO_VENDORS POV,
236 PO_DISTRIBUTIONS_ALL POD,
237 PO_LINE_LOCATIONS_ALL POLL,
238 PO_LINES_ALL POL,
239 PO_HEADERS_ALL POH,
240 XLA_AE_HEADERS XAH,
241 XLA_AE_LINES XAL,
242 XLA_DISTRIBUTION_LINKS XDL,
243 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
244 WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
245 AND XAH.APPLICATION_ID = 200
246 AND XAL.APPLICATION_ID = 200
247 AND XDL.APPLICATION_ID = 200
248 AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
249 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
250 AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO -- Bug 7563374 removed NVL as dates cannot be NULL
251 AND XAH.GL_TRANSFER_STATUS_CODE = 'Y' -- Bug 7563374 Changed the condition
252 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
253 AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
254 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
255 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
256 AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
257 AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
258 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
259 AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
260 AND POL.PO_LINE_ID = POD.PO_LINE_ID
261 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
262 AND POV.VENDOR_ID = POH.VENDOR_ID
263 AND API.INVOICE_ID = AID.INVOICE_ID
264 AND AID.PO_DISTRIBUTION_ID IS NOT NULL
265 AND AID.RCV_TRANSACTION_ID IS NULL
266 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM ,POV.VENDOR_NAME) AND NVL(P_VENDOR_TO ,POV.VENDOR_NAME)
267 AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
268
269
270 /* Added for Perf Bug 7563374 rgangara */
271 SELECT displayed_field
272 INTO l_disp_field
273 FROM PO_LOOKUP_CODES PLU
274 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
275 AND LOOKUP_CODE = 'AP INVOICE PRICE VAR';
276
277 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
278 (REQUEST_ID
279 ,PROGRAM_APPLICATION_ID
280 ,PROGRAM_ID
281 ,PROGRAM_UPDATE_DATE
282 ,PERIOD_NAME
283 ,TRANSACTION_ORGANIZATION_ID
284 ,ITEM_MASTER_ORGANIZATION_ID
285 ,ACCRUAL_ACCOUNT_ID
286 ,ACCRUAL_CODE
287 ,PO_TRANSACTION_TYPE
288 ,TRANSACTION_DATE
289 ,INVOICE_NUM
290 ,RECEIPT_NUM
291 ,PO_TRANSACTION_ID
292 ,INV_TRANSACTION_ID
293 ,INV_TRANSACTION_TYPE_ID
294 ,WIP_TRANSACTION_ID
295 ,WIP_TRANSACTION_TYPE_ID
296 ,INVENTORY_ITEM_ID
297 ,PO_UNIT_OF_MEASURE
298 ,PRIMARY_UNIT_OF_MEASURE
299 ,TRANSACTION_QUANTITY
300 ,NET_PO_LINE_QUANTITY
301 ,PO_HEADER_ID
302 ,PO_NUM
303 ,PO_LINE_NUM
304 ,PO_LINE_ID
305 ,PO_DISTRIBUTION_ID
306 ,VENDOR_ID
307 ,VENDOR_NAME
308 ,VENDOR_NAME_ALT
309 ,TRANSACTION_UNIT_PRICE
310 ,INVOICE_ID
311 ,INVOICE_LINE_NUM
312 ,AVG_RECEIPT_PRICE
313 ,TRANSACTION_AMOUNT
314 ,INVOICE_PRICE_VARIANCE
315 ,LINE_MATCH_ORDER
316 ,TRANSACTION_SOURCE_CODE
317 ,WRITE_OFF_FLAG
318 ,WRITE_OFF_ID
319 ,DESTINATION_TYPE_CODE
320 ,REASON_ID
321 ,COMMENTS
322 ,LINE_LOCATION_ID
323 ,COST_TYPE_ID
324 ,COST_GROUP_ID
325 ,LEGAL_ENTITY_ID
326 ,PERIOD_ID)
327 SELECT /*+ LEADING(xah) */
328 P_CONC_REQUEST_ID,
329 P_APPL_ID,
330 P_PROGRAM_ID,
331 sysdate,
332 NULL,
333 P_ORG_ID,
334 P_MASTER_ORG_ID,
335 XAL.CODE_COMBINATION_ID,
336 l_disp_field, --PLU.DISPLAYED_FIELD -- changed for perf Bug 7563374 rgangara
337 NULL,
338 XAH.ACCOUNTING_DATE,
339 API.INVOICE_NUM,
340 NULL,
341 NULL,
342 NULL,
343 NULL,
344 NULL,
345 NULL,
346 POL.ITEM_ID,
347 POL.UNIT_MEAS_LOOKUP_CODE,
348 POL.UNIT_MEAS_LOOKUP_CODE,
349 0,
350 NULL,
351 POH.PO_HEADER_ID,
352 POH.SEGMENT1,
353 POL.LINE_NUM,
354 POL.PO_LINE_ID,
355 POD.PO_DISTRIBUTION_ID,
356 API.VENDOR_ID,
357 POV.VENDOR_NAME,
358 POV.VENDOR_NAME_ALT,
359 0,
360 AID.INVOICE_ID,
361 AID.DISTRIBUTION_LINE_NUMBER,
362 NULL,
363 ROUND(DECODE(API.INVOICE_CURRENCY_CODE
364 ,P_CURRENCY_CODE
365 ,(NVL(XAL.ENTERED_DR
366 ,(-1) * XAL.ENTERED_CR))
367 ,(NVL(XAL.ACCOUNTED_DR
368 ,(-1) * XAL.ACCOUNTED_DR))) / ROUND_UNIT) * ROUND_UNIT,
369 ROUND(DECODE(API.INVOICE_CURRENCY_CODE
370 ,P_CURRENCY_CODE
371 ,(NVL(XAL.ENTERED_DR
372 ,(-1) * XAL.ENTERED_CR))
373 ,(NVL(XAL.ACCOUNTED_DR
374 ,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT * ( - 1 ),
375 NULL,
376 'AP',
377 'N',
378 NULL,
379 POD.DESTINATION_TYPE_CODE,
380 NULL,
381 NULL,
382 POD.LINE_LOCATION_ID,
383 P_COST_TYPE,
384 P_COST_GROUP,
385 P_LEGAL_ENTITY,
386 P_PERIOD
387 FROM
388 AP_INVOICES_ALL API,
389 AP_INVOICE_DISTRIBUTIONS_ALL AID,
390 PO_VENDORS POV,
391 PO_DISTRIBUTIONS_ALL POD,
392 PO_LINE_LOCATIONS_ALL POLL,
393 PO_LINES_ALL POL,
394 PO_HEADERS_ALL POH,
395 XLA_AE_HEADERS XAH,
396 XLA_AE_LINES XAL,
397 XLA_DISTRIBUTION_LINKS XDL,
398 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
399 WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
400 AND XAH.APPLICATION_ID = 200
401 AND XAL.APPLICATION_ID = 200
402 AND XDL.APPLICATION_ID = 200
403 AND XAL.ACCOUNTING_CLASS_CODE = 'IPV'
404 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
405 AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
406 AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
407 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
408 AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
409 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
410 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
411 AND XAH.EVENT_ID = AID.ACCOUNTING_EVENT_ID
412 AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
413 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
414 AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
415 AND POL.PO_LINE_ID = POD.PO_LINE_ID
416 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
417 AND POV.VENDOR_ID = POH.VENDOR_ID
418 AND API.INVOICE_ID = AID.INVOICE_ID
419 AND AID.PO_DISTRIBUTION_ID IS NOT NULL
420 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM ,POV.VENDOR_NAME) AND NVL(P_VENDOR_TO ,POV.VENDOR_NAME)
421 AND EXISTS (
422 SELECT
423 'X'
424 FROM
425 AP_INVOICE_DISTRIBUTIONS_ALL AIDA
426 WHERE AIDA.RELATED_ID = AID.INVOICE_DISTRIBUTION_ID
427 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'IPV'
428 AND ( ( AIDA.BASE_AMOUNT IS NOT NULL
429 OR AIDA.BASE_AMOUNT <> 0 )
430 OR ( AIDA.AMOUNT IS NOT NULL
431 OR AIDA.AMOUNT <> 0 ) ) )
432 AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID
433 /*Support for LCM*/
434 AND NVL(POLL.LCM_FLAG,'N') = 'N';
435
436
437 /* Added for Perf bug 7563374 rgangara */
438 SELECT displayed_field
439 INTO l_disp_field
440 FROM PO_LOOKUP_CODES PLU
441 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
442 AND PLU.LOOKUP_CODE = 'AP EXCHANGE RATE VAR';
443
444
445 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
446 (REQUEST_ID
447 ,PROGRAM_APPLICATION_ID
448 ,PROGRAM_ID
449 ,PROGRAM_UPDATE_DATE
450 ,PERIOD_NAME
451 ,TRANSACTION_ORGANIZATION_ID
452 ,ITEM_MASTER_ORGANIZATION_ID
453 ,ACCRUAL_ACCOUNT_ID
454 ,ACCRUAL_CODE
455 ,PO_TRANSACTION_TYPE
456 ,TRANSACTION_DATE
457 ,INVOICE_NUM
458 ,RECEIPT_NUM
459 ,PO_TRANSACTION_ID
460 ,INV_TRANSACTION_ID
461 ,INV_TRANSACTION_TYPE_ID
462 ,WIP_TRANSACTION_ID
463 ,WIP_TRANSACTION_TYPE_ID
464 ,INVENTORY_ITEM_ID
465 ,PO_UNIT_OF_MEASURE
466 ,PRIMARY_UNIT_OF_MEASURE
467 ,TRANSACTION_QUANTITY
468 ,NET_PO_LINE_QUANTITY
469 ,PO_HEADER_ID
470 ,PO_NUM
471 ,PO_LINE_NUM
472 ,PO_LINE_ID
473 ,PO_DISTRIBUTION_ID
474 ,VENDOR_ID
475 ,VENDOR_NAME
476 ,VENDOR_NAME_ALT
477 ,TRANSACTION_UNIT_PRICE
478 ,INVOICE_ID
479 ,INVOICE_LINE_NUM
480 ,AVG_RECEIPT_PRICE
481 ,TRANSACTION_AMOUNT
482 ,INVOICE_PRICE_VARIANCE
483 ,LINE_MATCH_ORDER
484 ,TRANSACTION_SOURCE_CODE
485 ,WRITE_OFF_FLAG
486 ,WRITE_OFF_ID
487 ,DESTINATION_TYPE_CODE
488 ,REASON_ID
489 ,COMMENTS
490 ,LINE_LOCATION_ID
491 ,COST_TYPE_ID
492 ,COST_GROUP_ID
493 ,LEGAL_ENTITY_ID
494 ,PERIOD_ID)
495 SELECT /*+ LEADING(xah) */
496 P_CONC_REQUEST_ID,
497 P_APPL_ID,
498 P_PROGRAM_ID,
499 sysdate,
500 NULL,
501 P_ORG_ID,
502 P_MASTER_ORG_ID,
503 XAL.CODE_COMBINATION_ID,
504 l_disp_field, --PLU.DISPLAYED_FIELD,
505 NULL,
506 XAH.ACCOUNTING_DATE,
507 API.INVOICE_NUM,
508 NULL,
509 NULL,
510 NULL,
511 NULL,
512 NULL,
513 NULL,
514 POL.ITEM_ID,
515 POL.UNIT_MEAS_LOOKUP_CODE,
516 POL.UNIT_MEAS_LOOKUP_CODE,
517 0,
518 NULL,
519 POH.PO_HEADER_ID,
520 POH.SEGMENT1,
521 POL.LINE_NUM,
522 POL.PO_LINE_ID,
523 POD.PO_DISTRIBUTION_ID,
524 API.VENDOR_ID,
525 POV.VENDOR_NAME,
526 POV.VENDOR_NAME_ALT,
527 0,
528 AID.INVOICE_ID,
529 AID.DISTRIBUTION_LINE_NUMBER,
530 NULL,
531 ROUND(NVL(XAL.ENTERED_DR
532 ,(-1) * XAL.ENTERED_CR) / ROUND_UNIT) * ROUND_UNIT,
533 ROUND(NVL(XAL.ENTERED_DR
534 ,(-1) * XAL.ENTERED_DR) / ROUND_UNIT) * ROUND_UNIT * ( - 1 ),
535 NULL,
536 'AP',
537 'N',
538 NULL,
539 POD.DESTINATION_TYPE_CODE,
540 NULL,
541 NULL,
542 POD.LINE_LOCATION_ID,
543 P_COST_TYPE,
544 P_COST_GROUP,
545 P_LEGAL_ENTITY,
546 P_PERIOD
547 FROM
548 AP_INVOICES_ALL API,
549 AP_INVOICE_DISTRIBUTIONS_ALL AID,
550 PO_VENDORS POV,
551 PO_DISTRIBUTIONS_ALL POD,
552 PO_LINE_LOCATIONS_ALL POLL,
553 PO_LINES_ALL POL,
554 PO_HEADERS_ALL POH,
555 XLA_AE_HEADERS XAH,
556 XLA_AE_LINES XAL,
557 XLA_DISTRIBUTION_LINKS XDL,
558 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
559 WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
560 AND XAH.APPLICATION_ID = 200
561 AND XAL.APPLICATION_ID = 200
562 AND XDL.APPLICATION_ID = 200
563 AND XAL.ACCOUNTING_CLASS_CODE = 'ERV'
564 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
565 AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
566 AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
567 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
568 AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
569 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
570 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
571 AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
572 AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
573 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
574 AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
575 AND POL.PO_LINE_ID = POD.PO_LINE_ID
576 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
577 AND POH.ORG_ID = P_OPERATING_UNIT
578 AND POV.VENDOR_ID = POH.VENDOR_ID
579 AND API.INVOICE_ID = AID.INVOICE_ID
580 AND AID.PO_DISTRIBUTION_ID IS NOT NULL
581 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
582 ,POV.VENDOR_NAME)
583 AND NVL(P_VENDOR_TO
584 ,POV.VENDOR_NAME)
585 AND EXISTS (
586 SELECT
587 'X'
588 FROM
589 AP_INVOICE_DISTRIBUTIONS_ALL AIDA
590 WHERE AIDA.RELATED_ID = AID.INVOICE_DISTRIBUTION_ID
591 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ERV'
592 AND ( AIDA.AMOUNT IS NOT NULL
593 OR AIDA.AMOUNT <> 0 ) )
594 AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID
595 /*Support for LCM*/
596 AND NVL(POLL.LCM_FLAG,'N') = 'N';
597 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
598 (REQUEST_ID
599 ,PROGRAM_APPLICATION_ID
600 ,PROGRAM_ID
601 ,PROGRAM_UPDATE_DATE
602 ,PERIOD_NAME
603 ,TRANSACTION_ORGANIZATION_ID
604 ,ITEM_MASTER_ORGANIZATION_ID
605 ,ACCRUAL_ACCOUNT_ID
606 ,ACCRUAL_CODE
607 ,PO_TRANSACTION_TYPE
608 ,TRANSACTION_DATE
609 ,INVOICE_NUM
610 ,RECEIPT_NUM
611 ,PO_TRANSACTION_ID
612 ,INV_TRANSACTION_ID
613 ,INV_TRANSACTION_TYPE_ID
614 ,WIP_TRANSACTION_ID
615 ,WIP_TRANSACTION_TYPE_ID
616 ,INVENTORY_ITEM_ID
617 ,PO_UNIT_OF_MEASURE
618 ,PRIMARY_UNIT_OF_MEASURE
619 ,TRANSACTION_QUANTITY
620 ,NET_PO_LINE_QUANTITY
621 ,PO_HEADER_ID
622 ,PO_NUM
623 ,PO_LINE_NUM
624 ,PO_LINE_ID
625 ,PO_DISTRIBUTION_ID
626 ,VENDOR_ID
627 ,VENDOR_NAME
628 ,VENDOR_NAME_ALT
629 ,TRANSACTION_UNIT_PRICE
630 ,INVOICE_ID
631 ,INVOICE_LINE_NUM
632 ,AVG_RECEIPT_PRICE
633 ,TRANSACTION_AMOUNT
634 ,LINE_MATCH_ORDER
635 ,TRANSACTION_SOURCE_CODE
636 ,WRITE_OFF_FLAG
637 ,WRITE_OFF_ID
638 ,DESTINATION_TYPE_CODE
639 ,REASON_ID
640 ,COMMENTS
641 ,LINE_LOCATION_ID
642 ,COST_TYPE_ID
643 ,COST_GROUP_ID
644 ,LEGAL_ENTITY_ID
645 ,PERIOD_ID)
646 SELECT /*+ LEADING(xah) */
647 P_CONC_REQUEST_ID,
648 P_APPL_ID,
649 P_PROGRAM_ID,
650 sysdate,
651 NULL,
652 P_ORG_ID,
653 P_MASTER_ORG_ID,
654 XAL.CODE_COMBINATION_ID,
655 'AP: not yet processed',
656 NULL,
657 XAH.ACCOUNTING_DATE,
658 API.INVOICE_NUM,
659 RSH.RECEIPT_NUM,
660 NULL,
661 NULL,
662 NULL,
663 NULL,
664 NULL,
665 POL.ITEM_ID,
666 POL.UNIT_MEAS_LOOKUP_CODE,
667 POL.UNIT_MEAS_LOOKUP_CODE,
668 NVL(AID.QUANTITY_INVOICED
669 ,0),
670 NULL,
671 POH.PO_HEADER_ID,
672 POH.SEGMENT1,
673 POL.LINE_NUM,
674 POL.PO_LINE_ID,
675 RT.PO_DISTRIBUTION_ID,
676 API.VENDOR_ID,
677 POV.VENDOR_NAME,
678 POV.VENDOR_NAME_ALT,
679 ROUND(DECODE(API.EXCHANGE_RATE
680 ,NULL
681 ,NVL(((NVL(XAL.ACCOUNTED_DR
682 ,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
683 ,1))
684 ,0)
685 ,NVL(((NVL(XAL.ACCOUNTED_DR
686 ,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
687 ,1))
688 ,0) * API.EXCHANGE_RATE)
689 ,P_EXT_PREC),
690 AID.INVOICE_ID,
691 AID.DISTRIBUTION_LINE_NUMBER,
692 NULL,
693 ROUND(DECODE(API.INVOICE_CURRENCY_CODE
694 ,P_CURRENCY_CODE
695 ,(NVL(XAL.ENTERED_DR
696 ,(-1) * XAL.ENTERED_CR))
697 ,(NVL(XAL.ACCOUNTED_DR
698 ,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT,
699 NULL,
700 'AP',
701 'N',
702 NULL,
703 RT.DESTINATION_TYPE_CODE,
704 NULL,
705 NULL,
706 POLL.LINE_LOCATION_ID,
707 P_COST_TYPE,
708 P_COST_GROUP,
709 P_LEGAL_ENTITY,
710 P_PERIOD
711 FROM
712 AP_INVOICES_ALL API,
713 AP_INVOICE_DISTRIBUTIONS_ALL AID,
714 PO_VENDORS POV,
715 RCV_TRANSACTIONS RT,
716 RCV_SHIPMENT_HEADERS RSH,
717 PO_LINES_ALL POL,
718 PO_LINE_LOCATIONS_ALL POLL,
719 PO_HEADERS_ALL POH,
720 XLA_AE_HEADERS XAH,
721 XLA_AE_LINES XAL,
722 XLA_DISTRIBUTION_LINKS XDL,
723 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
724 WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
725 AND XAH.APPLICATION_ID = 200
726 AND XAL.APPLICATION_ID = 200
727 AND XDL.APPLICATION_ID = 200
728 AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
729 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
730 AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
731 AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
732 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
733 AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
734 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
735 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
736 AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
737 AND AID.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
738 AND NVL(RT.ORGANIZATION_ID
739 ,P_ORG_ID) = P_ORG_ID
740 AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
741 AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
742 AND POL.PO_LINE_ID = POLL.PO_LINE_ID
743 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
744 AND POV.VENDOR_ID = POH.VENDOR_ID
745 AND API.INVOICE_ID = AID.INVOICE_ID
746 AND AID.RCV_TRANSACTION_ID IS NOT NULL
747 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
748 ,POV.VENDOR_NAME)
749 AND NVL(P_VENDOR_TO
750 ,POV.VENDOR_NAME)
751 AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
752 /*SRW.MESSAGE(131
753 ,'insert_ap_data() >> ' || TO_CHAR(SYSDATE
754 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
755 EXCEPTION
756 WHEN OTHERS THEN
757 /*SRW.MESSAGE(131
758 ,SQLERRM)*/NULL;
759 /*SRW.MESSAGE(131
760 ,'insert_ap_data() >X ' || TO_CHAR(SYSDATE
761 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
762 RAISE;
763 END INSERT_AP_DATA;
764
765 PROCEDURE INSERT_AP_MISC IS
766 BEGIN
767 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
768 (REQUEST_ID
769 ,PROGRAM_APPLICATION_ID
770 ,PROGRAM_ID
771 ,PROGRAM_UPDATE_DATE
772 ,PERIOD_NAME
773 ,TRANSACTION_ORGANIZATION_ID
774 ,ITEM_MASTER_ORGANIZATION_ID
775 ,ACCRUAL_ACCOUNT_ID
776 ,ACCRUAL_CODE
777 ,PO_TRANSACTION_TYPE
778 ,TRANSACTION_DATE
779 ,INVOICE_NUM
780 ,RECEIPT_NUM
781 ,PO_TRANSACTION_ID
782 ,INV_TRANSACTION_ID
783 ,INV_TRANSACTION_TYPE_ID
784 ,WIP_TRANSACTION_ID
785 ,WIP_TRANSACTION_TYPE_ID
786 ,INVENTORY_ITEM_ID
787 ,PO_UNIT_OF_MEASURE
788 ,PRIMARY_UNIT_OF_MEASURE
789 ,TRANSACTION_QUANTITY
790 ,NET_PO_LINE_QUANTITY
791 ,PO_HEADER_ID
792 ,PO_NUM
793 ,PO_LINE_NUM
794 ,PO_LINE_ID
795 ,PO_DISTRIBUTION_ID
796 ,VENDOR_ID
797 ,VENDOR_NAME
798 ,VENDOR_NAME_ALT
799 ,TRANSACTION_UNIT_PRICE
800 ,INVOICE_ID
801 ,INVOICE_LINE_NUM
802 ,AVG_RECEIPT_PRICE
803 ,TRANSACTION_AMOUNT
804 ,LINE_MATCH_ORDER
805 ,TRANSACTION_SOURCE_CODE
806 ,WRITE_OFF_FLAG
807 ,WRITE_OFF_ID
808 ,DESTINATION_TYPE_CODE
809 ,REASON_ID
810 ,COMMENTS
811 ,LINE_LOCATION_ID
812 ,COST_TYPE_ID
813 ,COST_GROUP_ID
814 ,LEGAL_ENTITY_ID
815 ,PERIOD_ID)
816 SELECT /*+ LEADING(xah) */
817 P_CONC_REQUEST_ID,
818 P_APPL_ID,
819 P_PROGRAM_ID,
820 sysdate,
821 NULL,
822 P_MASTER_ORG_ID,
823 P_MASTER_ORG_ID,
824 XAL.CODE_COMBINATION_ID,
825 'AP: not yet processed',
826 NULL,
827 XAH.ACCOUNTING_DATE,
828 API.INVOICE_NUM,
829 NULL,
830 NULL,
831 NULL,
832 NULL,
833 NULL,
834 NULL,
835 NULL,
836 NULL,
837 NULL,
838 NVL(AID.QUANTITY_INVOICED
839 ,0),
840 NULL,
841 NULL,
842 NULL,
843 NULL,
844 NULL,
845 NULL,
846 API.VENDOR_ID,
847 POV.VENDOR_NAME,
848 POV.VENDOR_NAME_ALT,
849 ROUND(DECODE(API.EXCHANGE_RATE
850 ,NULL
851 ,NVL(AID.UNIT_PRICE
852 ,0)
853 ,NVL(AID.UNIT_PRICE
854 ,0) * API.EXCHANGE_RATE)
855 ,P_EXT_PREC),
856 AID.INVOICE_ID,
857 AID.DISTRIBUTION_LINE_NUMBER,
858 NULL,
859 ROUND(NVL((NVL(XAL.ACCOUNTED_DR
860 ,(-1) * XAL.ACCOUNTED_CR))
861 ,(NVL(XAL.ENTERED_DR
862 ,(-1) * XAL.ENTERED_CR))) / ROUND_UNIT) * ROUND_UNIT,
863 NULL,
864 'AP',
865 'N',
866 NULL,
867 NULL,
868 NULL,
869 NULL,
870 NULL,
871 P_COST_TYPE,
872 P_COST_GROUP,
873 P_LEGAL_ENTITY,
874 P_PERIOD
875 FROM
876 AP_INVOICES_ALL API,
877 AP_INVOICE_DISTRIBUTIONS_ALL AID,
878 XLA_AE_HEADERS XAH,
879 XLA_AE_LINES XAL,
880 XLA_DISTRIBUTION_LINKS XDL,
881 PO_VENDORS POV,
882 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
883 WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
884 AND XAH.APPLICATION_ID = 200
885 AND XAL.APPLICATION_ID = 200
886 AND XDL.APPLICATION_ID = 200
887 AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
888 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
889 AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
890 AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
891 AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
892 AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
893 AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
894 AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
895 AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
896 AND AID.PO_DISTRIBUTION_ID is NULL
897 AND AID.RCV_TRANSACTION_ID is NULL
898 AND API.INVOICE_ID = AID.INVOICE_ID
899 AND POV.VENDOR_ID = API.VENDOR_ID
900 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
901 ,POV.VENDOR_NAME)
902 AND NVL(P_VENDOR_TO
903 ,POV.VENDOR_NAME)
904 AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
905 /*SRW.MESSAGE(132
906 ,'insert_ap_misc() >> ' || TO_CHAR(SYSDATE
907 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
908 EXCEPTION
909 WHEN OTHERS THEN
910 /*SRW.MESSAGE(132
911 ,SQLERRM)*/NULL;
912 /*SRW.MESSAGE(132
913 ,'insert_ap_misc() >X ' || TO_CHAR(SYSDATE
914 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
915 RAISE;
916 END INSERT_AP_MISC;
917
918 PROCEDURE INSERT_PO_DATA IS
919 BEGIN
920 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
921 (REQUEST_ID
922 ,PROGRAM_APPLICATION_ID
923 ,PROGRAM_ID
924 ,PROGRAM_UPDATE_DATE
925 ,PERIOD_NAME
926 ,TRANSACTION_ORGANIZATION_ID
927 ,ITEM_MASTER_ORGANIZATION_ID
928 ,ACCRUAL_ACCOUNT_ID
929 ,ACCRUAL_CODE
930 ,PO_TRANSACTION_TYPE
931 ,TRANSACTION_DATE
932 ,INVOICE_NUM
933 ,RECEIPT_NUM
934 ,PO_TRANSACTION_ID
935 ,INV_TRANSACTION_ID
936 ,INV_TRANSACTION_TYPE_ID
937 ,WIP_TRANSACTION_ID
938 ,WIP_TRANSACTION_TYPE_ID
939 ,INVENTORY_ITEM_ID
940 ,PO_UNIT_OF_MEASURE
941 ,PRIMARY_UNIT_OF_MEASURE
942 ,TRANSACTION_QUANTITY
943 ,NET_PO_LINE_QUANTITY
944 ,PO_HEADER_ID
945 ,PO_NUM
946 ,PO_LINE_NUM
947 ,PO_LINE_ID
948 ,PO_DISTRIBUTION_ID
949 ,VENDOR_ID
950 ,VENDOR_NAME
951 ,VENDOR_NAME_ALT
952 ,TRANSACTION_UNIT_PRICE
953 ,INVOICE_ID
954 ,INVOICE_LINE_NUM
955 ,AVG_RECEIPT_PRICE
956 ,TRANSACTION_AMOUNT
957 ,LINE_MATCH_ORDER
958 ,TRANSACTION_SOURCE_CODE
959 ,WRITE_OFF_FLAG
960 ,WRITE_OFF_ID
961 ,DESTINATION_TYPE_CODE
962 ,REASON_ID
963 ,COMMENTS
964 ,LINE_LOCATION_ID
965 ,COST_TYPE_ID
966 ,COST_GROUP_ID
967 ,LEGAL_ENTITY_ID
968 ,PERIOD_ID)
969 SELECT
970 P_CONC_REQUEST_ID,
971 P_APPL_ID,
972 P_PROGRAM_ID,
973 sysdate,
974 NULL,
975 P_ORG_ID,
976 P_MASTER_ORG_ID,
977 CAL.CODE_COMBINATION_ID,
978 PLC.DISPLAYED_FIELD,
979 RCT.TRANSACTION_TYPE,
980 CAH.ACCOUNTING_DATE,
981 NULL,
982 RSH.RECEIPT_NUM,
983 RCT.TRANSACTION_ID,
984 NULL,
985 NULL,
986 NULL,
987 NULL,
988 POL.ITEM_ID,
989 POL.UNIT_MEAS_LOOKUP_CODE,
990 RCT.PRIMARY_UNIT_OF_MEASURE,
991 DECODE(RCT.PO_DISTRIBUTION_ID
992 ,NULL
993 ,(NVL(RCT.PRIMARY_QUANTITY
994 ,0) * (NVL(POD.QUANTITY_ORDERED
995 ,0) / NVL(POLL.QUANTITY
996 ,1)))
997 ,NVL(RCT.PRIMARY_QUANTITY
998 ,0)) * DECODE(CAL.ACCOUNTED_DR
999 ,NULL
1000 ,-1
1001 ,1),
1002 NULL,
1003 RCT.PO_HEADER_ID,
1004 POH.SEGMENT1,
1005 POL.LINE_NUM,
1006 POL.PO_LINE_ID,
1007 POD.PO_DISTRIBUTION_ID,
1008 POH.VENDOR_ID,
1009 POV.VENDOR_NAME,
1010 POV.VENDOR_NAME_ALT,
1011 ROUND(NVL(CAL.RATE_OR_AMOUNT
1012 ,1)
1013 ,P_EXT_PREC),
1014 NULL,
1015 NULL,
1016 NULL,
1017 ROUND((NVL(CAL.ACCOUNTED_DR
1018 ,0) - NVL(CAL.ACCOUNTED_CR
1019 ,0)) / ROUND_UNIT) * ROUND_UNIT,
1020 NULL,
1021 'PO',
1022 'N',
1023 NULL,
1024 POD.DESTINATION_TYPE_CODE,
1025 NULL,
1026 NULL,
1027 POD.LINE_LOCATION_ID,
1028 P_COST_TYPE,
1029 P_COST_GROUP,
1030 P_LEGAL_ENTITY,
1031 P_PERIOD
1032 FROM
1033 PO_LOOKUP_CODES PLC,
1034 RCV_SHIPMENT_HEADERS RSH,
1035 PO_VENDORS POV,
1036 PO_HEADERS_ALL POH,
1037 PO_LINES_ALL POL,
1038 PO_LINE_LOCATIONS_ALL POLL,
1039 PO_DISTRIBUTIONS_ALL POD,
1040 RCV_TRANSACTIONS RCT,
1041 CST_AE_HEADERS CAH,
1042 CST_AE_LINES CAL,
1043 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
1044 WHERE CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
1045 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
1046 AND CAH.GL_TRANSFER_FLAG = 'Y'
1047 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1048 AND CAH.COST_TYPE_ID = P_COST_TYPE
1049 AND CAH.COST_GROUP_ID = P_COST_GROUP
1050 AND RCT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
1051 AND RCT.SOURCE_DOCUMENT_CODE <> 'REQ'
1052 AND RCT.TRANSACTION_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
1053 AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
1054 AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
1055 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
1056 AND POD.PO_DISTRIBUTION_ID = CAL.PO_DISTRIBUTION_ID
1057 AND RCT.ORGANIZATION_ID = P_ORG_ID
1058 AND POD.ORG_ID = P_OPERATING_UNIT
1059 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
1060 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1061 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
1062 AND POV.VENDOR_ID = POH.VENDOR_ID
1063 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
1064 ,POV.VENDOR_NAME)
1065 AND NVL(P_VENDOR_TO
1066 ,POV.VENDOR_NAME)
1067 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
1068 /*Support for LCM*/
1069 AND nvl(cal.ae_line_type_code,'16') <> '38';
1070 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
1071 (REQUEST_ID
1072 ,PROGRAM_APPLICATION_ID
1073 ,PROGRAM_ID
1074 ,PROGRAM_UPDATE_DATE
1075 ,PERIOD_NAME
1076 ,TRANSACTION_ORGANIZATION_ID
1077 ,ITEM_MASTER_ORGANIZATION_ID
1078 ,ACCRUAL_ACCOUNT_ID
1079 ,ACCRUAL_CODE
1080 ,PO_TRANSACTION_TYPE
1081 ,ADJUSTMENT_TRANSACTION
1082 ,TRANSACTION_DATE
1083 ,INVOICE_NUM
1084 ,RECEIPT_NUM
1085 ,PO_TRANSACTION_ID
1086 ,INV_TRANSACTION_ID
1087 ,INV_TRANSACTION_TYPE_ID
1088 ,WIP_TRANSACTION_ID
1089 ,WIP_TRANSACTION_TYPE_ID
1090 ,INVENTORY_ITEM_ID
1091 ,PO_UNIT_OF_MEASURE
1092 ,PRIMARY_UNIT_OF_MEASURE
1093 ,TRANSACTION_QUANTITY
1094 ,NET_PO_LINE_QUANTITY
1095 ,PO_HEADER_ID
1096 ,PO_NUM
1097 ,PO_LINE_NUM
1098 ,PO_LINE_ID
1099 ,PO_DISTRIBUTION_ID
1100 ,VENDOR_ID
1101 ,VENDOR_NAME
1102 ,VENDOR_NAME_ALT
1103 ,TRANSACTION_UNIT_PRICE
1104 ,INVOICE_ID
1105 ,INVOICE_LINE_NUM
1106 ,AVG_RECEIPT_PRICE
1107 ,TRANSACTION_AMOUNT
1108 ,LINE_MATCH_ORDER
1109 ,TRANSACTION_SOURCE_CODE
1110 ,WRITE_OFF_FLAG
1111 ,WRITE_OFF_ID
1112 ,DESTINATION_TYPE_CODE
1113 ,REASON_ID
1114 ,COMMENTS
1115 ,LINE_LOCATION_ID
1116 ,COST_TYPE_ID
1117 ,COST_GROUP_ID
1118 ,LEGAL_ENTITY_ID
1119 ,PERIOD_ID)
1120 SELECT
1121 P_CONC_REQUEST_ID,
1122 P_APPL_ID,
1123 P_PROGRAM_ID,
1124 sysdate,
1125 NULL,
1126 P_ORG_ID,
1127 P_MASTER_ORG_ID,
1128 CAL.CODE_COMBINATION_ID,
1129 PLC.DISPLAYED_FIELD,
1130 RCT.TRANSACTION_TYPE,
1131 1,
1132 CAH.ACCOUNTING_DATE,
1133 NULL,
1134 RSH.RECEIPT_NUM,
1135 RCT.TRANSACTION_ID,
1136 NULL,
1137 NULL,
1138 NULL,
1139 NULL,
1140 POL.ITEM_ID,
1141 POL.UNIT_MEAS_LOOKUP_CODE,
1142 RCT.PRIMARY_UNIT_OF_MEASURE,
1143 NULL,
1144 NULL,
1145 RCT.PO_HEADER_ID,
1146 POH.SEGMENT1,
1147 POL.LINE_NUM,
1148 POL.PO_LINE_ID,
1149 POD.PO_DISTRIBUTION_ID,
1150 POH.VENDOR_ID,
1151 POV.VENDOR_NAME,
1152 POV.VENDOR_NAME_ALT,
1153 NULL,
1154 NULL,
1155 NULL,
1156 NULL,
1157 ROUND((NVL(CAL.ACCOUNTED_DR
1158 ,0) - NVL(CAL.ACCOUNTED_CR
1159 ,0)) / ROUND_UNIT) * ROUND_UNIT,
1160 NULL,
1161 'PO',
1162 'N',
1163 NULL,
1164 POD.DESTINATION_TYPE_CODE,
1165 NULL,
1166 NULL,
1167 POD.LINE_LOCATION_ID,
1168 P_COST_TYPE,
1169 P_COST_GROUP,
1170 P_LEGAL_ENTITY,
1171 P_PERIOD
1172 FROM
1173 PO_LOOKUP_CODES PLC,
1174 RCV_SHIPMENT_HEADERS RSH,
1175 PO_VENDORS POV,
1176 PO_HEADERS_ALL POH,
1177 PO_LINES_ALL POL,
1178 PO_LINE_LOCATIONS_ALL POLL,
1179 PO_DISTRIBUTIONS_ALL POD,
1180 RCV_TRANSACTIONS RCT,
1181 CST_AE_HEADERS CAH,
1182 CST_AE_LINES CAL,
1183 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA,
1184 RCV_ACCOUNTING_EVENTS RAE
1185 WHERE CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
1186 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
1187 AND CAH.GL_TRANSFER_FLAG = 'Y'
1188 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1189 AND CAH.COST_TYPE_ID = P_COST_TYPE
1190 AND CAH.COST_GROUP_ID = P_COST_GROUP
1191 AND RCT.SOURCE_DOCUMENT_CODE <> 'REQ'
1192 AND RAE.TRANSACTION_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
1193 AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
1194 AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
1195 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
1196 AND POD.PO_DISTRIBUTION_ID = CAL.PO_DISTRIBUTION_ID
1197 AND RCT.ORGANIZATION_ID = P_ORG_ID
1198 AND POD.ORG_ID = P_OPERATING_UNIT
1199 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
1200 AND POL.PO_LINE_ID = POD.PO_LINE_ID
1201 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
1202 AND POV.VENDOR_ID = POH.VENDOR_ID
1203 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
1204 ,POV.VENDOR_NAME)
1205 AND NVL(P_VENDOR_TO
1206 ,POV.VENDOR_NAME)
1207 AND RAE.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
1208 AND RAE.EVENT_TYPE_ID in ( 7 , 8 )
1209 AND RAE.ACCOUNTING_EVENT_ID = CAH.ACCOUNTING_EVENT_ID
1210 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
1211 /*SRW.MESSAGE(141
1212 ,'insert_po_data() >> ' || TO_CHAR(SYSDATE
1213 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 /*SRW.MESSAGE(141
1217 ,SQLERRM)*/NULL;
1218 /*SRW.MESSAGE(141
1219 ,'insert_po_data() >X ' || TO_CHAR(SYSDATE
1220 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1221 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
1222 END INSERT_PO_DATA;
1223
1224 PROCEDURE MATCH_INV_HEADER IS
1225 BEGIN
1226 UPDATE
1227 CST_PAC_ACCRUAL_RECONCILE_TEMP p1
1228 SET
1229 ACCRUAL_CODE = (SELECT
1230 DISPLAYED_FIELD
1231 FROM
1232 PO_LOOKUP_CODES
1233 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1234 AND LOOKUP_CODE = 'AP PO MATCH')
1235 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1236 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1237 AND P1.COST_TYPE_ID = P_COST_TYPE
1238 AND P1.COST_GROUP_ID = P_COST_GROUP
1239 AND EXISTS (
1240 SELECT
1241 null
1242 FROM
1243 CST_PAC_ACCRUAL_RECONCILE_TEMP P2
1244 WHERE P2.INVOICE_ID is null
1245 AND ( P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
1246 OR ( P2.INVENTORY_ITEM_ID is NULL
1247 AND P2.DESTINATION_TYPE_CODE = 'EXPENSE' ) )
1248 AND P2.PO_HEADER_ID = P1.PO_HEADER_ID
1249 AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
1250 AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
1251 AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1252 AND P2.COST_TYPE_ID = P_COST_TYPE
1253 AND P2.COST_GROUP_ID = P_COST_GROUP );
1254 /*SRW.MESSAGE(204
1255 ,'match_inv_header() >> ' || TO_CHAR(SYSDATE
1256 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 /*SRW.MESSAGE(204
1260 ,SQLERRM)*/NULL;
1261 /*SRW.MESSAGE(204
1262 ,'match_inv_header() >X ' || TO_CHAR(SYSDATE
1263 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1264 RAISE;
1265 END MATCH_INV_HEADER;
1266
1267 PROCEDURE MATCH_INV_ITEM IS
1268 BEGIN
1269 UPDATE
1270 CST_PAC_ACCRUAL_RECONCILE_TEMP p1
1271 SET
1272 ACCRUAL_CODE = (SELECT
1273 DISPLAYED_FIELD
1274 FROM
1275 PO_LOOKUP_CODES
1276 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1277 AND LOOKUP_CODE = 'AP ITEM MATCH')
1278 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1279 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1280 AND P1.COST_TYPE_ID = P_COST_TYPE
1281 AND P1.COST_GROUP_ID = P_COST_GROUP
1282 AND EXISTS (
1283 SELECT
1284 null
1285 FROM
1286 CST_PAC_ACCRUAL_RECONCILE_TEMP P2
1287 WHERE P2.INVOICE_ID is null
1288 AND P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
1289 AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
1290 AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
1291 AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1292 AND P2.COST_TYPE_ID = P_COST_TYPE
1293 AND P2.COST_GROUP_ID = P_COST_GROUP );
1294 /*SRW.MESSAGE(205
1295 ,'match_inv_item() >> ' || TO_CHAR(SYSDATE
1296 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 /*SRW.MESSAGE(205
1300 ,SQLERRM)*/NULL;
1301 /*SRW.MESSAGE(205
1302 ,'match_inv_item() >X ' || TO_CHAR(SYSDATE
1303 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1304 RAISE;
1305 END MATCH_INV_ITEM;
1306
1307 PROCEDURE MATCH_INV_LINE IS
1308 BEGIN
1309 UPDATE
1310 CST_PAC_ACCRUAL_RECONCILE_TEMP p1
1311 SET
1312 ACCRUAL_CODE = (SELECT
1313 DISPLAYED_FIELD
1314 FROM
1315 PO_LOOKUP_CODES
1316 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1317 AND LOOKUP_CODE = 'AP LINE MATCH')
1318 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1319 AND DESTINATION_TYPE_CODE in ( 'SHOP FLOOR' , 'INVENTORY' , 'EXPENSE' )
1320 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1321 AND P1.COST_TYPE_ID = P_COST_TYPE
1322 AND P1.COST_GROUP_ID = P_COST_GROUP
1323 AND EXISTS (
1324 SELECT
1325 NULL
1326 FROM
1327 CST_PAC_ACCRUAL_RECONCILE_TEMP P2
1328 WHERE P2.INVOICE_ID is null
1329 AND ( P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
1330 OR ( P1.INVENTORY_ITEM_ID IS NULL
1331 AND P1.DESTINATION_TYPE_CODE = 'EXPENSE' ) )
1332 AND P2.PO_HEADER_ID = P1.PO_HEADER_ID
1333 AND P2.PO_LINE_ID = P1.PO_LINE_ID
1334 AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
1335 AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
1336 AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1337 AND P2.COST_TYPE_ID = P_COST_TYPE
1338 AND P2.COST_GROUP_ID = P_COST_GROUP );
1339 /*SRW.MESSAGE(203
1340 ,'match_inv_line() >> ' || TO_CHAR(SYSDATE
1341 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 /*SRW.MESSAGE(203
1345 ,SQLERRM)*/NULL;
1346 /*SRW.MESSAGE(203
1347 ,'match_inv_line() >X ' || TO_CHAR(SYSDATE
1348 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1349 RAISE;
1350 END MATCH_INV_LINE;
1351
1352 PROCEDURE MATCH_ITEM IS
1353 BEGIN
1354 UPDATE
1355 CST_PAC_ACCRUAL_RECONCILE_TEMP p1
1356 SET
1357 ACCRUAL_CODE = (SELECT
1358 DISPLAYED_FIELD
1359 FROM
1360 PO_LOOKUP_CODES
1361 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1362 AND LOOKUP_CODE = 'AP NO MATCH')
1363 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1364 AND PO_HEADER_ID IS NOT NULL
1365 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1366 AND COST_TYPE_ID = P_COST_TYPE
1367 AND COST_GROUP_ID = P_COST_GROUP;
1368 /*SRW.MESSAGE(206
1369 ,'match_item() >> ' || TO_CHAR(SYSDATE
1370 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 COMMIT;
1374 /*SRW.MESSAGE(206
1375 ,SQLERRM)*/NULL;
1376 /*SRW.MESSAGE(206
1377 ,'match_item() >X ' || TO_CHAR(SYSDATE
1378 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1379 RAISE;
1380 END MATCH_ITEM;
1381
1382 PROCEDURE MATCH_NO_ITEM IS
1383 BEGIN
1384 UPDATE
1385 CST_PAC_ACCRUAL_RECONCILE_TEMP p1
1386 SET
1387 ACCRUAL_CODE = (SELECT
1388 DISPLAYED_FIELD
1389 FROM
1390 PO_LOOKUP_CODES
1391 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1392 AND LOOKUP_CODE = 'AP NO ITEM')
1393 ,INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
1394 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1395 AND P1.PO_HEADER_ID IS NOT NULL
1396 AND P1.INVENTORY_ITEM_ID IS NOT NULL
1397 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1398 AND COST_TYPE_ID = P_COST_TYPE
1399 AND COST_GROUP_ID = P_COST_GROUP
1400 AND NOT EXISTS (
1401 SELECT
1402 null
1403 FROM
1404 MTL_SYSTEM_ITEMS MSI
1405 WHERE MSI.ORGANIZATION_ID = P1.TRANSACTION_ORGANIZATION_ID
1406 AND MSI.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID );
1407 /*SRW.MESSAGE(201
1408 ,'match_no_item() >> ' || TO_CHAR(SYSDATE
1409 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412 /*SRW.MESSAGE(201
1413 ,SQLERRM)*/NULL;
1414 /*SRW.MESSAGE(201
1415 ,'match_no_item() >X ' || TO_CHAR(SYSDATE
1416 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1417 RAISE;
1418 END MATCH_NO_ITEM;
1419
1420 PROCEDURE MATCH_NO_PO IS
1421 BEGIN
1422 UPDATE
1423 CST_PAC_ACCRUAL_RECONCILE_TEMP
1424 SET
1425 ACCRUAL_CODE = (SELECT
1426 DISPLAYED_FIELD
1427 FROM
1428 PO_LOOKUP_CODES
1429 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
1430 AND LOOKUP_CODE = 'AP NO PO')
1431 ,INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
1432 WHERE ACCRUAL_CODE = 'AP: not yet processed'
1433 AND PO_HEADER_ID IS NULL
1434 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1435 AND COST_TYPE_ID = P_COST_TYPE
1436 AND COST_GROUP_ID = P_COST_GROUP;
1437 /*SRW.MESSAGE(202
1438 ,'match_no_po() >> ' || TO_CHAR(SYSDATE
1439 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 /*SRW.MESSAGE(202
1443 ,SQLERRM)*/NULL;
1444 /*SRW.MESSAGE(202
1445 ,'match_no_po() >X ' || TO_CHAR(SYSDATE
1446 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1447 RAISE;
1448 END MATCH_NO_PO;
1449
1450 PROCEDURE POPULATE_TEMP_TABLE IS
1451 BEGIN
1452 /*SRW.MESSAGE(001
1453 ,'populate_temp_table() << ' || TO_CHAR(SYSDATE
1454 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
1455 INSERT_MFG_DATA;
1456 INSERT_AP_DATA;
1457 INSERT_PO_DATA;
1458 /*SRW.MESSAGE(001
1459 ,'populate_temp_table() >> ' || TO_CHAR(SYSDATE
1460 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 /*SRW.MESSAGE(001
1464 ,'populate_temp_table() >X ' || TO_CHAR(SYSDATE
1465 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
1466 RAISE;
1467 END POPULATE_TEMP_TABLE;
1468
1469 PROCEDURE CALC_IPV IS
1470 BEGIN
1471 DECLARE
1472 L_LINE_ID NUMBER;
1473 L_ACCOUNT NUMBER;
1474 L_POL_QTY NUMBER;
1475 L_POL_AMT NUMBER;
1476 L_POL_AVG NUMBER;
1477 L_INV_ID NUMBER;
1478 L_IVL_ID NUMBER;
1479 L_APL_QTY NUMBER;
1480 L_APL_AMT NUMBER;
1481 L_APL_AVG NUMBER;
1482 L_IPV NUMBER;
1483 TXN_NUM NUMBER;
1484 CURSOR C_PO_LINE IS
1485 SELECT
1486 PO_LINE_ID,
1487 ACCRUAL_ACCOUNT_ID,
1488 SUM((-1) * TRANSACTION_QUANTITY),
1489 SUM((-1) * TRANSACTION_AMOUNT)
1490 FROM
1491 CST_PAC_ACCRUAL_RECONCILE_TEMP P1
1492 WHERE INVOICE_ID is null
1493 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1494 AND P1.COST_TYPE_ID = P_COST_TYPE
1495 AND P1.COST_GROUP_ID = P_COST_GROUP
1496 AND TRANSACTION_DATE between P_GL_DATE_FROM
1497 AND P_GL_DATE_TO
1498 AND exists (
1499 SELECT
1500 null
1501 FROM
1502 CST_PAC_ACCRUAL_RECONCILE_TEMP P2
1503 WHERE P2.PO_LINE_ID = P1.PO_LINE_ID
1504 AND P2.LINE_LOCATION_ID = P1.LINE_LOCATION_ID
1505 AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
1506 AND P2.INVOICE_ID is not null
1507 AND P2.TRANSACTION_AMOUNT <> P2.AVG_RECEIPT_PRICE * P2.TRANSACTION_QUANTITY
1508 AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1509 AND P2.COST_TYPE_ID = P_COST_TYPE
1510 AND P2.COST_GROUP_ID = P_COST_GROUP
1511 AND TRANSACTION_DATE between P_GL_DATE_FROM
1512 AND P_GL_DATE_TO )
1513 GROUP BY
1514 PO_LINE_ID,
1515 ACCRUAL_ACCOUNT_ID;
1516 CURSOR C_AP_LINE IS
1517 SELECT
1518 INVOICE_ID,
1519 INVOICE_LINE_NUM,
1520 TRANSACTION_QUANTITY,
1521 TRANSACTION_AMOUNT,
1522 TRANSACTION_AMOUNT / DECODE(TRANSACTION_QUANTITY
1523 ,0
1524 ,1
1525 ,TRANSACTION_QUANTITY)
1526 FROM
1527 CST_PAC_ACCRUAL_RECONCILE_TEMP
1528 WHERE PO_LINE_ID = L_LINE_ID
1529 AND ACCRUAL_ACCOUNT_ID = L_ACCOUNT
1530 AND INVOICE_ID is not null
1531 AND TRANSACTION_QUANTITY <> 0
1532 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1533 AND COST_TYPE_ID = P_COST_TYPE
1534 AND COST_GROUP_ID = P_COST_GROUP
1535 AND TRANSACTION_DATE between P_GL_DATE_FROM
1536 AND P_GL_DATE_TO
1537 ORDER BY
1538 SIGN(TRANSACTION_QUANTITY),
1539 INVOICE_NUM,
1540 TRANSACTION_DATE;
1541 BEGIN
1542 TXN_NUM := 0;
1543 SELECT
1544 count(*)
1545 INTO TXN_NUM
1546 FROM
1547 MTL_TRANSACTION_ACCOUNTS MTA,
1548 CST_PAC_ACCRUAL_ACCOUNTS_TEMP PAT
1549 WHERE PAT.REQUEST_ID = P_CONC_REQUEST_ID
1550 AND MTA.REFERENCE_ACCOUNT = PAT.ACCRUAL_ACCOUNT_ID
1551 AND MTA.GL_BATCH_ID <> - 1
1552 AND MTA.TRANSACTION_SOURCE_TYPE_ID = 1;
1553 IF (TXN_NUM = 0 OR TXN_NUM IS NULL) THEN
1554 RETURN;
1555 END IF;
1556 UPDATE
1557 CST_PAC_ACCRUAL_RECONCILE_TEMP p
1558 SET
1559 INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
1560 WHERE PO_LINE_ID is not null
1561 AND P.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1562 AND P.COST_TYPE_ID = P_COST_TYPE
1563 AND P.COST_GROUP_ID = P_COST_GROUP
1564 AND exists (
1565 SELECT
1566 NULL
1567 FROM
1568 CST_PAC_ACCRUAL_RECONCILE_TEMP P1
1569 WHERE P1.PO_LINE_ID = P.PO_LINE_ID
1570 AND P1.INVOICE_ID is null
1571 AND P1.ACCRUAL_ACCOUNT_ID = P.ACCRUAL_ACCOUNT_ID
1572 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1573 AND P1.COST_TYPE_ID = P_COST_TYPE
1574 AND P1.COST_GROUP_ID = P_COST_GROUP
1575
1576 HAVING ( SUM(P1.TRANSACTION_QUANTITY) = 0
1577 AND SUM(P1.TRANSACTION_AMOUNT) <> 0 ) )
1578 AND not exists (
1579 SELECT
1580 NULL
1581 FROM
1582 CST_PAC_ACCRUAL_RECONCILE_TEMP P1
1583 WHERE P1.ACCRUAL_ACCOUNT_ID = P.ACCRUAL_ACCOUNT_ID
1584 AND P1.PO_LINE_ID = P.PO_LINE_ID
1585 AND P1.INVOICE_ID is not null
1586 AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1587 AND P1.COST_TYPE_ID = P_COST_TYPE
1588 AND P1.COST_GROUP_ID = P_COST_GROUP );
1589 UPDATE
1590 CST_PAC_ACCRUAL_RECONCILE_TEMP p
1591 SET
1592 INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
1593 WHERE INVOICE_ID is not null
1594 AND NVL(TRANSACTION_QUANTITY
1595 ,0) = 0
1596 AND TRANSACTION_AMOUNT <> 0
1597 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1598 AND COST_TYPE_ID = P_COST_TYPE
1599 AND COST_GROUP_ID = P_COST_GROUP;
1600 OPEN C_PO_LINE;
1601 LOOP
1602 FETCH C_PO_LINE
1603 INTO L_LINE_ID,L_ACCOUNT,L_POL_QTY,L_POL_AMT;
1604 EXIT WHEN C_PO_LINE%NOTFOUND;
1605 IF L_POL_QTY <> 0 THEN
1606 L_POL_AVG := L_POL_AMT / L_POL_QTY;
1607 OPEN C_AP_LINE;
1608 LOOP
1609 FETCH C_AP_LINE
1610 INTO L_INV_ID,L_IVL_ID,L_APL_QTY,L_APL_AMT,L_APL_AVG;
1611 EXIT WHEN C_AP_LINE%NOTFOUND OR L_POL_QTY = 0;
1612 IF L_APL_QTY > L_POL_QTY THEN
1613 L_APL_QTY := L_POL_QTY;
1614 L_POL_QTY := 0;
1615 ELSE
1616 L_POL_QTY := L_POL_QTY - L_APL_QTY;
1617 END IF;
1618 L_IPV := ROUND(L_APL_QTY * (L_POL_AVG - L_APL_AVG) / ROUND_UNIT) * ROUND_UNIT;
1619 IF L_IPV <> 0 THEN
1620 UPDATE
1621 CST_PAC_ACCRUAL_RECONCILE_TEMP
1622 SET
1623 INVOICE_PRICE_VARIANCE = L_IPV
1624 WHERE INVOICE_ID = L_INV_ID
1625 AND INVOICE_LINE_NUM = L_IVL_ID
1626 AND TRANSACTION_QUANTITY <> 0
1627 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1628 AND COST_TYPE_ID = P_COST_TYPE
1629 AND COST_GROUP_ID = P_COST_GROUP;
1630 END IF;
1631 END LOOP;
1632 CLOSE C_AP_LINE;
1633 END IF;
1634 END LOOP;
1635 CLOSE C_PO_LINE;
1636 /*SRW.MESSAGE(320
1637 ,'calc_ipv() >> ' || TO_CHAR(SYSDATE
1638 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 /*SRW.MESSAGE(320
1642 ,SQLERRM)*/NULL;
1643 /*SRW.MESSAGE(320
1644 ,'calc_ipv() >X ' || TO_CHAR(SYSDATE
1645 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1646 RAISE;
1647 END;
1648 END CALC_IPV;
1649
1650 FUNCTION PO_LINE_SORTFORMULA(PO_LINE_NUM IN NUMBER) RETURN NUMBER IS
1651 BEGIN
1652 IF (P_SORT_OPTION = 'ITEM') THEN
1653 RETURN (PO_LINE_NUM);
1654 ELSE
1655 RETURN (0);
1656 END IF;
1657 RETURN NULL;
1658 END PO_LINE_SORTFORMULA;
1659
1660 FUNCTION INSTALL_STATUS RETURN BOOLEAN IS
1661 L_COUNT NUMBER;
1662 BEGIN
1663 SELECT
1664 count(*)
1665 INTO L_COUNT
1666 FROM
1667 AP_SYSTEM_PARAMETERS_ALL
1668 WHERE NVL(ORG_ID
1669 ,P_OPERATING_UNIT) = P_OPERATING_UNIT;
1670 IF L_COUNT = 1 THEN
1671 /*SRW.MESSAGE(001
1672 ,'AP is installed')*/NULL;
1673 SELECT
1674 count(*)
1675 INTO L_COUNT
1676 FROM
1677 PO_SYSTEM_PARAMETERS_ALL
1678 WHERE NVL(ORG_ID
1679 ,P_OPERATING_UNIT) = P_OPERATING_UNIT;
1680 IF L_COUNT = 1 THEN
1681 /*SRW.MESSAGE(001
1682 ,'PO is installed')*/NULL;
1683 SELECT
1684 EXPENSE_ACCRUAL_CODE
1685 INTO P_ACCRUAL_CODE
1686 FROM
1687 PO_SYSTEM_PARAMETERS_ALL
1688 WHERE NVL(ORG_ID
1689 ,P_OPERATING_UNIT) = P_OPERATING_UNIT;
1690 RETURN TRUE;
1691 END IF;
1692 /*SRW.MESSAGE(001
1693 ,'PO is not installed')*/NULL;
1694 RETURN FALSE;
1695 END IF;
1696 /*SRW.MESSAGE(001
1697 ,'AP is installed')*/NULL;
1698 RETURN FALSE;
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 /*SRW.MESSAGE(001
1702 ,'PO or AP is not installed')*/NULL;
1703 RETURN FALSE;
1704 END INSTALL_STATUS;
1705
1706 PROCEDURE IDENT_ACCOUNTS IS
1707 BEGIN
1708 INSERT INTO CST_PAC_ACCRUAL_ACCOUNTS_TEMP
1709 (REQUEST_ID
1710 ,ACCRUAL_ACCOUNT_ID
1711 ,LEGAL_ENTITY_ID)
1712 SELECT
1713 DISTINCT
1714 P_CONC_REQUEST_ID,
1715 ACCRUAL_ACCOUNT_ID,
1716 P_LEGAL_ENTITY
1717 FROM
1718 PO_DISTRIBUTIONS_ALL POD
1719 WHERE POD.ACCRUAL_ACCOUNT_ID is not null
1720 AND POD.ORG_ID = P_OPERATING_UNIT
1721 AND NOT ( POD.DESTINATION_TYPE_CODE = 'EXPENSE'
1722 AND P_ACCRUAL_CODE = 'PERIOD END' )
1723 AND NOT EXISTS (
1724 SELECT
1725 1
1726 FROM
1727 CST_PAC_ACCRUAL_ACCOUNTS_TEMP A1
1728 WHERE POD.ACCRUAL_ACCOUNT_ID = A1.ACCRUAL_ACCOUNT_ID
1729 AND A1.REQUEST_ID = P_CONC_REQUEST_ID );
1730 /*SRW.MESSAGE(102
1731 ,'ident_accounts() >> ' || TO_CHAR(SYSDATE
1732 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 /*SRW.MESSAGE(102
1736 ,SQLERRM)*/NULL;
1737 /*SRW.MESSAGE(102
1738 ,'ident_accounts() >X ' || TO_CHAR(SYSDATE
1739 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1740 RAISE;
1741 END IDENT_ACCOUNTS;
1742
1743 PROCEDURE UPDATE_WRITEOFF IS
1744 BEGIN
1745 UPDATE
1746 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1747 SET
1748 (WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
1749 MAX(WRITE_OFF_ID),
1750 DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
1751 ,'WRITE OFF'
1752 ,1
1753 ,-1)))
1754 ,1
1755 ,'Y'
1756 ,'N')
1757 FROM
1758 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1759 WHERE PAWO.INV_TRANSACTION_ID = PART.INV_TRANSACTION_ID
1760 AND PAWO.COST_TYPE_ID = P_COST_TYPE
1761 AND PAWO.COST_GROUP_ID = P_COST_GROUP
1762 AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1763 GROUP BY
1764 PAWO.INV_TRANSACTION_ID)
1765 WHERE PART.COST_TYPE_ID = P_COST_TYPE
1766 AND PART.COST_GROUP_ID = P_COST_GROUP
1767 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1768 AND PART.ROWID in (
1769 SELECT
1770 PART1.ROWID
1771 FROM
1772 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
1773 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1774 WHERE PAWO.INV_TRANSACTION_ID = PART1.INV_TRANSACTION_ID
1775 AND PART1.COST_TYPE_ID = P_COST_TYPE
1776 AND PART1.COST_GROUP_ID = P_COST_GROUP
1777 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1778 AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
1779 AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
1780 AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
1781 /*SRW.MESSAGE(300
1782 ,'update_inventory() >> ' || TO_CHAR(SYSDATE
1783 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1784 UPDATE
1785 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1786 SET
1787 (WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
1788 MAX(WRITE_OFF_ID),
1789 DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
1790 ,'WRITE OFF'
1791 ,1
1792 ,-1)))
1793 ,1
1794 ,'Y'
1795 ,'N')
1796 FROM
1797 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1798 WHERE PAWO.WIP_TRANSACTION_ID = PART.WIP_TRANSACTION_ID
1799 AND PAWO.COST_TYPE_ID = P_COST_TYPE
1800 AND PAWO.COST_GROUP_ID = P_COST_GROUP
1801 AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1802 GROUP BY
1803 PAWO.WIP_TRANSACTION_ID)
1804 WHERE PART.COST_TYPE_ID = P_COST_TYPE
1805 AND PART.COST_GROUP_ID = P_COST_GROUP
1806 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1807 AND PART.ROWID in (
1808 SELECT
1809 PART1.ROWID
1810 FROM
1811 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
1812 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1813 WHERE PAWO.WIP_TRANSACTION_ID = PART1.WIP_TRANSACTION_ID
1814 AND PART1.COST_TYPE_ID = P_COST_TYPE
1815 AND PART1.COST_GROUP_ID = P_COST_GROUP
1816 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1817 AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
1818 AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
1819 AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
1820 /*SRW.MESSAGE(300
1821 ,'update_wip() >> ' || TO_CHAR(SYSDATE
1822 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1823 UPDATE
1824 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1825 SET
1826 (WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
1827 MAX(WRITE_OFF_ID),
1828 DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
1829 ,'WRITE OFF'
1830 ,1
1831 ,-1)))
1832 ,1
1833 ,'Y'
1834 ,'N')
1835 FROM
1836 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1837 WHERE PAWO.PO_TRANSACTION_ID = PART.PO_TRANSACTION_ID
1838 AND PAWO.COST_TYPE_ID = P_COST_TYPE
1839 AND PAWO.COST_GROUP_ID = P_COST_GROUP
1840 AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1841 GROUP BY
1842 PAWO.PO_TRANSACTION_ID)
1843 WHERE PART.COST_TYPE_ID = P_COST_TYPE
1844 AND PART.COST_GROUP_ID = P_COST_GROUP
1845 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1846 AND PART.ROWID in (
1847 SELECT
1848 PART1.ROWID
1849 FROM
1850 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
1851 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1852 WHERE PAWO.PO_TRANSACTION_ID = PART1.PO_TRANSACTION_ID
1853 AND PART1.COST_TYPE_ID = P_COST_TYPE
1854 AND PART1.COST_GROUP_ID = P_COST_GROUP
1855 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1856 AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
1857 AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
1858 AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
1859 /*SRW.MESSAGE(300
1860 ,'update_po() >> ' || TO_CHAR(SYSDATE
1861 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1862 UPDATE
1863 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1864 SET
1865 (WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
1866 MAX(WRITE_OFF_ID),
1867 DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
1868 ,'WRITE OFF'
1869 ,1
1870 ,-1)))
1871 ,1
1872 ,'Y'
1873 ,'N')
1874 FROM
1875 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1876 WHERE PAWO.INVOICE_ID = PART.INVOICE_ID
1877 AND PAWO.INVOICE_LINE_NUM = PART.INVOICE_LINE_NUM
1878 AND PAWO.COST_TYPE_ID = P_COST_TYPE
1879 AND PAWO.COST_GROUP_ID = P_COST_GROUP
1880 AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1881 GROUP BY
1882 PAWO.INVOICE_ID,
1883 PAWO.INVOICE_LINE_NUM)
1884 WHERE PART.COST_TYPE_ID = P_COST_TYPE
1885 AND PART.COST_GROUP_ID = P_COST_GROUP
1886 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1887 AND PART.ROWID in (
1888 SELECT
1889 PART1.ROWID
1890 FROM
1891 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
1892 CST_PAC_ACCRUAL_WRITE_OFFS PAWO
1893 WHERE PAWO.INVOICE_ID = PART1.INVOICE_ID
1894 AND PAWO.INVOICE_LINE_NUM = PART1.INVOICE_LINE_NUM
1895 AND PART1.COST_TYPE_ID = P_COST_TYPE
1896 AND PART1.COST_GROUP_ID = P_COST_GROUP
1897 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1898 AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
1899 AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
1900 AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
1901 /*SRW.MESSAGE(300
1902 ,'update_writeoff() >> ' || TO_CHAR(SYSDATE
1903 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 /*SRW.MESSAGE(300
1907 ,SQLERRM)*/NULL;
1908 /*SRW.MESSAGE(300
1909 ,'update_writeoff() >X ' || TO_CHAR(SYSDATE
1910 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
1911 RAISE;
1912 END UPDATE_WRITEOFF;
1913
1914 PROCEDURE CALC_BUCKET IS
1915 BEGIN
1916 UPDATE
1917 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1918 SET
1919 (AGING_DATE,AVG_RECEIPT_PRICE) = (SELECT
1920 MIN(PART1.TRANSACTION_DATE),
1921 DECODE(SUM(DECODE(INVOICE_ID
1922 ,NULL
1923 ,PART1.TRANSACTION_QUANTITY
1924 ,0))
1925 ,0
1926 ,0
1927 ,(-1) * SUM(DECODE(INVOICE_ID
1928 ,NULL
1929 ,PART1.TRANSACTION_AMOUNT
1930 ,0)) / SUM(DECODE(INVOICE_ID
1931 ,NULL
1932 ,PART1.TRANSACTION_QUANTITY
1933 ,0)))
1934 FROM
1935 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
1936 WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
1937 AND PART1.LINE_LOCATION_ID = PART.LINE_LOCATION_ID
1938 AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
1939 AND PART1.WRITE_OFF_FLAG = 'N'
1940 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
1941 ,PART1.TRANSACTION_DATE - 1)
1942 AND NVL(P_GL_DATE_TO
1943 ,PART1.TRANSACTION_DATE + 1)
1944 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1945 AND PART1.COST_TYPE_ID = P_COST_TYPE
1946 AND PART1.COST_GROUP_ID = P_COST_GROUP)
1947 WHERE PART.WRITE_OFF_FLAG = 'N'
1948 AND PART.PO_LINE_ID is NOT NULL
1949 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1950 AND PART.COST_TYPE_ID = P_COST_TYPE
1951 AND PART.COST_GROUP_ID = P_COST_GROUP;
1952 UPDATE
1953 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1954 SET
1955 (NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
1956 SUM(PART1.TRANSACTION_QUANTITY),
1957 SUM(PART1.TRANSACTION_AMOUNT)
1958 FROM
1959 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
1960 WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
1961 AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
1962 AND PART1.WRITE_OFF_FLAG = 'N'
1963 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
1964 ,PART1.TRANSACTION_DATE - 1)
1965 AND NVL(P_GL_DATE_TO
1966 ,PART1.TRANSACTION_DATE + 1)
1967 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1968 AND PART1.COST_TYPE_ID = P_COST_TYPE
1969 AND PART1.COST_GROUP_ID = P_COST_GROUP)
1970 WHERE PART.WRITE_OFF_FLAG = 'N'
1971 AND PART.PO_LINE_ID is NOT NULL
1972 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
1973 AND PART.COST_TYPE_ID = P_COST_TYPE
1974 AND PART.COST_GROUP_ID = P_COST_GROUP;
1975 UPDATE
1976 CST_PAC_ACCRUAL_RECONCILE_TEMP part
1977 SET
1978 (AGING_DATE,AVG_RECEIPT_PRICE,NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
1979 PART1.TRANSACTION_DATE,
1980 DECODE(SUM(DECODE(INVOICE_ID
1981 ,NULL
1982 ,PART1.TRANSACTION_QUANTITY
1983 ,0))
1984 ,0
1985 ,0
1986 ,(-1) * SUM(DECODE(INVOICE_ID
1987 ,NULL
1988 ,PART1.TRANSACTION_AMOUNT
1989 ,0)) / SUM(DECODE(INVOICE_ID
1990 ,NULL
1991 ,PART1.TRANSACTION_QUANTITY
1992 ,0))),
1993 PART1.TRANSACTION_QUANTITY,
1994 PART1.TRANSACTION_AMOUNT
1995 FROM
1996 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
1997 WHERE PART1.ROWID = PART.ROWID
1998 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
1999 ,PART1.TRANSACTION_DATE - 1)
2000 AND NVL(P_GL_DATE_TO
2001 ,PART1.TRANSACTION_DATE + 1)
2002 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2003 AND PART1.COST_TYPE_ID = P_COST_TYPE
2004 AND PART1.COST_GROUP_ID = P_COST_GROUP
2005 GROUP BY
2006 PART1.TRANSACTION_DATE,
2007 PART1.TRANSACTION_QUANTITY,
2008 PART1.TRANSACTION_AMOUNT)
2009 WHERE PART.WRITE_OFF_FLAG = 'N'
2010 AND PART.PO_LINE_ID is NULL
2011 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2012 AND PART.COST_TYPE_ID = P_COST_TYPE
2013 AND PART.COST_GROUP_ID = P_COST_GROUP;
2014 UPDATE
2015 CST_PAC_ACCRUAL_RECONCILE_TEMP part
2016 SET
2017 (AGING_DATE,AVG_RECEIPT_PRICE) = (SELECT
2018 MIN(PART1.TRANSACTION_DATE),
2019 DECODE(SUM(DECODE(INVOICE_ID
2020 ,NULL
2021 ,PART1.TRANSACTION_QUANTITY
2022 ,0))
2023 ,0
2024 ,0
2025 ,(-1) * SUM(DECODE(INVOICE_ID
2026 ,NULL
2027 ,PART1.TRANSACTION_AMOUNT
2028 ,0)) / SUM(DECODE(INVOICE_ID
2029 ,NULL
2030 ,PART1.TRANSACTION_QUANTITY
2031 ,0)))
2032 FROM
2033 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
2034 WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
2035 AND PART1.LINE_LOCATION_ID = PART.LINE_LOCATION_ID
2036 AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
2037 AND PART1.WRITE_OFF_FLAG = 'Y'
2038 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
2039 ,PART1.TRANSACTION_DATE - 1)
2040 AND NVL(P_GL_DATE_TO
2041 ,PART1.TRANSACTION_DATE + 1)
2042 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2043 AND PART1.COST_TYPE_ID = P_COST_TYPE
2044 AND PART1.COST_GROUP_ID = P_COST_GROUP)
2045 WHERE PART.WRITE_OFF_FLAG = 'Y'
2046 AND PART.PO_LINE_ID is NOT NULL
2047 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2048 AND PART.COST_TYPE_ID = P_COST_TYPE
2049 AND PART.COST_GROUP_ID = P_COST_GROUP;
2050 UPDATE
2051 CST_PAC_ACCRUAL_RECONCILE_TEMP part
2052 SET
2053 (NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
2054 SUM(PART1.TRANSACTION_QUANTITY),
2055 0
2056 FROM
2057 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
2058 WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
2059 AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
2060 AND PART1.WRITE_OFF_FLAG = 'Y'
2061 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
2062 ,PART1.TRANSACTION_DATE - 1)
2063 AND NVL(P_GL_DATE_TO
2064 ,PART1.TRANSACTION_DATE + 1)
2065 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2066 AND PART1.COST_TYPE_ID = P_COST_TYPE
2067 AND PART1.COST_GROUP_ID = P_COST_GROUP)
2068 WHERE PART.WRITE_OFF_FLAG = 'Y'
2069 AND PART.PO_LINE_ID is NOT NULL
2070 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2071 AND PART.COST_TYPE_ID = P_COST_TYPE
2072 AND PART.COST_GROUP_ID = P_COST_GROUP;
2073 UPDATE
2074 CST_PAC_ACCRUAL_RECONCILE_TEMP part
2075 SET
2076 (AGING_DATE,AVG_RECEIPT_PRICE,NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
2077 PART1.TRANSACTION_DATE,
2078 DECODE(SUM(DECODE(INVOICE_ID
2079 ,NULL
2080 ,PART1.TRANSACTION_QUANTITY
2081 ,0))
2082 ,0
2083 ,0
2084 ,(-1) * SUM(DECODE(INVOICE_ID
2085 ,NULL
2086 ,PART1.TRANSACTION_AMOUNT
2087 ,0)) / SUM(DECODE(INVOICE_ID
2088 ,NULL
2089 ,PART1.TRANSACTION_QUANTITY
2090 ,0))),
2091 PART1.TRANSACTION_QUANTITY,
2092 0
2093 FROM
2094 CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
2095 WHERE PART1.ROWID = PART.ROWID
2096 AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
2097 ,PART1.TRANSACTION_DATE - 1)
2098 AND NVL(P_GL_DATE_TO
2099 ,PART1.TRANSACTION_DATE + 1)
2100 AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2101 AND PART1.COST_TYPE_ID = P_COST_TYPE
2102 AND PART1.COST_GROUP_ID = P_COST_GROUP
2103 GROUP BY
2104 PART1.TRANSACTION_DATE,
2105 PART1.TRANSACTION_QUANTITY)
2106 WHERE PART.WRITE_OFF_FLAG = 'Y'
2107 AND PART.PO_LINE_ID is NULL
2108 AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2109 AND PART.COST_TYPE_ID = P_COST_TYPE
2110 AND PART.COST_GROUP_ID = P_COST_GROUP;
2111 /*SRW.MESSAGE(310
2112 ,'calc_bucket() >> ' || TO_CHAR(SYSDATE
2113 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2114 EXCEPTION
2115 WHEN OTHERS THEN
2116 /*SRW.MESSAGE(310
2117 ,SQLERRM)*/NULL;
2118 /*SRW.MESSAGE(310
2119 ,'calc_bucket() >X ' || TO_CHAR(SYSDATE
2120 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2121 RAISE;
2122 END CALC_BUCKET;
2123
2124 PROCEDURE INSERT_MFG_DATA IS
2125 WIP_IS_INSTALLED VARCHAR2(1);
2126 INV_IS_INSTALLED VARCHAR2(1);
2127 L_SUCCESS BOOLEAN;
2128 INDUSTRY VARCHAR2(1);
2129 BEGIN
2130 L_SUCCESS := GET(201
2131 ,706
2132 ,WIP_IS_INSTALLED
2133 ,INDUSTRY);
2134 L_SUCCESS := GET(201
2135 ,401
2136 ,INV_IS_INSTALLED
2137 ,INDUSTRY);
2138 IF NOT INV_IS_INSTALLED = 'I' THEN
2139 NULL;
2140 END IF;
2141 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
2142 (REQUEST_ID
2143 ,PROGRAM_APPLICATION_ID
2144 ,PROGRAM_ID
2145 ,PROGRAM_UPDATE_DATE
2146 ,PERIOD_NAME
2147 ,TRANSACTION_ORGANIZATION_ID
2148 ,ITEM_MASTER_ORGANIZATION_ID
2149 ,ACCRUAL_ACCOUNT_ID
2150 ,ACCRUAL_CODE
2151 ,PO_TRANSACTION_TYPE
2152 ,TRANSACTION_DATE
2153 ,INVOICE_NUM
2154 ,RECEIPT_NUM
2155 ,PO_TRANSACTION_ID
2156 ,INV_TRANSACTION_ID
2157 ,INV_TRANSACTION_TYPE_ID
2158 ,WIP_TRANSACTION_ID
2159 ,WIP_TRANSACTION_TYPE_ID
2160 ,INVENTORY_ITEM_ID
2161 ,PO_UNIT_OF_MEASURE
2162 ,PRIMARY_UNIT_OF_MEASURE
2163 ,TRANSACTION_QUANTITY
2164 ,NET_PO_LINE_QUANTITY
2165 ,PO_HEADER_ID
2166 ,PO_NUM
2167 ,PO_LINE_NUM
2168 ,PO_LINE_ID
2169 ,PO_DISTRIBUTION_ID
2170 ,VENDOR_ID
2171 ,VENDOR_NAME
2172 ,VENDOR_NAME_ALT
2173 ,TRANSACTION_UNIT_PRICE
2174 ,INVOICE_ID
2175 ,INVOICE_LINE_NUM
2176 ,AVG_RECEIPT_PRICE
2177 ,TRANSACTION_AMOUNT
2178 ,LINE_MATCH_ORDER
2179 ,TRANSACTION_SOURCE_CODE
2180 ,WRITE_OFF_FLAG
2181 ,WRITE_OFF_ID
2182 ,DESTINATION_TYPE_CODE
2183 ,REASON_ID
2184 ,COMMENTS
2185 ,LINE_LOCATION_ID
2186 ,COST_TYPE_ID
2187 ,COST_GROUP_ID
2188 ,LEGAL_ENTITY_ID
2189 ,PERIOD_ID)
2190 SELECT
2191 P_CONC_REQUEST_ID,
2192 P_APPL_ID,
2193 P_PROGRAM_ID,
2194 sysdate,
2195 NULL,
2196 P_ORG_ID,
2197 P_MASTER_ORG_ID,
2198 CAL.CODE_COMBINATION_ID,
2199 PLC.DISPLAYED_FIELD,
2200 NULL,
2201 CAH.ACCOUNTING_DATE,
2202 NULL,
2203 RSH.RECEIPT_NUM,
2204 NULL,
2205 CAH.ACCOUNTING_EVENT_ID,
2206 MMT.TRANSACTION_TYPE_ID,
2207 NULL,
2208 NULL,
2209 MMT.INVENTORY_ITEM_ID,
2210 POL.UNIT_MEAS_LOOKUP_CODE,
2211 NULL,
2212 DECODE(RCT.PRIMARY_QUANTITY
2213 ,0
2214 ,0
2215 ,(-1) * MMT.PRIMARY_QUANTITY * NVL(RCT.SOURCE_DOC_QUANTITY / RCT.PRIMARY_QUANTITY
2216 ,1)),
2217 NULL,
2218 MMT.TRANSACTION_SOURCE_ID,
2219 POH.SEGMENT1,
2220 POL.LINE_NUM,
2221 NVL(MMT.TRX_SOURCE_LINE_ID
2222 ,RCT.PO_LINE_ID),
2223 RCT.PO_DISTRIBUTION_ID,
2224 POH.VENDOR_ID,
2225 POV.VENDOR_NAME,
2226 POV.VENDOR_NAME_ALT,
2227 DECODE(RCT.SOURCE_DOC_QUANTITY * MMT.PRIMARY_QUANTITY
2228 ,0
2229 ,0
2230 ,(-1) * ROUND((NVL(CAL.ACCOUNTED_DR
2231 ,(-1) * CAL.ACCOUNTED_CR) / MMT.PRIMARY_QUANTITY) * NVL(RCT.PRIMARY_QUANTITY / RCT.SOURCE_DOC_QUANTITY
2232 ,1)
2233 ,P_EXT_PREC)),
2234 NULL,
2235 NULL,
2236 NULL,
2237 ROUND(NVL(CAL.ACCOUNTED_DR
2238 ,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
2239 NULL,
2240 'INV',
2241 'N',
2242 NULL,
2243 RCT.DESTINATION_TYPE_CODE,
2244 NULL,
2245 NULL,
2246 RCT.PO_LINE_LOCATION_ID,
2247 P_COST_TYPE,
2248 P_COST_GROUP,
2249 P_LEGAL_ENTITY,
2250 P_PERIOD
2251 FROM
2252 PO_LOOKUP_CODES PLC,
2253 PO_VENDORS POV,
2254 PO_LINES_ALL POL,
2255 PO_HEADERS_ALL POH,
2256 RCV_SHIPMENT_HEADERS RSH,
2257 RCV_TRANSACTIONS RCT,
2258 MTL_MATERIAL_TRANSACTIONS MMT,
2259 CST_AE_HEADERS CAH,
2260 CST_AE_LINES CAL,
2261 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
2262 WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
2263 AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
2264 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
2265 AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
2266 AND CAH.GL_TRANSFER_FLAG = 'Y'
2267 AND CAH.COST_TYPE_ID = P_COST_TYPE
2268 AND CAH.COST_GROUP_ID = P_COST_GROUP
2269 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2270 AND MMT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
2271 AND RCT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
2272 AND NVL(RCT.ORGANIZATION_ID
2273 ,P_ORG_ID) = P_ORG_ID
2274 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
2275 AND POH.PO_HEADER_ID = RCT.PO_HEADER_ID
2276 AND POH.ORG_ID = P_OPERATING_UNIT
2277 AND POL.PO_LINE_ID = RCT.PO_LINE_ID
2278 AND POV.VENDOR_ID = POH.VENDOR_ID
2279 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
2280 ,POV.VENDOR_NAME)
2281 AND NVL(P_VENDOR_TO
2282 ,POV.VENDOR_NAME)
2283 AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
2284 AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
2285 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
2286 /*Support for LCM*/
2287 AND NOT ( mmt.transaction_action_id = 24
2288 AND nvl(mmt.source_code,'XXX') = 'PACLCMADJ'
2289 );
2290 /*SRW.MESSAGE(111
2291 ,'insert_inv_data() >> ' || TO_CHAR(SYSDATE
2292 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2293 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
2294 (REQUEST_ID
2295 ,PROGRAM_APPLICATION_ID
2296 ,PROGRAM_ID
2297 ,PROGRAM_UPDATE_DATE
2298 ,PERIOD_NAME
2299 ,TRANSACTION_ORGANIZATION_ID
2300 ,ITEM_MASTER_ORGANIZATION_ID
2301 ,ACCRUAL_ACCOUNT_ID
2302 ,ACCRUAL_CODE
2303 ,PO_TRANSACTION_TYPE
2304 ,TRANSACTION_DATE
2305 ,INVOICE_NUM
2306 ,RECEIPT_NUM
2307 ,PO_TRANSACTION_ID
2308 ,INV_TRANSACTION_ID
2309 ,INV_TRANSACTION_TYPE_ID
2310 ,WIP_TRANSACTION_ID
2311 ,WIP_TRANSACTION_TYPE_ID
2312 ,INVENTORY_ITEM_ID
2313 ,PO_UNIT_OF_MEASURE
2314 ,PRIMARY_UNIT_OF_MEASURE
2315 ,TRANSACTION_QUANTITY
2316 ,NET_PO_LINE_QUANTITY
2317 ,PO_HEADER_ID
2318 ,PO_NUM
2319 ,PO_LINE_NUM
2320 ,PO_LINE_ID
2321 ,PO_DISTRIBUTION_ID
2322 ,VENDOR_ID
2323 ,VENDOR_NAME
2324 ,VENDOR_NAME_ALT
2325 ,TRANSACTION_UNIT_PRICE
2326 ,INVOICE_ID
2327 ,INVOICE_LINE_NUM
2328 ,AVG_RECEIPT_PRICE
2329 ,TRANSACTION_AMOUNT
2330 ,LINE_MATCH_ORDER
2331 ,TRANSACTION_SOURCE_CODE
2332 ,WRITE_OFF_FLAG
2333 ,WRITE_OFF_ID
2334 ,DESTINATION_TYPE_CODE
2335 ,REASON_ID
2336 ,COMMENTS
2337 ,LINE_LOCATION_ID
2338 ,COST_TYPE_ID
2339 ,COST_GROUP_ID
2340 ,LEGAL_ENTITY_ID
2341 ,PERIOD_ID)
2342 SELECT
2343 P_CONC_REQUEST_ID,
2344 P_APPL_ID,
2345 P_PROGRAM_ID,
2346 SYSDATE,
2347 NULL,
2348 P_ORG_ID,
2349 P_MASTER_ORG_ID,
2350 CAL.CODE_COMBINATION_ID,
2351 PLC.DISPLAYED_FIELD,
2352 NULL,
2353 CAH.ACCOUNTING_DATE,
2354 NULL,
2355 RSH.RECEIPT_NUM,
2356 NULL,
2357 NULL,
2358 NULL,
2359 WTS.TRANSACTION_ID,
2360 WTS.TRANSACTION_TYPE,
2361 POL.ITEM_ID,
2362 POL.UNIT_MEAS_LOOKUP_CODE,
2363 RCT.UNIT_OF_MEASURE,
2364 DECODE(RCT.PRIMARY_QUANTITY
2365 ,0
2366 ,0
2367 ,(-1) * NVL(WTS.PRIMARY_QUANTITY
2368 ,0) * NVL(RCT.SOURCE_DOC_QUANTITY / RCT.PRIMARY_QUANTITY
2369 ,1)),
2370 NULL,
2371 WTS.PO_HEADER_ID,
2372 POH.SEGMENT1,
2373 POL.LINE_NUM,
2374 WTS.PO_LINE_ID,
2375 RCT.PO_DISTRIBUTION_ID,
2376 POH.VENDOR_ID,
2377 POV.VENDOR_NAME,
2378 POV.VENDOR_NAME_ALT,
2379 DECODE(RCT.SOURCE_DOC_QUANTITY * WTS.PRIMARY_QUANTITY
2380 ,0
2381 ,0
2382 ,(-1) * ROUND((NVL(CAL.ACCOUNTED_DR
2383 ,(-1) * CAL.ACCOUNTED_CR) / WTS.PRIMARY_QUANTITY) * NVL(RCT.PRIMARY_QUANTITY / RCT.SOURCE_DOC_QUANTITY
2384 ,1)
2385 ,P_EXT_PREC)),
2386 NULL,
2387 NULL,
2388 NULL,
2389 ROUND(NVL(CAL.ACCOUNTED_DR
2390 ,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
2391 NULL,
2392 'WIP',
2393 'N',
2394 NULL,
2395 RCT.DESTINATION_TYPE_CODE,
2396 NULL,
2397 NULL,
2398 RCT.PO_LINE_LOCATION_ID,
2399 P_COST_TYPE,
2400 P_COST_GROUP,
2401 P_LEGAL_ENTITY,
2402 P_PERIOD
2403 FROM
2404 PO_HEADERS_ALL POH,
2405 PO_LINES_ALL POL,
2406 PO_VENDORS POV,
2407 PO_LOOKUP_CODES PLC,
2408 RCV_TRANSACTIONS RCT,
2409 RCV_SHIPMENT_HEADERS RSH,
2410 CST_AE_HEADERS CAH,
2411 CST_AE_LINES CAL,
2412 WIP_TRANSACTIONS WTS,
2413 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
2414 WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
2415 AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
2416 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
2417 AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
2418 AND CAH.GL_TRANSFER_FLAG = 'Y'
2419 AND CAH.COST_TYPE_ID = P_COST_TYPE
2420 AND CAH.COST_GROUP_ID = P_COST_GROUP
2421 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2422 AND WTS.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
2423 AND WTS.TRANSACTION_TYPE = 3
2424 AND RCT.TRANSACTION_ID = WTS.RCV_TRANSACTION_ID
2425 AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
2426 AND RCT.ORGANIZATION_ID = P_ORG_ID
2427 AND POH.PO_HEADER_ID = RCT.PO_HEADER_ID
2428 AND POH.ORG_ID = P_OPERATING_UNIT
2429 AND POL.PO_LINE_ID = RCT.PO_LINE_ID
2430 AND POV.VENDOR_ID = POH.VENDOR_ID
2431 AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
2432 ,POV.VENDOR_NAME)
2433 AND NVL(P_VENDOR_TO
2434 ,POV.VENDOR_NAME)
2435 AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
2436 AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
2437 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
2438 /*SRW.MESSAGE(113
2439 ,'insert_wip_data() >> ' || TO_CHAR(SYSDATE
2440 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2441 EXCEPTION
2442 WHEN OTHERS THEN
2443 /*SRW.MESSAGE(111
2444 ,SQLERRM)*/NULL;
2445 /*SRW.MESSAGE(111
2446 ,'insert_mfg_static() >X ' || TO_CHAR(SYSDATE
2447 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2448 RAISE;
2449 END INSERT_MFG_DATA;
2450
2451 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
2452 BEGIN
2453 DECLARE
2454 SQL_STMT_NUM VARCHAR(5);
2455 L_COUNT NUMBER(10);
2456
2457 CURSOR C_OU IS
2458 SELECT /*+ ORDERED */ -- Added for perf bug rgangara */
2459 DISTINCT
2460 POH.ORG_ID
2461 FROM
2462 CST_COST_GROUP_ASSIGNMENTS CCGA,
2463 RCV_TRANSACTIONS RT,
2464 PO_HEADERS_ALL POH
2465 WHERE POH.PO_HEADER_ID = RT.PO_HEADER_ID
2466 AND CCGA.ORGANIZATION_ID = RT.ORGANIZATION_ID
2467 AND CCGA.COST_GROUP_ID = P_COST_GROUP;
2468
2469 CURSOR C_ORG IS
2470 SELECT
2471 MP.ORGANIZATION_ID,
2472 MP.MASTER_ORGANIZATION_ID
2473 FROM
2474 MTL_PARAMETERS MP,
2475 CST_COST_GROUP_ASSIGNMENTS CGA
2476 WHERE MP.ORGANIZATION_ID = CGA.ORGANIZATION_ID
2477 AND CGA.COST_GROUP_ID = P_COST_GROUP;
2478 BEGIN
2479 SQL_STMT_NUM := '4: ';
2480 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
2481 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
2482 /*SRW.MESSAGE(400
2483 ,'start_date' || TO_CHAR(P_GL_DATE_FROM
2484 ,'DD-MON-YY HH24:MI:SS'))*/NULL;
2485 P_GL_DATE_TO := P_GL_DATE_TO + 0.99999;
2486 /*SRW.MESSAGE(400
2487 ,'end_date' || TO_CHAR(P_GL_DATE_TO
2488 ,'DD-MON-YY HH24:MI:SS'))*/NULL;
2489 SQL_STMT_NUM := '1: ';
2490 SELECT
2491 SET_OF_BOOKS_ID
2492 INTO P_SET_OF_BOOKS_ID
2493 FROM
2494 CST_LE_COST_TYPES
2495 WHERE LEGAL_ENTITY = P_LEGAL_ENTITY
2496 AND COST_TYPE_ID = P_COST_TYPE;
2497 SELECT
2498 GLL.LEDGER_NAME,
2499 GLL.CURRENCY_CODE,
2500 GLL.CHART_OF_ACCOUNTS_ID,
2501 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
2502 ,POWER(10
2503 ,NVL(-FC.PRECISION
2504 ,0))),
2505 NVL(FC.EXTENDED_PRECISION
2506 ,NVL(FC.PRECISION
2507 ,0)),
2508 PLC1.DISPLAYED_FIELD,
2509 PLC2.DISPLAYED_FIELD
2510 INTO P_COMPANY,P_CURRENCY_CODE,LP_CHART_OF_ACCOUNTS_ID,ROUND_UNIT,P_EXT_PREC,P_SORT_BY,P_ITEM_VENDOR
2511 FROM
2512 GL_LEDGER_LE_V GLL,
2513 FND_CURRENCIES FC,
2514 PO_LOOKUP_CODES PLC1,
2515 PO_LOOKUP_CODES PLC2
2516 WHERE GLL.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2517 AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
2518 AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
2519 AND GLL.CURRENCY_CODE = FC.CURRENCY_CODE
2520 AND PLC1.LOOKUP_TYPE = 'SRS ORDER BY'
2521 AND PLC1.LOOKUP_CODE = P_SORT_OPTION
2522 AND PLC2.LOOKUP_TYPE = 'SRS ORDER BY'
2523 AND PLC2.LOOKUP_CODE = DECODE(P_SORT_OPTION
2524 ,'ITEM'
2525 ,'VENDOR'
2526 ,'ITEM');
2527 SQL_STMT_NUM := '2: ';
2528 IF P_CONC_REQUEST_ID > 0 THEN
2529 SELECT
2530 PROGRAM_APPLICATION_ID,
2531 CONCURRENT_PROGRAM_ID
2532 INTO P_APPL_ID,P_PROGRAM_ID
2533 FROM
2534 FND_CONCURRENT_REQUESTS
2535 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
2536 ELSE
2537 SELECT
2538 APPLICATION_ID,
2539 CONCURRENT_PROGRAM_ID
2540 INTO P_APPL_ID,P_PROGRAM_ID
2541 FROM
2542 FND_CONCURRENT_PROGRAMS
2543 WHERE APPLICATION_ID = 702
2544 AND CONCURRENT_PROGRAM_NAME = 'BOM_CSTRACCR_XMLP_PKG';
2545 END IF;
2546 SQL_STMT_NUM := '3: ';
2547 P_QTY_PRES := MOD(P_QTY_PRECISION
2548 ,7);
2549 SQL_STMT_NUM := '5: ';
2550 SQL_STMT_NUM := '6: ';
2551 SQL_STMT_NUM := '7: ';
2552 BEGIN
2553 /*SRW.REFERENCE(SORT_BY_ALTERNATE)*/NULL;
2554 SORT_BY_ALTERNATE := FND_PROFILE.VALUE('AP_SORT_BY_ALTERNATE');
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557 SORT_BY_ALTERNATE := 'N';
2558 END;
2559 IF P_REBUILD_REPORT = '1' THEN
2560 /*SRW.MESSAGE(400
2561 ,'Rebuild Report')*/NULL;
2562 DELETE_TABLE;
2563 /*SRW.MESSAGE(400
2564 ,'Deleted Tables')*/NULL;
2565 FOR rec_ou IN C_OU LOOP
2566 P_OPERATING_UNIT := REC_OU.ORG_ID;
2567 IF INSTALL_STATUS THEN
2568 IDENT_ACCOUNTS;
2569 /*SRW.MESSAGE(400
2570 ,'Ident Account')*/NULL;
2571 SELECT
2572 INVENTORY_ORGANIZATION_ID
2573 INTO P_MASTER_ORG_ID
2574 FROM
2575 FINANCIALS_SYSTEM_PARAMS_ALL
2576 WHERE NVL(ORG_ID
2577 ,P_OPERATING_UNIT) = P_OPERATING_UNIT;
2578 SELECT
2579 count(*)
2580 INTO L_COUNT
2581 FROM
2582 CST_COST_GROUP_ASSIGNMENTS
2583 WHERE COST_GROUP_ID = P_COST_GROUP
2584 AND ORGANIZATION_ID = P_MASTER_ORG_ID;
2585 IF L_COUNT <> 0 THEN
2586 INSERT_AP_MISC;
2587 END IF;
2588 FOR rec_org IN C_ORG LOOP
2589 P_ORG_ID := REC_ORG.ORGANIZATION_ID;
2590 P_MASTER_ORG_ID := REC_ORG.MASTER_ORGANIZATION_ID;
2591 POPULATE_TEMP_TABLE;
2592 /*SRW.MESSAGE(400
2593 ,'Populated Temp table')*/NULL;
2594 END LOOP;
2595 END IF;
2596 END LOOP;
2597 FOR rec_org IN C_ORG LOOP
2598 P_ORG_ID := REC_ORG.ORGANIZATION_ID;
2599 P_MASTER_ORG_ID := REC_ORG.MASTER_ORGANIZATION_ID;
2600 INSERT_MISC_MFG_DATA;
2601 /*SRW.MESSAGE(400
2602 ,'Inserted misc mfg data')*/NULL;
2603 END LOOP;
2604 MATCH_CONDITION;
2605 /*SRW.MESSAGE(400
2606 ,'Assigned AP match conditions')*/NULL;
2607 UPDATE_WRITEOFF;
2608 /*SRW.MESSAGE(400
2609 ,'Updated write offs')*/NULL;
2610 END IF;
2611 CALC_BUCKET;
2612 /*SRW.MESSAGE(400
2613 ,'Calculated Bucket')*/NULL;
2614 COMMIT;
2615 /*SRW.MESSAGE(400
2616 ,'POXACREC << ' || TO_CHAR(SYSDATE
2617 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2618 EXCEPTION
2619 WHEN OTHERS THEN
2620 /*SRW.MESSAGE(999
2621 ,SQL_STMT_NUM || SQLERRM)*/NULL;
2622 /*SRW.MESSAGE(999
2623 ,'POXACREC >X ' || TO_CHAR(SYSDATE
2624 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2625 ROLLBACK;
2626 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
2627 END;
2628 RETURN (TRUE);
2629 END BEFOREREPORT;
2630
2631 FUNCTION SORT_COLUMN_DSPFORMULA(ITEM_RAW IN VARCHAR2
2632 ,ITEM_SEG IN VARCHAR2
2633 ,VENDOR_NAME IN VARCHAR2) RETURN VARCHAR2 IS
2634 BEGIN
2635 /*SRW.REFERENCE(ITEM_RAW)*/NULL;
2636 /*SRW.REFERENCE(ITEM_SEG)*/NULL;
2637 /*SRW.REFERENCE(VENDOR_NAME)*/NULL;
2638 IF (P_SORT_OPTION = 'VENDOR') THEN
2639 RETURN (VENDOR_NAME);
2640 ELSE
2641 RETURN (ITEM_SEG);
2642 END IF;
2643 RETURN NULL;
2644 END SORT_COLUMN_DSPFORMULA;
2645
2646 FUNCTION INDICATORFORMULA(WRITE_OFF_ID IN NUMBER) RETURN VARCHAR2 IS
2647 BEGIN
2648 IF (LP_WRITTEN_OFF = 'Y' AND WRITE_OFF_ID IS NOT NULL) THEN
2649 RETURN ('*');
2650 ELSE
2651 RETURN (NULL);
2652 END IF;
2653 RETURN NULL;
2654 END INDICATORFORMULA;
2655
2656 FUNCTION P_QTY_TOLERANCEVALIDTRIGGER RETURN BOOLEAN IS
2657 BEGIN
2658 RETURN (TRUE);
2659 END P_QTY_TOLERANCEVALIDTRIGGER;
2660
2661 FUNCTION SORT_COLUMN_ALTFORMULA(ITEM_RAW IN VARCHAR2
2662 ,ITEM_SEG IN VARCHAR2
2663 ,VENDOR_NAME IN VARCHAR2
2664 ,VENDOR_NAME_ALT IN VARCHAR2
2665 ,ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
2666 BEGIN
2667 /*SRW.REFERENCE(ITEM_RAW)*/NULL;
2668 /*SRW.REFERENCE(ITEM_SEG)*/NULL;
2669 /*SRW.REFERENCE(VENDOR_NAME)*/NULL;
2670 /*SRW.REFERENCE(VENDOR_NAME_ALT)*/NULL;
2671 IF (P_SORT_OPTION = 'VENDOR') THEN
2672 IF (SORT_BY_ALTERNATE = 'Y') THEN
2673 RETURN (VENDOR_NAME_ALT);
2674 ELSE
2675 RETURN (VENDOR_NAME);
2676 END IF;
2677 ELSE
2678 RETURN (ITEM_PSEG);
2679 END IF;
2680 RETURN NULL;
2681 END SORT_COLUMN_ALTFORMULA;
2682
2683 FUNCTION P_PERIODVALIDTRIGGER RETURN BOOLEAN IS
2684 BEGIN
2685 SELECT
2686 PERIOD_START_DATE,
2687 PERIOD_END_DATE,
2688 PERIOD_NAME
2689 INTO P_GL_DATE_FROM,P_GL_DATE_TO,P_PERIOD_NAME
2690 FROM
2691 CST_PAC_PERIODS
2692 WHERE PAC_PERIOD_ID = P_PERIOD;
2693 RETURN (TRUE);
2694 END P_PERIODVALIDTRIGGER;
2695
2696 FUNCTION P_COST_GROUPVALIDTRIGGER RETURN BOOLEAN IS
2697 BEGIN
2698 SELECT
2699 COST_GROUP
2700 INTO P_COST_GROUP_NAME
2701 FROM
2702 CST_COST_GROUPS
2703 WHERE COST_GROUP_ID = P_COST_GROUP;
2704 RETURN (TRUE);
2705 END P_COST_GROUPVALIDTRIGGER;
2706
2707 FUNCTION P_COST_TYPEVALIDTRIGGER RETURN BOOLEAN IS
2708 BEGIN
2709 SELECT
2710 COST_TYPE
2711 INTO P_COST_TYPE_NAME
2712 FROM
2713 CST_COST_TYPES
2714 WHERE COST_TYPE_ID = P_COST_TYPE;
2715 RETURN (TRUE);
2716 END P_COST_TYPEVALIDTRIGGER;
2717
2718 FUNCTION P_LEGAL_ENTITYVALIDTRIGGER RETURN BOOLEAN IS
2719 BEGIN
2720 SELECT
2721 NAME
2722 INTO P_LEGAL_ENTITY_NAME
2723 FROM
2724 XLE_FIRSTPARTY_INFORMATION_V
2725 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY;
2726 RETURN (TRUE);
2727 END P_LEGAL_ENTITYVALIDTRIGGER;
2728
2729 PROCEDURE MATCH_RECEIPT IS
2730 BEGIN
2731 UPDATE
2732 CST_PAC_ACCRUAL_RECONCILE_TEMP
2733 SET
2734 ACCRUAL_CODE = (SELECT
2735 DISPLAYED_FIELD
2736 FROM
2737 PO_LOOKUP_CODES
2738 WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
2739 AND LOOKUP_CODE = 'AP RECEIPT MATCH')
2740 WHERE ACCRUAL_CODE = 'AP: not yet processed'
2741 AND RECEIPT_NUM IS NOT NULL
2742 AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2743 AND COST_TYPE_ID = P_COST_TYPE
2744 AND COST_GROUP_ID = P_COST_GROUP;
2745 /*SRW.MESSAGE(202
2746 ,'match_no_po() >> ' || TO_CHAR(SYSDATE
2747 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 /*SRW.MESSAGE(202
2751 ,SQLERRM)*/NULL;
2752 /*SRW.MESSAGE(202
2753 ,'match_receipt() >X ' || TO_CHAR(SYSDATE
2754 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2755 RAISE;
2756 END MATCH_RECEIPT;
2757
2758 PROCEDURE MATCH_CONDITION IS
2759 BEGIN
2760 /*SRW.MESSAGE(001
2761 ,'match_condition() << ' || TO_CHAR(SYSDATE
2762 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
2763 MATCH_NO_ITEM;
2764 MATCH_RECEIPT;
2765 MATCH_NO_PO;
2766 MATCH_INV_LINE;
2767 MATCH_INV_HEADER;
2768 MATCH_INV_ITEM;
2769 MATCH_ITEM;
2770 /*SRW.MESSAGE(001
2771 ,'match_condition() >> ' || TO_CHAR(SYSDATE
2772 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 /*SRW.MESSAGE(001
2776 ,'match_condition() >X ' || TO_CHAR(SYSDATE
2777 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
2778 RAISE;
2779 END MATCH_CONDITION;
2780
2781 PROCEDURE INSERT_MISC_MFG_DATA IS
2782 WIP_IS_INSTALLED VARCHAR2(1);
2783 INV_IS_INSTALLED VARCHAR2(1);
2784 L_SUCCESS BOOLEAN;
2785 INDUSTRY VARCHAR2(1);
2786 BEGIN
2787 L_SUCCESS := GET(201
2788 ,706
2789 ,WIP_IS_INSTALLED
2790 ,INDUSTRY);
2791 L_SUCCESS := GET(201
2792 ,401
2793 ,INV_IS_INSTALLED
2794 ,INDUSTRY);
2795 IF NOT INV_IS_INSTALLED = 'I' THEN
2796 NULL;
2797 END IF;
2798 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
2799 (REQUEST_ID
2800 ,PROGRAM_APPLICATION_ID
2801 ,PROGRAM_ID
2802 ,PROGRAM_UPDATE_DATE
2803 ,PERIOD_NAME
2804 ,TRANSACTION_ORGANIZATION_ID
2805 ,ITEM_MASTER_ORGANIZATION_ID
2806 ,ACCRUAL_ACCOUNT_ID
2807 ,ACCRUAL_CODE
2808 ,PO_TRANSACTION_TYPE
2809 ,TRANSACTION_DATE
2810 ,INVOICE_NUM
2811 ,RECEIPT_NUM
2812 ,PO_TRANSACTION_ID
2813 ,INV_TRANSACTION_ID
2814 ,INV_TRANSACTION_TYPE_ID
2815 ,WIP_TRANSACTION_ID
2816 ,WIP_TRANSACTION_TYPE_ID
2817 ,INVENTORY_ITEM_ID
2818 ,PO_UNIT_OF_MEASURE
2819 ,PRIMARY_UNIT_OF_MEASURE
2820 ,TRANSACTION_QUANTITY
2821 ,NET_PO_LINE_QUANTITY
2822 ,PO_HEADER_ID
2823 ,PO_NUM
2824 ,PO_LINE_NUM
2825 ,PO_LINE_ID
2826 ,PO_DISTRIBUTION_ID
2827 ,VENDOR_ID
2828 ,VENDOR_NAME
2829 ,VENDOR_NAME_ALT
2830 ,TRANSACTION_UNIT_PRICE
2831 ,INVOICE_ID
2832 ,INVOICE_LINE_NUM
2833 ,AVG_RECEIPT_PRICE
2834 ,TRANSACTION_AMOUNT
2835 ,LINE_MATCH_ORDER
2836 ,TRANSACTION_SOURCE_CODE
2837 ,WRITE_OFF_FLAG
2838 ,WRITE_OFF_ID
2839 ,DESTINATION_TYPE_CODE
2840 ,REASON_ID
2841 ,COMMENTS
2842 ,LINE_LOCATION_ID
2843 ,COST_TYPE_ID
2844 ,COST_GROUP_ID
2845 ,LEGAL_ENTITY_ID
2846 ,PERIOD_ID)
2847 SELECT
2848 P_CONC_REQUEST_ID,
2849 P_APPL_ID,
2850 P_PROGRAM_ID,
2851 sysdate,
2852 NULL,
2853 P_ORG_ID,
2854 P_MASTER_ORG_ID,
2855 CAL.CODE_COMBINATION_ID,
2856 MTT.TRANSACTION_TYPE_NAME,
2857 NULL,
2858 CAH.ACCOUNTING_DATE,
2859 NULL,
2860 NULL,
2861 NULL,
2862 MMT.TRANSACTION_ID,
2863 MMT.TRANSACTION_TYPE_ID,
2864 NULL,
2865 NULL,
2866 MMT.INVENTORY_ITEM_ID,
2867 NULL,
2868 MSI.PRIMARY_UOM_CODE,
2869 NVL(MMT.PRIMARY_QUANTITY
2870 ,0),
2871 NULL,
2872 NULL,
2873 NULL,
2874 NULL,
2875 NULL,
2876 NULL,
2877 NULL,
2878 NULL,
2879 NULL,
2880 ROUND(ABS(NVL(CAL.ACCOUNTED_DR
2881 ,(-1) * CAL.ACCOUNTED_CR) / DECODE(MMT.PRIMARY_QUANTITY
2882 ,0
2883 ,1
2884 ,MMT.PRIMARY_QUANTITY))
2885 ,P_EXT_PREC),
2886 NULL,
2887 NULL,
2888 NULL,
2889 ROUND(NVL(CAL.ACCOUNTED_DR
2890 ,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
2891 NULL,
2892 'INV',
2893 'N',
2894 NULL,
2895 NULL,
2896 NULL,
2897 NULL,
2898 NULL,
2899 P_COST_TYPE,
2900 P_COST_GROUP,
2901 P_LEGAL_ENTITY,
2902 P_PERIOD
2903 FROM
2904 CST_AE_HEADERS CAH,
2905 CST_AE_LINES CAL,
2906 MTL_MATERIAL_TRANSACTIONS MMT,
2907 MTL_TRANSACTION_TYPES MTT,
2908 MTL_SYSTEM_ITEMS MSI,
2909 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
2910 WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
2911 AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
2912 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
2913 AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
2914 AND CAH.GL_TRANSFER_FLAG = 'Y'
2915 AND CAH.COST_TYPE_ID = P_COST_TYPE
2916 AND CAH.COST_GROUP_ID = P_COST_GROUP
2917 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
2918 AND CAH.ORGANIZATION_ID = P_ORG_ID
2919 AND MMT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
2920 AND MMT.RCV_TRANSACTION_ID is NULL
2921 AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
2922 AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
2923 AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
2924 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
2925 /*Support for LCM*/
2926 AND NOT ( mmt.transaction_action_id = 24
2927 AND nvl(mmt.source_code,'XXX') = 'PACLCMADJ'
2928 );
2929 /*SRW.MESSAGE(112
2930 ,'insert_inv_misc() >> ' || TO_CHAR(SYSDATE
2931 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
2932 INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
2933 (REQUEST_ID
2934 ,PROGRAM_APPLICATION_ID
2935 ,PROGRAM_ID
2936 ,PROGRAM_UPDATE_DATE
2937 ,PERIOD_NAME
2938 ,TRANSACTION_ORGANIZATION_ID
2939 ,ITEM_MASTER_ORGANIZATION_ID
2940 ,ACCRUAL_ACCOUNT_ID
2941 ,ACCRUAL_CODE
2942 ,PO_TRANSACTION_TYPE
2943 ,TRANSACTION_DATE
2944 ,INVOICE_NUM
2945 ,RECEIPT_NUM
2946 ,PO_TRANSACTION_ID
2947 ,INV_TRANSACTION_ID
2948 ,INV_TRANSACTION_TYPE_ID
2949 ,WIP_TRANSACTION_ID
2950 ,WIP_TRANSACTION_TYPE_ID
2951 ,INVENTORY_ITEM_ID
2952 ,PO_UNIT_OF_MEASURE
2953 ,PRIMARY_UNIT_OF_MEASURE
2954 ,TRANSACTION_QUANTITY
2955 ,NET_PO_LINE_QUANTITY
2956 ,PO_HEADER_ID
2957 ,PO_NUM
2958 ,PO_LINE_NUM
2959 ,PO_LINE_ID
2960 ,PO_DISTRIBUTION_ID
2961 ,VENDOR_ID
2962 ,VENDOR_NAME
2963 ,VENDOR_NAME_ALT
2964 ,TRANSACTION_UNIT_PRICE
2965 ,INVOICE_ID
2966 ,INVOICE_LINE_NUM
2967 ,AVG_RECEIPT_PRICE
2968 ,TRANSACTION_AMOUNT
2969 ,LINE_MATCH_ORDER
2970 ,TRANSACTION_SOURCE_CODE
2971 ,WRITE_OFF_FLAG
2972 ,WRITE_OFF_ID
2973 ,DESTINATION_TYPE_CODE
2974 ,REASON_ID
2975 ,COMMENTS
2976 ,LINE_LOCATION_ID
2977 ,COST_TYPE_ID
2978 ,COST_GROUP_ID
2979 ,LEGAL_ENTITY_ID
2980 ,PERIOD_ID)
2981 SELECT
2982 P_CONC_REQUEST_ID,
2983 P_APPL_ID,
2984 P_PROGRAM_ID,
2985 SYSDATE,
2986 NULL,
2987 P_ORG_ID,
2988 P_MASTER_ORG_ID,
2989 CAL.CODE_COMBINATION_ID,
2990 MFL.MEANING,
2991 NULL,
2992 CAH.ACCOUNTING_DATE,
2993 NULL,
2994 NULL,
2995 NULL,
2996 NULL,
2997 NULL,
2998 WTS.TRANSACTION_ID,
2999 WTS.TRANSACTION_TYPE,
3000 NULL,
3001 NULL,
3002 WTS.PRIMARY_UOM,
3003 NVL(WTS.TRANSACTION_QUANTITY
3004 ,0),
3005 NULL,
3006 NULL,
3007 NULL,
3008 NULL,
3009 NULL,
3010 NULL,
3011 NULL,
3012 NULL,
3013 NULL,
3014 ROUND(ABS(NVL(CAL.ACCOUNTED_DR
3015 ,(-1) * CAL.ACCOUNTED_CR) / DECODE(WTS.TRANSACTION_QUANTITY
3016 ,0
3017 ,1
3018 ,WTS.TRANSACTION_QUANTITY))
3019 ,P_EXT_PREC),
3020 NULL,
3021 NULL,
3022 NULL,
3023 ROUND(NVL(CAL.ACCOUNTED_DR
3024 ,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
3025 NULL,
3026 'WIP',
3027 'N',
3028 NULL,
3029 NULL,
3030 NULL,
3031 NULL,
3032 NULL,
3033 P_COST_TYPE,
3034 P_COST_GROUP,
3035 P_LEGAL_ENTITY,
3036 P_PERIOD
3037 FROM
3038 CST_AE_HEADERS CAH,
3039 CST_AE_LINES CAL,
3040 WIP_TRANSACTIONS WTS,
3041 MFG_LOOKUPS MFL,
3042 CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
3043 WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
3044 AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
3045 AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
3046 AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
3047 AND CAH.GL_TRANSFER_FLAG = 'Y'
3048 AND CAH.COST_TYPE_ID = P_COST_TYPE
3049 AND CAH.COST_GROUP_ID = P_COST_GROUP
3050 AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
3051 AND CAH.ORGANIZATION_ID = P_ORG_ID
3052 AND WTS.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
3053 AND WTS.RCV_TRANSACTION_ID is NULL
3054 AND MFL.LOOKUP_CODE = WTS.TRANSACTION_TYPE
3055 AND MFL.LOOKUP_TYPE = 'WIP_TRANSACTION_TYPE'
3056 AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
3057 /*SRW.MESSAGE(114
3058 ,'insert_wip_misc() >> ' || TO_CHAR(SYSDATE
3059 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 /*SRW.MESSAGE(111
3063 ,SQLERRM)*/NULL;
3064 /*SRW.MESSAGE(111
3065 ,'insert_misc_mfg_data() >X ' || TO_CHAR(SYSDATE
3066 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
3067 RAISE;
3068 END INSERT_MISC_MFG_DATA;
3069
3070 FUNCTION GET(APPL_ID IN INTEGER
3071 ,DEP_APPL_ID IN INTEGER
3072 ,STATUS OUT NOCOPY VARCHAR2
3073 ,INDUSTRY OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3074 X0 BOOLEAN;
3075 BEGIN
3076 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_INSTALLATION.GET(:APPL_ID, :DEP_APPL_ID, :STATUS, :INDUSTRY); :X0 := sys.diutil.bool_to_int(X0rv); end;');
3077 STPROC.BIND_I(APPL_ID);
3078 STPROC.BIND_I(DEP_APPL_ID);
3079 STPROC.BIND_O(STATUS);
3080 STPROC.BIND_O(INDUSTRY);
3081 STPROC.BIND_O(X0);
3082 STPROC.EXECUTE;
3083 STPROC.RETRIEVE(3
3084 ,STATUS);
3085 STPROC.RETRIEVE(4
3086 ,INDUSTRY);
3087 STPROC.RETRIEVE(5
3088 ,X0);*/ null;
3089 RETURN X0;
3090 END GET;
3091
3092 FUNCTION GET_APP_INFO(APPLICATION_SHORT_NAME IN VARCHAR2
3093 ,STATUS OUT NOCOPY VARCHAR2
3094 ,INDUSTRY OUT NOCOPY VARCHAR2
3095 ,ORACLE_SCHEMA OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3096 X0 BOOLEAN;
3097 BEGIN
3098 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_INSTALLATION.GET_APP_INFO(:APPLICATION_SHORT_NAME, :STATUS, :INDUSTRY, :ORACLE_SCHEMA); :X0 := sys.diutil.bool_to_int(X0rv); end;');
3099 STPROC.BIND_I(APPLICATION_SHORT_NAME);
3100 STPROC.BIND_O(STATUS);
3101 STPROC.BIND_O(INDUSTRY);
3102 STPROC.BIND_O(ORACLE_SCHEMA);
3103 STPROC.BIND_O(X0);
3104 STPROC.EXECUTE;
3105 STPROC.RETRIEVE(2
3106 ,STATUS);
3107 STPROC.RETRIEVE(3
3108 ,INDUSTRY);
3109 STPROC.RETRIEVE(4
3110 ,ORACLE_SCHEMA);
3111 STPROC.RETRIEVE(5
3112 ,X0);*/ null;
3113 RETURN X0;
3114 END GET_APP_INFO;
3115
3116 FUNCTION GET_APP_INFO_OTHER(APPLICATION_SHORT_NAME IN VARCHAR2
3117 ,TARGET_SCHEMA IN VARCHAR2
3118 ,STATUS OUT NOCOPY VARCHAR2
3119 ,INDUSTRY OUT NOCOPY VARCHAR2
3120 ,ORACLE_SCHEMA OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3121 X0 BOOLEAN;
3122 BEGIN
3123 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_INSTALLATION.GET_APP_INFO_OTHER(:APPLICATION_SHORT_NAME, :TARGET_SCHEMA, :STATUS, :INDUSTRY, :ORACLE_SCHEMA); :X0 := sys.diutil.bool_to_int(X0rv); end;');
3124 STPROC.BIND_I(APPLICATION_SHORT_NAME);
3125 STPROC.BIND_I(TARGET_SCHEMA);
3126 STPROC.BIND_O(STATUS);
3127 STPROC.BIND_O(INDUSTRY);
3128 STPROC.BIND_O(ORACLE_SCHEMA);
3129 STPROC.BIND_O(X0);
3130 STPROC.EXECUTE;
3131 STPROC.RETRIEVE(3
3132 ,STATUS);
3133 STPROC.RETRIEVE(4
3134 ,INDUSTRY);
3135 STPROC.RETRIEVE(5
3136 ,ORACLE_SCHEMA);
3137 STPROC.RETRIEVE(6
3138 ,X0);*/ null;
3139 RETURN X0;
3140 END GET_APP_INFO_OTHER;
3141
3142 END BOM_CSTRACCR_XMLP_PKG;
3143