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