[Home] [Help]
PACKAGE BODY: APPS.ONT_OEXOECCH_XMLP_PKG
Source
1 PACKAGE BODY ONT_OEXOECCH_XMLP_PKG AS
2 /* $Header: OEXOECCHB.pls 120.1 2007/12/25 07:13:51 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
9 EXCEPTION
10 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
11 /*SRW.MESSAGE(1000
12 ,'Failed in BEFORE REPORT trigger')*/NULL;
13 RETURN (FALSE);
14 END;
15 DECLARE
16 L_COMPANY_NAME VARCHAR2(100);
17 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
18 BEGIN
19 SELECT
20 SOB.NAME,
21 SOB.CURRENCY_CODE
22 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
23 FROM
24 GL_SETS_OF_BOOKS SOB,
25 FND_CURRENCIES CUR
26 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
27 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
28 RP_COMPANY_NAME := L_COMPANY_NAME;
29 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 NULL;
33 END;
34 BEGIN
35 /*SRW.REFERENCE(P_VAT_PROFILE)*/NULL;
36 RP_VAT_PROFILE := FND_PROFILE.VALUE(':P_VAT_PROFILE');
37 EXCEPTION
38 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
39 /*SRW.MESSAGE(2000
40 ,'Failed in BEFORE REPORT trigger. FND GETPROFILE - VAT USER_EXIT')*/NULL;
41 END;
42 DECLARE
43 L_REPORT_NAME VARCHAR2(240);
44 BEGIN
45 SELECT
46 CP.USER_CONCURRENT_PROGRAM_NAME
47 INTO L_REPORT_NAME
48 FROM
49 FND_CONCURRENT_PROGRAMS_VL CP,
50 FND_CONCURRENT_REQUESTS CR
51 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
52 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
53 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
54 RP_REPORT_NAME := L_REPORT_NAME;
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN
57 RP_REPORT_NAME := 'Orders on Credit Hold';
58 END;
59 DECLARE
60 L_DATE_HOLD_APPLIED_LOW VARCHAR2(50);
61 L_DATE_HOLD_APPLIED_HIGH VARCHAR2(50);
62 BEGIN
63 IF (P_DATE_HOLD_APPLIED_LOW IS NULL) AND (P_DATE_HOLD_APPLIED_HIGH IS NULL) THEN
64 NULL;
65 ELSE
66 IF P_DATE_HOLD_APPLIED_LOW IS NULL THEN
67 L_DATE_HOLD_APPLIED_LOW := ' ';
68 ELSE
69 L_DATE_HOLD_APPLIED_LOW := SUBSTR(TO_CHAR(P_DATE_HOLD_APPLIED_LOW
70 ,'DD-MON-YYYY')
71 ,1
72 ,18);
73 END IF;
74 IF P_DATE_HOLD_APPLIED_HIGH IS NULL THEN
75 L_DATE_HOLD_APPLIED_HIGH := ' ';
76 ELSE
77 L_DATE_HOLD_APPLIED_HIGH := SUBSTR(TO_CHAR(P_DATE_HOLD_APPLIED_HIGH
78 ,'DD-MON-YYYY')
79 ,1
80 ,18);
81 END IF;
82 RP_DATE_HOLD_APPLIED_RANGE := 'From ' || L_DATE_HOLD_APPLIED_LOW || ' To ' || L_DATE_HOLD_APPLIED_HIGH;
83 END IF;
84 END;
85 DECLARE
86 L_SHIP VARCHAR2(80);
87 L_LOOKUP_TYPE VARCHAR2(80);
88 L_LOOKUP_CODE VARCHAR2(80);
89 BEGIN
90 L_LOOKUP_TYPE := 'CREDIT_RULE_TYPES';
91 L_LOOKUP_CODE := 'SHIPPING';
92 SELECT
93 MEANING
94 INTO L_SHIP
95 FROM
96 OE_LOOKUPS
97 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
98 AND LOOKUP_CODE = L_LOOKUP_CODE;
99 RP_SHIP := L_SHIP;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 RP_SHIP := 'Shipping';
103 END;
104 DECLARE
105 L_ORD VARCHAR2(80);
106 L_LOOKUP_TYPE VARCHAR2(80);
107 L_LOOKUP_CODE VARCHAR2(80);
108 BEGIN
109 L_LOOKUP_TYPE := 'CREDIT_RULE_TYPES';
110 L_LOOKUP_CODE := 'ORDERING';
111 SELECT
112 MEANING
113 INTO L_ORD
114 FROM
115 OE_LOOKUPS
116 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
117 AND LOOKUP_CODE = L_LOOKUP_CODE;
118 RP_ORDER := L_ORD;
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 RP_ORDER := 'Ordering';
122 END;
123 BEGIN
124 RP_CURR_PROFILE := FND_PROFILE.VALUE('ONT_UNIT_PRICE_PRECISION_TYPE');
125 EXCEPTION
126 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
127 /*SRW.MESSAGE(3000
128 ,'Failed in BEFORE REPORT Trigger FND GETPROFILE USER_EXIT')*/NULL;
129 END;
130 DECLARE
131 L_PICK VARCHAR2(80);
132 L_LOOKUP_TYPE VARCHAR2(80);
133 L_LOOKUP_CODE VARCHAR2(80);
134 BEGIN
135 L_LOOKUP_TYPE := 'CREDIT_RULE_TYPES';
136 L_LOOKUP_CODE := 'PICKING';
137 SELECT
138 MEANING
139 INTO L_PICK
140 FROM
141 OE_LOOKUPS
142 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
143 AND LOOKUP_CODE = L_LOOKUP_CODE;
144 RP_PICK := L_PICK;
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 RP_PICK := 'Picking';
148 END;
149 DECLARE
150 L_PACK VARCHAR2(80);
151 L_LOOKUP_TYPE VARCHAR2(80);
152 L_LOOKUP_CODE VARCHAR2(80);
153 BEGIN
154 L_LOOKUP_TYPE := 'CREDIT_RULE_TYPES';
155 L_LOOKUP_CODE := 'PACKING';
156 SELECT
157 MEANING
158 INTO L_PACK
159 FROM
160 OE_LOOKUPS
161 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
162 AND LOOKUP_CODE = L_LOOKUP_CODE;
163 RP_PACK := L_PACK;
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 RP_PACK := 'Packing';
167 END;
168 END;
169 RETURN (TRUE);
170 END BEFOREREPORT;
171
172 FUNCTION AFTERREPORT RETURN BOOLEAN IS
173 BEGIN
174 BEGIN
175 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
176 EXCEPTION
177 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
178 /*SRW.MESSAGE(1
179 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
180 RETURN (FALSE);
181 END;
182 RETURN (TRUE);
183 END AFTERREPORT;
184
185 FUNCTION AFTERPFORM RETURN BOOLEAN IS
186 BEGIN
187 BEGIN
188 IF P_CUSTOMER_NAME IS NOT NULL THEN
189 LP_CUSTOMER_NAME := ' and party.party_name = :p_customer_name';
190 END IF;
191 IF P_CUSTOMER_NUMBER IS NOT NULL THEN
192 LP_CUSTOMER_NUMBER := ' and cust_acct.account_number = :p_customer_number';
193 END IF;
194 IF P_ORDER_NUMBER IS NOT NULL THEN
195 LP_ORDER_NUMBER := ' and h.order_number = :p_order_number';
196 END IF;
197 IF P_CURRENCY_CODE IS NOT NULL THEN
198 LP_CURRENCY_CODE := ' and h.transactional_curr_code = :p_currency_code';
199 END IF;
200 IF (P_DATE_HOLD_APPLIED_LOW IS NOT NULL) AND (P_DATE_HOLD_APPLIED_HIGH IS NOT NULL) THEN
201 LP_DATE_HOLD_APPLIED := 'and (trunc(oh.creation_date) between :p_date_hold_applied_low
202 and :p_date_hold_applied_high) ';
203 ELSIF (P_DATE_HOLD_APPLIED_LOW IS NOT NULL) THEN
204 LP_DATE_HOLD_APPLIED := 'and trunc(oh.creation_date) >= :p_date_hold_applied_low ';
205 ELSIF (P_DATE_HOLD_APPLIED_HIGH IS NOT NULL) THEN
206 LP_DATE_HOLD_APPLIED := 'and trunc(oh.creation_date) <= :p_date_hold_applied_high ';
207 END IF;
208 IF P_ORDER_TYPE IS NOT NULL THEN
209 LP_ORDER_TYPE := ' and ot.transaction_type_id = :p_order_type';
210 SELECT
211 OEOT.NAME
212 INTO L_ORDER_TYPE
213 FROM
214 OE_TRANSACTION_TYPES_TL OEOT
215 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE
216 AND OEOT.LANGUAGE = USERENV('LANG');
217 END IF;
218 END;
219 RETURN (TRUE);
220 END AFTERPFORM;
221
222 FUNCTION C_COMPUTE_AMOUNTSFORMULA(SITE_USE_ID IN NUMBER
223 ,CURRENCY1 IN VARCHAR2
224 ,CUSTOMER_ID IN NUMBER
225 ,ENTRY_OPEN_AR_DAYS IN NUMBER
226 ,ENTRY_OPEN_AR_FLAG IN VARCHAR2
227 ,SHIP_OPEN_AR_DAYS IN NUMBER
228 ,SHIP_OPEN_AR_FLAG IN VARCHAR2
229 ,ENTRY_UNINVOICED_FLAG IN VARCHAR2
230 ,ENTRY_ON_HOLD_FLAG IN VARCHAR2
231 ,SHIP_UNINVOICED_FLAG IN VARCHAR2
232 ,SHIP_ON_HOLD_FLAG IN VARCHAR2
233 ,ENTRY_SHIPPING_INTERVAL IN NUMBER
234 ,SHIP_SHIPPING_INTERVAL IN NUMBER
235 ,ENTRY_RULE_ID IN NUMBER
236 ,SHIP_RULE_ID IN NUMBER
237 ,PICK_OPEN_AR_DAYS IN NUMBER
238 ,PICK_OPEN_AR_FLAG IN VARCHAR2
239 ,PICK_UNINVOICED_FLAG IN VARCHAR2
240 ,PICK_ON_HOLD_FLAG IN VARCHAR2
241 ,PICK_SHIPPING_INTERVAL IN NUMBER
242 ,PICK_RULE_ID IN NUMBER
243 ,PACK_OPEN_AR_DAYS IN NUMBER
244 ,PACK_OPEN_AR_FLAG IN VARCHAR2
245 ,PACK_UNINVOICED_FLAG IN VARCHAR2
246 ,PACK_ON_HOLD_FLAG IN VARCHAR2
247 ,PACK_SHIPPING_INTERVAL IN NUMBER
248 ,PACK_RULE_ID IN NUMBER) RETURN NUMBER IS
249 BEGIN
250 DECLARE
251 L_ORDER_LIMIT NUMBER(17,2);
252 L_TOTAL_LIMIT NUMBER(17,2);
253 L_REC_BAL_CREDIT NUMBER(17,2);
254 L_REC_BAL_SHIP NUMBER(17,2);
255 L_UNINV_ORD_CREDIT NUMBER(17,2);
256 L_UNINV_ORD_SHIP NUMBER(17,2);
257 L_TOTAL1_CREDIT NUMBER(17,2);
258 L_TOTAL3_CREDIT NUMBER(17,2);
259 L_TOTAL1_SHIP NUMBER(17,2);
260 L_TOTAL3_SHIP NUMBER(17,2);
261 L_INCLUDE_RISK_FLAG1 VARCHAR2(1);
262 L_INCLUDE_RISK_FLAG2 VARCHAR2(1);
263 DEBUG NUMBER;
264 L_INCLUDE_RISK_FLAG3 VARCHAR2(1);
265 L_INCLUDE_RISK_FLAG4 VARCHAR2(1);
266 L_REC_BAL_PICK NUMBER(17,2);
267 L_REC_BAL_PACK NUMBER(17,2);
268 L_UNINV_ORD_PICK NUMBER(17,2);
269 L_UNINV_ORD_PACK NUMBER(17,2);
270 L_TOTAL1_PICK NUMBER(17,2);
271 L_TOTAL3_PICK NUMBER(17,2);
272 L_TOTAL1_PACK NUMBER(17,2);
273 L_TOTAL3_PACK NUMBER(17,2);
274 BEGIN
275 /*SRW.REFERENCE(SITE_USE_ID)*/NULL;
276 /*SRW.REFERENCE(CURRENCY1)*/NULL;
277 /*SRW.REFERENCE(CUSTOMER_ID)*/NULL;
278 /*SRW.REFERENCE(ENTRY_OPEN_AR_DAYS)*/NULL;
279 /*SRW.REFERENCE(ENTRY_OPEN_AR_FLAG)*/NULL;
280 /*SRW.REFERENCE(SHIP_OPEN_AR_DAYS)*/NULL;
281 /*SRW.REFERENCE(SHIP_OPEN_AR_FLAG)*/NULL;
282 /*SRW.REFERENCE(ENTRY_UNINVOICED_FLAG)*/NULL;
283 /*SRW.REFERENCE(ENTRY_ON_HOLD_FLAG)*/NULL;
284 /*SRW.REFERENCE(SHIP_UNINVOICED_FLAG)*/NULL;
285 /*SRW.REFERENCE(SHIP_ON_HOLD_FLAG)*/NULL;
286 /*SRW.REFERENCE(ENTRY_SHIPPING_INTERVAL)*/NULL;
287 /*SRW.REFERENCE(SHIP_SHIPPING_INTERVAL)*/NULL;
288 /*SRW.REFERENCE(ENTRY_RULE_ID)*/NULL;
289 /*SRW.REFERENCE(SHIP_RULE_ID)*/NULL;
290 /*SRW.REFERENCE(RP_VAT_PROFILE)*/NULL;
291 /*SRW.REFERENCE(PICK_OPEN_AR_DAYS)*/NULL;
292 /*SRW.REFERENCE(PICK_OPEN_AR_FLAG)*/NULL;
293 /*SRW.REFERENCE(PICK_UNINVOICED_FLAG)*/NULL;
294 /*SRW.REFERENCE(PICK_ON_HOLD_FLAG)*/NULL;
295 /*SRW.REFERENCE(PICK_SHIPPING_INTERVAL)*/NULL;
296 /*SRW.REFERENCE(PICK_RULE_ID)*/NULL;
297 /*SRW.REFERENCE(PACK_OPEN_AR_DAYS)*/NULL;
298 /*SRW.REFERENCE(PACK_OPEN_AR_FLAG)*/NULL;
299 /*SRW.REFERENCE(PACK_UNINVOICED_FLAG)*/NULL;
300 /*SRW.REFERENCE(PACK_ON_HOLD_FLAG)*/NULL;
301 /*SRW.REFERENCE(PACK_SHIPPING_INTERVAL)*/NULL;
302 /*SRW.REFERENCE(PACK_RULE_ID)*/NULL;
303 L_ORDER_LIMIT := 0;
304 L_TOTAL_LIMIT := 0;
305 L_REC_BAL_CREDIT := 0;
306 L_REC_BAL_SHIP := 0;
307 L_UNINV_ORD_CREDIT := 0;
308 L_UNINV_ORD_SHIP := 0;
309 L_TOTAL1_CREDIT := 0;
310 L_TOTAL3_CREDIT := 0;
311 L_TOTAL1_SHIP := 0;
312 L_TOTAL3_SHIP := 0;
313 L_INCLUDE_RISK_FLAG1 := 'N';
314 L_INCLUDE_RISK_FLAG2 := 'N';
315 C_ORDER_LIMIT := 0;
316 C_TOT_ORDER_LIMIT := 0;
317 C_REC_BAL_CREDIT := 0;
318 C_REC_BAL_SHIP := 0;
319 C_UNINV_ORD_CREDIT := 0;
320 C_UNINV_ORD_SHIP := 0;
321 L_INCLUDE_RISK_FLAG3 := 'N';
322 L_INCLUDE_RISK_FLAG4 := 'N';
323 L_REC_BAL_PICK := 0;
324 L_REC_BAL_PACK := 0;
325 L_UNINV_ORD_PICK := 0;
326 L_UNINV_ORD_PACK := 0;
327 L_TOTAL1_PICK := 0;
328 L_TOTAL3_PICK := 0;
329 L_TOTAL1_PACK := 0;
330 L_TOTAL3_PACK := 0;
331 C_REC_BAL_PICK := 0;
332 C_REC_BAL_PACK := 0;
333 C_UNINV_ORD_PICK := 0;
334 C_UNINV_ORD_PACK := 0;
335 IF SITE_USE_ID <> 0 THEN
336 DEBUG := 1;
337 SELECT
338 NVL(SUM(NVL(CPA.OVERALL_CREDIT_LIMIT
339 ,-1) * (100 + CP.TOLERANCE) / 100)
340 ,-1),
341 NVL(SUM(NVL(CPA.TRX_CREDIT_LIMIT
342 ,-1) * (100 + CP.TOLERANCE) / 100)
343 ,-1)
344 INTO L_TOTAL_LIMIT,L_ORDER_LIMIT
345 FROM
346 HZ_CUSTOMER_PROFILES CP,
347 HZ_CUST_PROFILE_AMTS CPA
348 WHERE CP.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
349 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
350 AND CPA.CURRENCY_CODE = CURRENCY1;
351 ELSE
352 DEBUG := 2;
353 SELECT
354 NVL(SUM(NVL(CPA.OVERALL_CREDIT_LIMIT
355 ,-1) * (100 + CP.TOLERANCE) / 100)
356 ,-1),
357 NVL(SUM(NVL(CPA.TRX_CREDIT_LIMIT
358 ,-1) * (100 + CP.TOLERANCE) / 100)
359 ,-1)
360 INTO L_TOTAL_LIMIT,L_ORDER_LIMIT
361 FROM
362 HZ_CUSTOMER_PROFILES CP,
363 HZ_CUST_PROFILE_AMTS CPA
364 WHERE CP.CUST_ACCOUNT_ID = CUSTOMER_ID
365 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
366 AND CPA.CURRENCY_CODE = CURRENCY1
367 AND CP.SITE_USE_ID IS NULL;
368 END IF;
369 C_ORDER_LIMIT := L_ORDER_LIMIT;
370 C_TOT_ORDER_LIMIT := L_TOTAL_LIMIT;
371 IF RP_VAT_PROFILE IS NOT NULL THEN
372 IF ENTRY_RULE_ID <> -1 THEN
373 DEBUG := 3;
374 SELECT
375 INCLUDE_PAYMENTS_AT_RISK_FLAG
376 INTO L_INCLUDE_RISK_FLAG1
377 FROM
378 OE_CREDIT_CHECK_RULES
379 WHERE CREDIT_CHECK_RULE_ID = ENTRY_RULE_ID;
380 END IF;
381 IF SHIP_RULE_ID <> -1 THEN
382 DEBUG := 4;
383 SELECT
384 INCLUDE_PAYMENTS_AT_RISK_FLAG
385 INTO L_INCLUDE_RISK_FLAG2
386 FROM
387 OE_CREDIT_CHECK_RULES
388 WHERE CREDIT_CHECK_RULE_ID = SHIP_RULE_ID;
389 END IF;
390 IF PICK_RULE_ID <> -1 AND PICK_RULE_ID IS NOT NULL THEN
391 DEBUG := 45;
392 SELECT
393 INCLUDE_PAYMENTS_AT_RISK_FLAG
394 INTO L_INCLUDE_RISK_FLAG3
395 FROM
396 OE_CREDIT_CHECK_RULES
397 WHERE CREDIT_CHECK_RULE_ID = PICK_RULE_ID;
398 END IF;
399 IF PACK_RULE_ID <> -1 AND PACK_RULE_ID IS NOT NULL THEN
400 DEBUG := 46;
401 SELECT
402 INCLUDE_PAYMENTS_AT_RISK_FLAG
403 INTO L_INCLUDE_RISK_FLAG4
404 FROM
405 OE_CREDIT_CHECK_RULES
406 WHERE CREDIT_CHECK_RULE_ID = PACK_RULE_ID;
407 END IF;
408 END IF;
409 IF SITE_USE_ID = 0 THEN
410 IF ENTRY_OPEN_AR_FLAG = 'Y' THEN
411 IF ENTRY_OPEN_AR_DAYS IS NULL THEN
412 DEBUG := 5;
413 SELECT
414 NVL(SUM(AMOUNT_DUE_REMAINING)
415 ,0)
416 INTO L_REC_BAL_CREDIT
417 FROM
418 AR_PAYMENT_SCHEDULES
419 WHERE CUSTOMER_ID = CUSTOMER_ID
420 AND INVOICE_CURRENCY_CODE = CURRENCY1
421 AND NVL(RECEIPT_CONFIRMED_FLAG
422 ,'Y') = 'Y';
423 IF L_INCLUDE_RISK_FLAG1 = 'Y' THEN
424 DEBUG := 6;
425 SELECT
426 NVL(SUM(CRH.AMOUNT)
427 ,0) + L_REC_BAL_CREDIT
428 INTO L_REC_BAL_CREDIT
429 FROM
430 AR_CASH_RECEIPT_HISTORY CRH,
431 AR_CASH_RECEIPTS CR
432 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
433 AND NVL(CR.CONFIRMED_FLAG
434 ,'Y') = 'Y'
435 AND CRH.CURRENT_RECORD_FLAG = 'Y'
436 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
437 ,'Y'
438 ,'RISK_ELIMINATED'
439 ,'CLEARED')
440 AND CRH.STATUS <> 'REVERSED'
441 AND CR.CURRENCY_CODE = CURRENCY1
442 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
443 END IF;
444 ELSE
445 DEBUG := 7;
446 SELECT
447 NVL(SUM(AMOUNT_DUE_REMAINING)
448 ,0)
449 INTO L_REC_BAL_CREDIT
450 FROM
451 AR_PAYMENT_SCHEDULES
452 WHERE CUSTOMER_ID = CUSTOMER_ID
453 AND INVOICE_CURRENCY_CODE = CURRENCY1
454 AND NVL(RECEIPT_CONFIRMED_FLAG
455 ,'Y') = 'Y'
456 AND SYSDATE - TRX_DATE > ENTRY_OPEN_AR_DAYS;
457 IF L_INCLUDE_RISK_FLAG1 = 'Y' THEN
458 DEBUG := 8;
459 SELECT
460 NVL(SUM(CRH.AMOUNT)
461 ,0) + L_REC_BAL_CREDIT
462 INTO L_REC_BAL_CREDIT
463 FROM
464 AR_CASH_RECEIPT_HISTORY CRH,
465 AR_CASH_RECEIPTS CR
466 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
467 AND NVL(CR.CONFIRMED_FLAG
468 ,'Y') = 'Y'
469 AND CRH.CURRENT_RECORD_FLAG = 'Y'
470 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
471 ,'Y'
472 ,'RISK_ELIMINATED'
473 ,'CLEARED')
474 AND CRH.STATUS <> 'REVERSED'
475 AND CR.CURRENCY_CODE = CURRENCY1
476 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
477 AND SYSDATE - CR.RECEIPT_DATE > ENTRY_OPEN_AR_DAYS;
478 END IF;
479 END IF;
480 ELSE
481 DEBUG := 9;
482 L_REC_BAL_CREDIT := 0;
483 END IF;
484 ELSE
485 IF ENTRY_OPEN_AR_FLAG = 'Y' THEN
486 IF ENTRY_OPEN_AR_DAYS IS NULL THEN
487 DEBUG := 10;
488 SELECT
489 NVL(SUM(AMOUNT_DUE_REMAINING)
490 ,0)
491 INTO L_REC_BAL_CREDIT
492 FROM
493 AR_PAYMENT_SCHEDULES
494 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
495 AND NVL(RECEIPT_CONFIRMED_FLAG
496 ,'Y') = 'Y'
497 AND INVOICE_CURRENCY_CODE = CURRENCY1;
498 IF L_INCLUDE_RISK_FLAG1 = 'Y' THEN
499 DEBUG := 11;
500 SELECT
501 NVL(SUM(CRH.AMOUNT)
502 ,0) + L_REC_BAL_CREDIT
503 INTO L_REC_BAL_CREDIT
504 FROM
505 AR_CASH_RECEIPT_HISTORY CRH,
506 AR_CASH_RECEIPTS CR
507 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
508 AND NVL(CR.CONFIRMED_FLAG
509 ,'Y') = 'Y'
510 AND CRH.CURRENT_RECORD_FLAG = 'Y'
511 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
512 ,'Y'
513 ,'RISK_ELIMINATED'
514 ,'CLEARED')
515 AND CRH.STATUS <> 'REVERSED'
516 AND CR.CURRENCY_CODE = CURRENCY1
517 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
518 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
519 END IF;
520 ELSE
521 DEBUG := 12;
522 SELECT
523 NVL(SUM(AMOUNT_DUE_REMAINING)
524 ,0)
525 INTO L_REC_BAL_CREDIT
526 FROM
527 AR_PAYMENT_SCHEDULES
528 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
529 AND NVL(RECEIPT_CONFIRMED_FLAG
530 ,'Y') = 'Y'
531 AND INVOICE_CURRENCY_CODE = CURRENCY1
532 AND SYSDATE - TRX_DATE > ENTRY_OPEN_AR_DAYS;
533 IF L_INCLUDE_RISK_FLAG1 = 'Y' THEN
534 DEBUG := 13;
535 SELECT
536 NVL(SUM(CRH.AMOUNT)
537 ,0) + L_REC_BAL_CREDIT
538 INTO L_REC_BAL_CREDIT
539 FROM
540 AR_CASH_RECEIPT_HISTORY CRH,
541 AR_CASH_RECEIPTS CR
542 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
543 AND NVL(CR.CONFIRMED_FLAG
544 ,'Y') = 'Y'
545 AND CRH.CURRENT_RECORD_FLAG = 'Y'
546 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
547 ,'Y'
548 ,'RISK_ELIMINATED'
549 ,'CLEARED')
550 AND CRH.STATUS <> 'REVERSED'
551 AND CR.CURRENCY_CODE = CURRENCY1
552 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
553 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
554 AND SYSDATE - CR.RECEIPT_DATE > ENTRY_OPEN_AR_DAYS;
555 END IF;
556 END IF;
557 ELSE
558 DEBUG := 14;
559 L_REC_BAL_CREDIT := 0;
560 END IF;
561 END IF;
562 C_REC_BAL_CREDIT := L_REC_BAL_CREDIT;
563 IF SITE_USE_ID = 0 THEN
564 IF SHIP_OPEN_AR_FLAG = 'Y' THEN
565 IF SHIP_OPEN_AR_DAYS IS NULL THEN
566 DEBUG := 15;
567 SELECT
568 NVL(SUM(AMOUNT_DUE_REMAINING)
569 ,0)
570 INTO L_REC_BAL_SHIP
571 FROM
572 AR_PAYMENT_SCHEDULES
573 WHERE CUSTOMER_ID = CUSTOMER_ID
574 AND INVOICE_CURRENCY_CODE = CURRENCY1
575 AND NVL(RECEIPT_CONFIRMED_FLAG
576 ,'Y') = 'Y';
577 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
578 DEBUG := 16;
579 SELECT
580 NVL(SUM(CRH.AMOUNT)
581 ,0) + L_REC_BAL_SHIP
582 INTO L_REC_BAL_SHIP
583 FROM
584 AR_CASH_RECEIPT_HISTORY CRH,
585 AR_CASH_RECEIPTS CR
586 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
587 AND NVL(CR.CONFIRMED_FLAG
588 ,'Y') = 'Y'
589 AND CRH.CURRENT_RECORD_FLAG = 'Y'
590 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
591 ,'Y'
592 ,'RISK_ELIMINATED'
593 ,'CLEARED')
594 AND CRH.STATUS <> 'REVERSED'
595 AND CR.CURRENCY_CODE = CURRENCY1
596 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
597 END IF;
598 ELSE
599 DEBUG := 17;
600 SELECT
601 NVL(SUM(AMOUNT_DUE_REMAINING)
602 ,0)
603 INTO L_REC_BAL_SHIP
604 FROM
605 AR_PAYMENT_SCHEDULES
606 WHERE CUSTOMER_ID = CUSTOMER_ID
607 AND INVOICE_CURRENCY_CODE = CURRENCY1
608 AND NVL(RECEIPT_CONFIRMED_FLAG
609 ,'Y') = 'Y'
610 AND SYSDATE - TRX_DATE > SHIP_OPEN_AR_DAYS;
611 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
612 DEBUG := 18;
613 SELECT
614 NVL(SUM(CRH.AMOUNT)
615 ,0) + L_REC_BAL_SHIP
616 INTO L_REC_BAL_SHIP
617 FROM
618 AR_CASH_RECEIPT_HISTORY CRH,
619 AR_CASH_RECEIPTS CR
620 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
621 AND NVL(CR.CONFIRMED_FLAG
622 ,'Y') = 'Y'
623 AND CRH.CURRENT_RECORD_FLAG = 'Y'
624 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
625 ,'Y'
626 ,'RISK_ELIMINATED'
627 ,'CLEARED')
628 AND CRH.STATUS <> 'REVERSED'
629 AND CR.CURRENCY_CODE = CURRENCY1
630 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
631 AND SYSDATE - CR.RECEIPT_DATE > SHIP_OPEN_AR_DAYS;
632 END IF;
633 END IF;
634 ELSE
635 DEBUG := 19;
636 L_REC_BAL_SHIP := 0;
637 END IF;
638 ELSE
639 IF SHIP_OPEN_AR_FLAG = 'Y' THEN
640 IF SHIP_OPEN_AR_DAYS IS NULL THEN
641 DEBUG := 20;
642 SELECT
643 NVL(SUM(AMOUNT_DUE_REMAINING)
644 ,0)
645 INTO L_REC_BAL_SHIP
646 FROM
647 AR_PAYMENT_SCHEDULES
648 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
649 AND NVL(RECEIPT_CONFIRMED_FLAG
650 ,'Y') = 'Y'
651 AND INVOICE_CURRENCY_CODE = CURRENCY1;
652 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
653 DEBUG := 21;
654 SELECT
655 NVL(SUM(CRH.AMOUNT)
656 ,0) + L_REC_BAL_SHIP
657 INTO L_REC_BAL_SHIP
658 FROM
659 AR_CASH_RECEIPT_HISTORY CRH,
660 AR_CASH_RECEIPTS CR
661 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
662 AND NVL(CR.CONFIRMED_FLAG
663 ,'Y') = 'Y'
664 AND CRH.CURRENT_RECORD_FLAG = 'Y'
665 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
666 ,'Y'
667 ,'RISK_ELIMINATED'
668 ,'CLEARED')
669 AND CRH.STATUS <> 'REVERSED'
670 AND CR.CURRENCY_CODE = CURRENCY1
671 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
672 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
673 END IF;
674 ELSE
675 DEBUG := 22;
676 SELECT
677 NVL(SUM(AMOUNT_DUE_REMAINING)
678 ,0)
679 INTO L_REC_BAL_SHIP
680 FROM
681 AR_PAYMENT_SCHEDULES
682 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
683 AND NVL(RECEIPT_CONFIRMED_FLAG
684 ,'Y') = 'Y'
685 AND INVOICE_CURRENCY_CODE = CURRENCY1
686 AND SYSDATE - TRX_DATE > SHIP_OPEN_AR_DAYS;
687 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
688 DEBUG := 23;
689 SELECT
690 NVL(SUM(CRH.AMOUNT)
691 ,0) + L_REC_BAL_SHIP
692 INTO L_REC_BAL_SHIP
693 FROM
694 AR_CASH_RECEIPT_HISTORY CRH,
695 AR_CASH_RECEIPTS CR
696 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
697 AND NVL(CR.CONFIRMED_FLAG
698 ,'Y') = 'Y'
699 AND CRH.CURRENT_RECORD_FLAG = 'Y'
700 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
701 ,'Y'
702 ,'RISK_ELIMINATED'
703 ,'CLEARED')
704 AND CRH.STATUS <> 'REVERSED'
705 AND CR.CURRENCY_CODE = CURRENCY1
706 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
707 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
708 AND SYSDATE - CR.RECEIPT_DATE > SHIP_OPEN_AR_DAYS;
709 END IF;
710 END IF;
711 ELSE
712 DEBUG := 24;
713 L_REC_BAL_SHIP := 0;
714 END IF;
715 END IF;
716 C_REC_BAL_SHIP := L_REC_BAL_SHIP;
717 IF SITE_USE_ID = 0 THEN
718 IF ENTRY_UNINVOICED_FLAG = 'Y' THEN
719 IF ENTRY_ON_HOLD_FLAG = 'Y' THEN
720 DEBUG := 25;
721 SELECT
722 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
723 ,0) * NVL(L.ORDERED_QUANTITY
724 ,0))
725 ,0)
726 INTO L_TOTAL1_CREDIT
727 FROM
728 OE_ORDER_LINES_ALL L,
729 OE_ORDER_HEADERS H,
730 HZ_CUST_SITE_USES_ALL SU,
731 HZ_PARTY_SITES PARTY_SITE,
732 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
733 HZ_LOCATIONS LOC,
734 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
735 WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
736 AND ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
737 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
738 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
739 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
740 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
741 AND NVL(ACCT_SITE.ORG_ID
742 ,-99) = NVL(LOC_ASSIGN.ORG_ID
743 ,-99)
744 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
745 AND L.HEADER_ID = H.HEADER_ID
746 AND L.LINE_CATEGORY_CODE = 'ORDER'
747 AND L.BOOKED_FLAG = 'Y'
748 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
749 ,'X') not in ( 'PARTIAL' , 'YES' )
750 AND DECODE(ENTRY_SHIPPING_INTERVAL
751 ,-1
752 ,TRUNC(SYSDATE)
753 ,NVL(L.REQUEST_DATE
754 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
755 DEBUG := 26;
756 SELECT
757 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
758 ,0) * (NVL(L.ORDERED_QUANTITY
759 ,0) - NVL(L.SHIPPED_QUANTITY
760 ,0)))
761 ,0)
762 INTO L_TOTAL3_CREDIT
763 FROM
764 OE_ORDER_LINES_ALL L,
765 OE_ORDER_HEADERS H,
766 HZ_CUST_SITE_USES_ALL SU,
767 HZ_PARTY_SITES PARTY_SITE,
768 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
769 HZ_LOCATIONS LOC,
770 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
771 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
772 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
773 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
774 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
775 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
776 AND NVL(ACCT_SITE.ORG_ID
777 ,-99) = NVL(LOC_ASSIGN.ORG_ID
778 ,-99)
779 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
780 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
781 AND L.HEADER_ID = H.HEADER_ID
782 AND L.LINE_CATEGORY_CODE = 'ORDER'
783 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
784 AND L.BOOKED_FLAG = 'Y'
785 AND DECODE(ENTRY_SHIPPING_INTERVAL
786 ,-1
787 ,TRUNC(SYSDATE)
788 ,NVL(L.REQUEST_DATE
789 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
790 L_UNINV_ORD_CREDIT := L_TOTAL1_CREDIT + L_TOTAL3_CREDIT;
791 ELSE
792 DEBUG := 27;
793 SELECT
794 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
795 ,0) * NVL(L.ORDERED_QUANTITY
796 ,0))
797 ,0)
798 INTO L_TOTAL1_CREDIT
799 FROM
800 OE_ORDER_LINES_ALL L,
801 OE_ORDER_HEADERS H,
802 HZ_CUST_SITE_USES_ALL SU,
803 HZ_PARTY_SITES PARTY_SITE,
804 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
805 HZ_LOCATIONS LOC,
806 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
807 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
808 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
809 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
810 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
811 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
812 AND NVL(ACCT_SITE.ORG_ID
813 ,-99) = NVL(LOC_ASSIGN.ORG_ID
814 ,-99)
815 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
816 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
817 AND L.HEADER_ID = H.HEADER_ID
818 AND L.LINE_CATEGORY_CODE = 'ORDER'
819 AND L.BOOKED_FLAG = 'Y'
820 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
821 ,'X') not in ( 'PARTIAL' , 'YES' )
822 AND DECODE(ENTRY_SHIPPING_INTERVAL
823 ,-1
824 ,TRUNC(SYSDATE)
825 ,NVL(L.REQUEST_DATE
826 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
827 AND not exists (
828 SELECT
829 'x'
830 FROM
831 OE_ORDER_HOLDS OH
832 WHERE OH.HEADER_ID = H.HEADER_ID
833 AND OH.HOLD_RELEASE_ID is null );
834 DEBUG := 28;
835 SELECT
836 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
837 ,0) * (NVL(L.ORDERED_QUANTITY
838 ,0) - -NVL(L.SHIPPED_QUANTITY
839 ,0)))
840 ,0)
841 INTO L_TOTAL3_CREDIT
842 FROM
843 OE_ORDER_LINES_ALL L,
844 OE_ORDER_HEADERS H,
845 HZ_CUST_SITE_USES_ALL SU,
846 HZ_PARTY_SITES PARTY_SITE,
847 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
848 HZ_LOCATIONS LOC,
849 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
850 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
851 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
852 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
853 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
854 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
855 AND NVL(ACCT_SITE.ORG_ID
856 ,-99) = NVL(LOC_ASSIGN.ORG_ID
857 ,-99)
858 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
859 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
860 AND L.HEADER_ID = H.HEADER_ID
861 AND L.LINE_CATEGORY_CODE = 'ORDER'
862 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
863 AND L.BOOKED_FLAG = 'Y'
864 AND DECODE(ENTRY_SHIPPING_INTERVAL
865 ,-1
866 ,TRUNC(SYSDATE)
867 ,NVL(L.REQUEST_DATE
868 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
869 AND not exists (
870 SELECT
871 'x'
872 FROM
873 OE_ORDER_HOLDS OH
874 WHERE OH.HEADER_ID = H.HEADER_ID
875 AND OH.HOLD_RELEASE_ID is null );
876 L_UNINV_ORD_CREDIT := L_TOTAL1_CREDIT + L_TOTAL3_CREDIT;
877 END IF;
878 ELSE
879 DEBUG := 29;
880 L_UNINV_ORD_CREDIT := 0;
881 END IF;
882 ELSE
883 IF ENTRY_UNINVOICED_FLAG = 'Y' THEN
884 IF ENTRY_ON_HOLD_FLAG = 'Y' THEN
885 DEBUG := 30;
886 SELECT
887 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
888 ,0) * NVL(L.ORDERED_QUANTITY
889 ,0))
890 ,0)
891 INTO L_TOTAL1_CREDIT
892 FROM
893 OE_ORDER_LINES_ALL L,
894 OE_ORDER_HEADERS H,
895 HZ_CUST_SITE_USES_ALL SU,
896 HZ_PARTY_SITES PARTY_SITE,
897 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
898 HZ_LOCATIONS LOC,
899 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
900 WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
901 AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
902 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
903 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
904 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
905 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
906 AND NVL(ACCT_SITE.ORG_ID
907 ,-99) = NVL(LOC_ASSIGN.ORG_ID
908 ,-99)
909 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
910 AND L.HEADER_ID = H.HEADER_ID
911 AND L.LINE_CATEGORY_CODE = 'ORDER'
912 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
913 ,'X') not in ( 'PARTIAL' , 'YES' )
914 AND L.BOOKED_FLAG = 'Y'
915 AND DECODE(ENTRY_SHIPPING_INTERVAL
916 ,-1
917 ,TRUNC(SYSDATE)
918 ,NVL(L.REQUEST_DATE
919 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
920 DEBUG := 31;
921 SELECT
922 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
923 ,0) * (NVL(L.ORDERED_QUANTITY
924 ,0) - -NVL(L.SHIPPED_QUANTITY
925 ,0)))
926 ,0)
927 INTO L_TOTAL3_CREDIT
928 FROM
929 OE_ORDER_LINES_ALL L,
930 OE_ORDER_HEADERS H,
931 HZ_CUST_SITE_USES_ALL SU,
932 HZ_PARTY_SITES PARTY_SITE,
933 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
934 HZ_LOCATIONS LOC,
935 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
936 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
937 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
938 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
939 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
940 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
941 AND NVL(ACCT_SITE.ORG_ID
942 ,-99) = NVL(LOC_ASSIGN.ORG_ID
943 ,-99)
944 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
945 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
946 AND L.HEADER_ID = H.HEADER_ID
947 AND L.LINE_CATEGORY_CODE = 'ORDER'
948 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
949 AND L.BOOKED_FLAG = 'Y'
950 AND DECODE(ENTRY_SHIPPING_INTERVAL
951 ,-1
952 ,TRUNC(SYSDATE)
953 ,NVL(L.REQUEST_DATE
954 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
955 L_UNINV_ORD_CREDIT := L_TOTAL1_CREDIT + L_TOTAL3_CREDIT;
956 ELSE
957 DEBUG := 32;
958 SELECT
959 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
960 ,0) * NVL(L.ORDERED_QUANTITY
961 ,0))
962 ,0)
963 INTO L_TOTAL1_CREDIT
964 FROM
965 OE_ORDER_LINES_ALL L,
966 OE_ORDER_HEADERS H,
967 HZ_CUST_SITE_USES_ALL SU,
968 HZ_PARTY_SITES PARTY_SITE,
969 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
970 HZ_LOCATIONS LOC,
971 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
972 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
973 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
974 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
975 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
976 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
977 AND NVL(ACCT_SITE.ORG_ID
978 ,-99) = NVL(LOC_ASSIGN.ORG_ID
979 ,-99)
980 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
981 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
982 AND L.HEADER_ID = H.HEADER_ID
983 AND L.LINE_CATEGORY_CODE = 'ORDER'
984 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
985 ,'X') not in ( 'PARTIAL' , 'YES' )
986 AND L.BOOKED_FLAG = 'Y'
987 AND DECODE(ENTRY_SHIPPING_INTERVAL
988 ,-1
989 ,TRUNC(SYSDATE)
990 ,NVL(L.REQUEST_DATE
991 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
992 AND not exists (
993 SELECT
994 'x'
995 FROM
996 OE_ORDER_HOLDS OH
997 WHERE OH.HEADER_ID = H.HEADER_ID
998 AND OH.HOLD_RELEASE_ID is null );
999 DEBUG := 33;
1000 SELECT
1001 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1002 ,0) * (NVL(L.ORDERED_QUANTITY
1003 ,0) - -NVL(L.SHIPPED_QUANTITY
1004 ,0)))
1005 ,0)
1006 INTO L_TOTAL3_CREDIT
1007 FROM
1008 OE_ORDER_LINES_ALL L,
1009 OE_ORDER_HEADERS H,
1010 HZ_CUST_SITE_USES_ALL SU,
1011 HZ_PARTY_SITES PARTY_SITE,
1012 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1013 HZ_LOCATIONS LOC,
1014 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1015 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1016 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1017 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1018 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1019 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1020 AND NVL(ACCT_SITE.ORG_ID
1021 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1022 ,-99)
1023 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1024 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1025 AND L.HEADER_ID = H.HEADER_ID
1026 AND L.LINE_CATEGORY_CODE = 'ORDER'
1027 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1028 AND L.BOOKED_FLAG = 'Y'
1029 AND DECODE(ENTRY_SHIPPING_INTERVAL
1030 ,-1
1031 ,TRUNC(SYSDATE)
1032 ,NVL(L.REQUEST_DATE
1033 ,H.REQUEST_DATE) + ENTRY_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1034 AND not exists (
1035 SELECT
1036 'x'
1037 FROM
1038 OE_ORDER_HOLDS OH
1039 WHERE OH.HEADER_ID = H.HEADER_ID
1040 AND OH.HOLD_RELEASE_ID is null );
1041 L_UNINV_ORD_CREDIT := L_TOTAL1_CREDIT + L_TOTAL3_CREDIT;
1042 END IF;
1043 ELSE
1044 DEBUG := 34;
1045 L_UNINV_ORD_CREDIT := 0;
1046 END IF;
1047 END IF;
1048 C_UNINV_ORD_CREDIT := L_UNINV_ORD_CREDIT;
1049 IF SITE_USE_ID = 0 THEN
1050 IF SHIP_UNINVOICED_FLAG = 'Y' THEN
1051 IF SHIP_ON_HOLD_FLAG = 'Y' THEN
1052 DEBUG := 35;
1053 SELECT
1054 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1055 ,0) * NVL(L.ORDERED_QUANTITY
1056 ,0))
1057 ,0)
1058 INTO L_TOTAL1_SHIP
1059 FROM
1060 OE_ORDER_LINES_ALL L,
1061 OE_ORDER_HEADERS H,
1062 HZ_CUST_SITE_USES_ALL SU,
1063 HZ_PARTY_SITES PARTY_SITE,
1064 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1065 HZ_LOCATIONS LOC,
1066 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1067 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1068 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1069 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1070 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1071 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1072 AND NVL(ACCT_SITE.ORG_ID
1073 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1074 ,-99)
1075 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1076 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1077 AND L.HEADER_ID = H.HEADER_ID
1078 AND L.LINE_CATEGORY_CODE = 'ORDER'
1079 AND L.BOOKED_FLAG = 'Y'
1080 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1081 ,'X') not in ( 'PARTIAL' , 'YES' )
1082 AND DECODE(SHIP_SHIPPING_INTERVAL
1083 ,-1
1084 ,TRUNC(SYSDATE)
1085 ,NVL(L.REQUEST_DATE
1086 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1087 DEBUG := 36;
1088 SELECT
1089 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1090 ,0) * (NVL(L.ORDERED_QUANTITY
1091 ,0) - NVL(L.SHIPPED_QUANTITY
1092 ,0)))
1093 ,0)
1094 INTO L_TOTAL3_SHIP
1095 FROM
1096 OE_ORDER_LINES_ALL L,
1097 OE_ORDER_HEADERS H,
1098 HZ_CUST_SITE_USES_ALL SU,
1099 HZ_PARTY_SITES PARTY_SITE,
1100 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1101 HZ_LOCATIONS LOC,
1102 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1103 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1104 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1105 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1106 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1107 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1108 AND NVL(ACCT_SITE.ORG_ID
1109 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1110 ,-99)
1111 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1112 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1113 AND L.HEADER_ID = H.HEADER_ID
1114 AND L.LINE_CATEGORY_CODE = 'ORDER'
1115 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1116 AND L.BOOKED_FLAG = 'Y'
1117 AND DECODE(SHIP_SHIPPING_INTERVAL
1118 ,-1
1119 ,TRUNC(SYSDATE)
1120 ,NVL(L.REQUEST_DATE
1121 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1122 L_UNINV_ORD_SHIP := L_TOTAL1_SHIP + L_TOTAL3_SHIP;
1123 ELSE
1124 DEBUG := 37;
1125 SELECT
1126 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1127 ,0) * NVL(L.ORDERED_QUANTITY
1128 ,0))
1129 ,0)
1130 INTO L_TOTAL1_SHIP
1131 FROM
1132 OE_ORDER_LINES_ALL L,
1133 OE_ORDER_HEADERS H,
1134 HZ_CUST_SITE_USES_ALL SU,
1135 HZ_PARTY_SITES PARTY_SITE,
1136 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1137 HZ_LOCATIONS LOC,
1138 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1139 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1140 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1141 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1142 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1143 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1144 AND NVL(ACCT_SITE.ORG_ID
1145 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1146 ,-99)
1147 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1148 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1149 AND L.HEADER_ID = H.HEADER_ID
1150 AND L.LINE_CATEGORY_CODE = 'ORDER'
1151 AND L.BOOKED_FLAG = 'Y'
1152 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1153 ,'X') not in ( 'PARTIAL' , 'YES' )
1154 AND DECODE(SHIP_SHIPPING_INTERVAL
1155 ,-1
1156 ,TRUNC(SYSDATE)
1157 ,NVL(L.REQUEST_DATE
1158 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1159 AND not exists (
1160 SELECT
1161 'x'
1162 FROM
1163 OE_ORDER_HOLDS OH
1164 WHERE OH.HEADER_ID = H.HEADER_ID
1165 AND OH.HOLD_RELEASE_ID is null );
1166 DEBUG := 38;
1167 SELECT
1168 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1169 ,0) * (NVL(L.ORDERED_QUANTITY
1170 ,0) - NVL(L.SHIPPED_QUANTITY
1171 ,0)))
1172 ,0)
1173 INTO L_TOTAL3_SHIP
1174 FROM
1175 OE_ORDER_LINES_ALL L,
1176 OE_ORDER_HEADERS H,
1177 HZ_CUST_SITE_USES_ALL SU,
1178 HZ_PARTY_SITES PARTY_SITE,
1179 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1180 HZ_LOCATIONS LOC,
1181 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1182 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1183 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1184 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1185 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1186 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1187 AND NVL(ACCT_SITE.ORG_ID
1188 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1189 ,-99)
1190 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1191 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1192 AND L.HEADER_ID = H.HEADER_ID
1193 AND L.LINE_CATEGORY_CODE = 'ORDER'
1194 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1195 AND L.BOOKED_FLAG = 'Y'
1196 AND DECODE(SHIP_SHIPPING_INTERVAL
1197 ,-1
1198 ,TRUNC(SYSDATE)
1199 ,NVL(L.REQUEST_DATE
1200 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1201 AND not exists (
1202 SELECT
1203 'x'
1204 FROM
1205 OE_ORDER_HOLDS OH
1206 WHERE OH.HEADER_ID = H.HEADER_ID
1207 AND OH.HOLD_RELEASE_ID is null );
1208 L_UNINV_ORD_SHIP := L_TOTAL1_SHIP + L_TOTAL3_SHIP;
1209 END IF;
1210 ELSE
1211 DEBUG := 39;
1212 L_UNINV_ORD_SHIP := 0;
1213 END IF;
1214 ELSE
1215 IF SHIP_UNINVOICED_FLAG = 'Y' THEN
1216 IF SHIP_ON_HOLD_FLAG = 'Y' THEN
1217 DEBUG := 40;
1218 SELECT
1219 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1220 ,0) * NVL(L.ORDERED_QUANTITY
1221 ,0))
1222 ,0)
1223 INTO L_TOTAL1_SHIP
1224 FROM
1225 OE_ORDER_LINES_ALL L,
1226 OE_ORDER_HEADERS H,
1227 HZ_CUST_SITE_USES_ALL SU,
1228 HZ_PARTY_SITES PARTY_SITE,
1229 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1230 HZ_LOCATIONS LOC,
1231 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1232 WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
1233 AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1234 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1235 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1236 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1237 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1238 AND NVL(ACCT_SITE.ORG_ID
1239 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1240 ,-99)
1241 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1242 AND L.HEADER_ID = H.HEADER_ID
1243 AND L.LINE_CATEGORY_CODE = 'ORDER'
1244 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1245 ,'X') not in ( 'PARTIAL' , 'YES' )
1246 AND L.BOOKED_FLAG = 'Y'
1247 AND DECODE(SHIP_SHIPPING_INTERVAL
1248 ,-1
1249 ,TRUNC(SYSDATE)
1250 ,NVL(L.REQUEST_DATE
1251 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1252 DEBUG := 41;
1253 SELECT
1254 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1255 ,0) * (NVL(L.ORDERED_QUANTITY
1256 ,0) - NVL(L.SHIPPED_QUANTITY
1257 ,0)))
1258 ,0)
1259 INTO L_TOTAL3_SHIP
1260 FROM
1261 OE_ORDER_LINES_ALL L,
1262 OE_ORDER_HEADERS H,
1263 HZ_CUST_SITE_USES_ALL SU,
1264 HZ_PARTY_SITES PARTY_SITE,
1265 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1266 HZ_LOCATIONS LOC,
1267 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1268 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1269 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1270 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1271 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1272 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1273 AND NVL(ACCT_SITE.ORG_ID
1274 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1275 ,-99)
1276 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1277 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1278 AND L.HEADER_ID = H.HEADER_ID
1279 AND L.LINE_CATEGORY_CODE = 'ORDER'
1280 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1281 AND L.BOOKED_FLAG = 'Y'
1282 AND DECODE(SHIP_SHIPPING_INTERVAL
1283 ,-1
1284 ,TRUNC(SYSDATE)
1285 ,NVL(L.REQUEST_DATE
1286 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1287 L_UNINV_ORD_SHIP := L_TOTAL1_SHIP + L_TOTAL3_SHIP;
1288 ELSE
1289 DEBUG := 42;
1290 SELECT
1291 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1292 ,0) * NVL(L.ORDERED_QUANTITY
1293 ,0))
1294 ,0)
1295 INTO L_TOTAL1_SHIP
1296 FROM
1297 OE_ORDER_LINES_ALL L,
1298 OE_ORDER_HEADERS H,
1299 HZ_CUST_SITE_USES_ALL SU,
1300 HZ_PARTY_SITES PARTY_SITE,
1301 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1302 HZ_LOCATIONS LOC,
1303 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1304 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1305 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1306 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1307 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1308 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1309 AND NVL(ACCT_SITE.ORG_ID
1310 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1311 ,-99)
1312 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1313 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1314 AND L.HEADER_ID = H.HEADER_ID
1315 AND L.LINE_CATEGORY_CODE = 'ORDER'
1316 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1317 ,'X') not in ( 'PARTIAL' , 'YES' )
1318 AND L.BOOKED_FLAG = 'Y'
1319 AND DECODE(SHIP_SHIPPING_INTERVAL
1320 ,-1
1321 ,TRUNC(SYSDATE)
1322 ,NVL(L.REQUEST_DATE
1323 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1324 AND not exists (
1325 SELECT
1326 'x'
1327 FROM
1328 OE_ORDER_HOLDS OH
1329 WHERE OH.HEADER_ID = H.HEADER_ID
1330 AND OH.HOLD_RELEASE_ID is null );
1331 DEBUG := 43;
1332 SELECT
1333 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1334 ,0) * (NVL(L.ORDERED_QUANTITY
1335 ,0) - NVL(L.SHIPPED_QUANTITY
1336 ,0)))
1337 ,0)
1338 INTO L_TOTAL3_SHIP
1339 FROM
1340 OE_ORDER_LINES_ALL L,
1341 OE_ORDER_HEADERS H,
1342 HZ_CUST_SITE_USES_ALL SU,
1343 HZ_PARTY_SITES PARTY_SITE,
1344 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1345 HZ_LOCATIONS LOC,
1346 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1347 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1348 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1349 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1350 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1351 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1352 AND NVL(ACCT_SITE.ORG_ID
1353 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1354 ,-99)
1355 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1356 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1357 AND L.HEADER_ID = H.HEADER_ID
1358 AND L.LINE_CATEGORY_CODE = 'ORDER'
1359 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1360 AND L.BOOKED_FLAG = 'Y'
1361 AND DECODE(SHIP_SHIPPING_INTERVAL
1362 ,-1
1363 ,TRUNC(SYSDATE)
1364 ,NVL(L.REQUEST_DATE
1365 ,H.REQUEST_DATE) + SHIP_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1366 AND not exists (
1367 SELECT
1368 'x'
1369 FROM
1370 OE_ORDER_HOLDS OH
1371 WHERE OH.HEADER_ID = H.HEADER_ID
1372 AND OH.HOLD_RELEASE_ID is null );
1373 L_UNINV_ORD_SHIP := L_TOTAL1_SHIP + L_TOTAL3_SHIP;
1374 END IF;
1375 ELSE
1376 DEBUG := 44;
1377 L_UNINV_ORD_SHIP := 0;
1378 END IF;
1379 END IF;
1380 C_UNINV_ORD_PACK := L_UNINV_ORD_PACK;
1381 IF SITE_USE_ID = 0 THEN
1382 IF PICK_OPEN_AR_FLAG = 'Y' THEN
1383 IF PICK_OPEN_AR_DAYS IS NULL THEN
1384 DEBUG := 47;
1385 SELECT
1386 NVL(SUM(AMOUNT_DUE_REMAINING)
1387 ,0)
1388 INTO L_REC_BAL_PICK
1389 FROM
1390 AR_PAYMENT_SCHEDULES
1391 WHERE CUSTOMER_ID = CUSTOMER_ID
1392 AND INVOICE_CURRENCY_CODE = CURRENCY1
1393 AND NVL(RECEIPT_CONFIRMED_FLAG
1394 ,'Y') = 'Y';
1395 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1396 DEBUG := 48;
1397 SELECT
1398 NVL(SUM(CRH.AMOUNT)
1399 ,0) + L_REC_BAL_PICK
1400 INTO L_REC_BAL_PICK
1401 FROM
1402 AR_CASH_RECEIPT_HISTORY CRH,
1403 AR_CASH_RECEIPTS CR
1404 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1405 AND NVL(CR.CONFIRMED_FLAG
1406 ,'Y') = 'Y'
1407 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1408 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1409 ,'Y'
1410 ,'RISK_ELIMINATED'
1411 ,'CLEARED')
1412 AND CRH.STATUS <> 'REVERSED'
1413 AND CR.CURRENCY_CODE = CURRENCY1
1414 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
1415 END IF;
1416 ELSE
1417 DEBUG := 49;
1418 SELECT
1419 NVL(SUM(AMOUNT_DUE_REMAINING)
1420 ,0)
1421 INTO L_REC_BAL_PICK
1422 FROM
1423 AR_PAYMENT_SCHEDULES
1424 WHERE CUSTOMER_ID = CUSTOMER_ID
1425 AND INVOICE_CURRENCY_CODE = CURRENCY1
1426 AND NVL(RECEIPT_CONFIRMED_FLAG
1427 ,'Y') = 'Y'
1428 AND SYSDATE - TRX_DATE > PICK_OPEN_AR_DAYS;
1429 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1430 DEBUG := 50;
1431 SELECT
1432 NVL(SUM(CRH.AMOUNT)
1433 ,0) + L_REC_BAL_PICK
1434 INTO L_REC_BAL_PICK
1435 FROM
1436 AR_CASH_RECEIPT_HISTORY CRH,
1437 AR_CASH_RECEIPTS CR
1438 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1439 AND NVL(CR.CONFIRMED_FLAG
1440 ,'Y') = 'Y'
1441 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1442 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1443 ,'Y'
1444 ,'RISK_ELIMINATED'
1445 ,'CLEARED')
1446 AND CRH.STATUS <> 'REVERSED'
1447 AND CR.CURRENCY_CODE = CURRENCY1
1448 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1449 AND SYSDATE - CR.RECEIPT_DATE > PICK_OPEN_AR_DAYS;
1450 END IF;
1451 END IF;
1452 ELSE
1453 DEBUG := 51;
1454 L_REC_BAL_PICK := 0;
1455 END IF;
1456 ELSE
1457 IF PICK_OPEN_AR_FLAG = 'Y' THEN
1458 IF PICK_OPEN_AR_DAYS IS NULL THEN
1459 DEBUG := 52;
1460 SELECT
1461 NVL(SUM(AMOUNT_DUE_REMAINING)
1462 ,0)
1463 INTO L_REC_BAL_PICK
1464 FROM
1465 AR_PAYMENT_SCHEDULES
1466 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1467 AND NVL(RECEIPT_CONFIRMED_FLAG
1468 ,'Y') = 'Y'
1469 AND INVOICE_CURRENCY_CODE = CURRENCY1;
1470 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1471 DEBUG := 53;
1472 SELECT
1473 NVL(SUM(CRH.AMOUNT)
1474 ,0) + L_REC_BAL_PICK
1475 INTO L_REC_BAL_PICK
1476 FROM
1477 AR_CASH_RECEIPT_HISTORY CRH,
1478 AR_CASH_RECEIPTS CR
1479 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1480 AND NVL(CR.CONFIRMED_FLAG
1481 ,'Y') = 'Y'
1482 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1483 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1484 ,'Y'
1485 ,'RISK_ELIMINATED'
1486 ,'CLEARED')
1487 AND CRH.STATUS <> 'REVERSED'
1488 AND CR.CURRENCY_CODE = CURRENCY1
1489 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1490 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
1491 END IF;
1492 ELSE
1493 DEBUG := 54;
1494 SELECT
1495 NVL(SUM(AMOUNT_DUE_REMAINING)
1496 ,0)
1497 INTO L_REC_BAL_PICK
1498 FROM
1499 AR_PAYMENT_SCHEDULES
1500 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1501 AND NVL(RECEIPT_CONFIRMED_FLAG
1502 ,'Y') = 'Y'
1503 AND INVOICE_CURRENCY_CODE = CURRENCY1
1504 AND SYSDATE - TRX_DATE > PICK_OPEN_AR_DAYS;
1505 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1506 DEBUG := 55;
1507 SELECT
1508 NVL(SUM(CRH.AMOUNT)
1509 ,0) + L_REC_BAL_PICK
1510 INTO L_REC_BAL_PICK
1511 FROM
1512 AR_CASH_RECEIPT_HISTORY CRH,
1513 AR_CASH_RECEIPTS CR
1514 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1515 AND NVL(CR.CONFIRMED_FLAG
1516 ,'Y') = 'Y'
1517 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1518 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1519 ,'Y'
1520 ,'RISK_ELIMINATED'
1521 ,'CLEARED')
1522 AND CRH.STATUS <> 'REVERSED'
1523 AND CR.CURRENCY_CODE = CURRENCY1
1524 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1525 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1526 AND SYSDATE - CR.RECEIPT_DATE > PICK_OPEN_AR_DAYS;
1527 END IF;
1528 END IF;
1529 ELSE
1530 DEBUG := 56;
1531 L_REC_BAL_PICK := 0;
1532 END IF;
1533 END IF;
1534 C_REC_BAL_PICK := L_REC_BAL_PICK;
1535 IF SITE_USE_ID = 0 THEN
1536 IF PACK_OPEN_AR_FLAG = 'Y' THEN
1537 IF PACK_OPEN_AR_DAYS IS NULL THEN
1538 DEBUG := 57;
1539 SELECT
1540 NVL(SUM(AMOUNT_DUE_REMAINING)
1541 ,0)
1542 INTO L_REC_BAL_PACK
1543 FROM
1544 AR_PAYMENT_SCHEDULES
1545 WHERE CUSTOMER_ID = CUSTOMER_ID
1546 AND INVOICE_CURRENCY_CODE = CURRENCY1
1547 AND NVL(RECEIPT_CONFIRMED_FLAG
1548 ,'Y') = 'Y';
1549 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1550 DEBUG := 58;
1551 SELECT
1552 NVL(SUM(CRH.AMOUNT)
1553 ,0) + L_REC_BAL_PACK
1554 INTO L_REC_BAL_PACK
1555 FROM
1556 AR_CASH_RECEIPT_HISTORY CRH,
1557 AR_CASH_RECEIPTS CR
1558 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1559 AND NVL(CR.CONFIRMED_FLAG
1560 ,'Y') = 'Y'
1561 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1562 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1563 ,'Y'
1564 ,'RISK_ELIMINATED'
1565 ,'CLEARED')
1566 AND CRH.STATUS <> 'REVERSED'
1567 AND CR.CURRENCY_CODE = CURRENCY1
1568 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID;
1569 END IF;
1570 ELSE
1571 DEBUG := 59;
1572 SELECT
1573 NVL(SUM(AMOUNT_DUE_REMAINING)
1574 ,0)
1575 INTO L_REC_BAL_PACK
1576 FROM
1577 AR_PAYMENT_SCHEDULES
1578 WHERE CUSTOMER_ID = CUSTOMER_ID
1579 AND INVOICE_CURRENCY_CODE = CURRENCY1
1580 AND NVL(RECEIPT_CONFIRMED_FLAG
1581 ,'Y') = 'Y'
1582 AND SYSDATE - TRX_DATE > PACK_OPEN_AR_DAYS;
1583 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1584 DEBUG := 60;
1585 SELECT
1586 NVL(SUM(CRH.AMOUNT)
1587 ,0) + L_REC_BAL_PACK
1588 INTO L_REC_BAL_PACK
1589 FROM
1590 AR_CASH_RECEIPT_HISTORY CRH,
1591 AR_CASH_RECEIPTS CR
1592 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1593 AND NVL(CR.CONFIRMED_FLAG
1594 ,'Y') = 'Y'
1595 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1596 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1597 ,'Y'
1598 ,'RISK_ELIMINATED'
1599 ,'CLEARED')
1600 AND CRH.STATUS <> 'REVERSED'
1601 AND CR.CURRENCY_CODE = CURRENCY1
1602 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1603 AND SYSDATE - CR.RECEIPT_DATE > PACK_OPEN_AR_DAYS;
1604 END IF;
1605 END IF;
1606 ELSE
1607 DEBUG := 61;
1608 L_REC_BAL_PACK := 0;
1609 END IF;
1610 ELSE
1611 IF PACK_OPEN_AR_FLAG = 'Y' THEN
1612 IF PACK_OPEN_AR_DAYS IS NULL THEN
1613 DEBUG := 62;
1614 SELECT
1615 NVL(SUM(AMOUNT_DUE_REMAINING)
1616 ,0)
1617 INTO L_REC_BAL_PACK
1618 FROM
1619 AR_PAYMENT_SCHEDULES
1620 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1621 AND NVL(RECEIPT_CONFIRMED_FLAG
1622 ,'Y') = 'Y'
1623 AND INVOICE_CURRENCY_CODE = CURRENCY1;
1624 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1625 DEBUG := 63;
1626 SELECT
1627 NVL(SUM(CRH.AMOUNT)
1628 ,0) + L_REC_BAL_PACK
1629 INTO L_REC_BAL_PACK
1630 FROM
1631 AR_CASH_RECEIPT_HISTORY CRH,
1632 AR_CASH_RECEIPTS CR
1633 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1634 AND NVL(CR.CONFIRMED_FLAG
1635 ,'Y') = 'Y'
1636 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1637 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1638 ,'Y'
1639 ,'RISK_ELIMINATED'
1640 ,'CLEARED')
1641 AND CRH.STATUS <> 'REVERSED'
1642 AND CR.CURRENCY_CODE = CURRENCY1
1643 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1644 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID;
1645 END IF;
1646 ELSE
1647 DEBUG := 64;
1648 SELECT
1649 NVL(SUM(AMOUNT_DUE_REMAINING)
1650 ,0)
1651 INTO L_REC_BAL_PACK
1652 FROM
1653 AR_PAYMENT_SCHEDULES
1654 WHERE CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1655 AND NVL(RECEIPT_CONFIRMED_FLAG
1656 ,'Y') = 'Y'
1657 AND INVOICE_CURRENCY_CODE = CURRENCY1
1658 AND SYSDATE - TRX_DATE > PACK_OPEN_AR_DAYS;
1659 IF L_INCLUDE_RISK_FLAG2 = 'Y' THEN
1660 DEBUG := 65;
1661 SELECT
1662 NVL(SUM(CRH.AMOUNT)
1663 ,0) + L_REC_BAL_PACK
1664 INTO L_REC_BAL_PACK
1665 FROM
1666 AR_CASH_RECEIPT_HISTORY CRH,
1667 AR_CASH_RECEIPTS CR
1668 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1669 AND NVL(CR.CONFIRMED_FLAG
1670 ,'Y') = 'Y'
1671 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1672 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG
1673 ,'Y'
1674 ,'RISK_ELIMINATED'
1675 ,'CLEARED')
1676 AND CRH.STATUS <> 'REVERSED'
1677 AND CR.CURRENCY_CODE = CURRENCY1
1678 AND CR.PAY_FROM_CUSTOMER = CUSTOMER_ID
1679 AND CR.CUSTOMER_SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1680 AND SYSDATE - CR.RECEIPT_DATE > PACK_OPEN_AR_DAYS;
1681 END IF;
1682 END IF;
1683 ELSE
1684 DEBUG := 66;
1685 L_REC_BAL_PACK := 0;
1686 END IF;
1687 END IF;
1688 C_REC_BAL_PACK := L_REC_BAL_PACK;
1689 IF SITE_USE_ID = 0 THEN
1690 IF PICK_UNINVOICED_FLAG = 'Y' THEN
1691 IF PICK_ON_HOLD_FLAG = 'Y' THEN
1692 DEBUG := 67;
1693 SELECT
1694 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1695 ,0) * NVL(L.ORDERED_QUANTITY
1696 ,0))
1697 ,0)
1698 INTO L_TOTAL1_PICK
1699 FROM
1700 OE_ORDER_LINES_ALL L,
1701 OE_ORDER_HEADERS H,
1702 HZ_CUST_SITE_USES_ALL SU,
1703 HZ_PARTY_SITES PARTY_SITE,
1704 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1705 HZ_LOCATIONS LOC,
1706 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1707 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1708 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1709 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1710 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1711 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1712 AND NVL(ACCT_SITE.ORG_ID
1713 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1714 ,-99)
1715 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1716 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1717 AND L.HEADER_ID = H.HEADER_ID
1718 AND L.LINE_CATEGORY_CODE = 'ORDER'
1719 AND L.BOOKED_FLAG = 'Y'
1720 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1721 ,'X') not in ( 'PARTIAL' , 'YES' )
1722 AND DECODE(PICK_SHIPPING_INTERVAL
1723 ,-1
1724 ,TRUNC(SYSDATE)
1725 ,NVL(L.REQUEST_DATE
1726 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1727 DEBUG := 68;
1728 SELECT
1729 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1730 ,0) * (NVL(L.ORDERED_QUANTITY
1731 ,0) - NVL(L.SHIPPED_QUANTITY
1732 ,0)))
1733 ,0)
1734 INTO L_TOTAL3_PICK
1735 FROM
1736 OE_ORDER_LINES_ALL L,
1737 OE_ORDER_HEADERS H,
1738 HZ_CUST_SITE_USES_ALL SU,
1739 HZ_PARTY_SITES PARTY_SITE,
1740 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1741 HZ_LOCATIONS LOC,
1742 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1743 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1744 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1745 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1746 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1747 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1748 AND NVL(ACCT_SITE.ORG_ID
1749 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1750 ,-99)
1751 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1752 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1753 AND L.HEADER_ID = H.HEADER_ID
1754 AND L.LINE_CATEGORY_CODE = 'ORDER'
1755 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1756 AND L.BOOKED_FLAG = 'Y'
1757 AND DECODE(PICK_SHIPPING_INTERVAL
1758 ,-1
1759 ,TRUNC(SYSDATE)
1760 ,NVL(L.REQUEST_DATE
1761 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1762 L_UNINV_ORD_PICK := L_TOTAL1_PICK + L_TOTAL3_PICK;
1763 ELSE
1764 DEBUG := 69;
1765 SELECT
1766 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1767 ,0) * NVL(L.ORDERED_QUANTITY
1768 ,0))
1769 ,0)
1770 INTO L_TOTAL1_PICK
1771 FROM
1772 OE_ORDER_LINES_ALL L,
1773 OE_ORDER_HEADERS H,
1774 HZ_CUST_SITE_USES_ALL SU,
1775 HZ_PARTY_SITES PARTY_SITE,
1776 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1777 HZ_LOCATIONS LOC,
1778 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1779 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1780 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1781 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1782 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1783 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1784 AND NVL(ACCT_SITE.ORG_ID
1785 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1786 ,-99)
1787 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1788 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1789 AND L.HEADER_ID = H.HEADER_ID
1790 AND L.LINE_CATEGORY_CODE = 'ORDER'
1791 AND L.BOOKED_FLAG = 'Y'
1792 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1793 ,'X') not in ( 'PARTIAL' , 'YES' )
1794 AND DECODE(PICK_SHIPPING_INTERVAL
1795 ,-1
1796 ,TRUNC(SYSDATE)
1797 ,NVL(L.REQUEST_DATE
1798 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1799 AND not exists (
1800 SELECT
1801 'x'
1802 FROM
1803 OE_ORDER_HOLDS OH
1804 WHERE OH.HEADER_ID = H.HEADER_ID
1805 AND OH.HOLD_RELEASE_ID is null );
1806 DEBUG := 70;
1807 SELECT
1808 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1809 ,0) * (NVL(L.ORDERED_QUANTITY
1810 ,0) - NVL(L.SHIPPED_QUANTITY
1811 ,0)))
1812 ,0)
1813 INTO L_TOTAL3_PICK
1814 FROM
1815 OE_ORDER_LINES_ALL L,
1816 OE_ORDER_HEADERS H,
1817 HZ_CUST_SITE_USES_ALL SU,
1818 HZ_PARTY_SITES PARTY_SITE,
1819 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1820 HZ_LOCATIONS LOC,
1821 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1822 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
1823 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1824 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1825 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1826 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1827 AND NVL(ACCT_SITE.ORG_ID
1828 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1829 ,-99)
1830 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1831 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1832 AND L.HEADER_ID = H.HEADER_ID
1833 AND L.LINE_CATEGORY_CODE = 'ORDER'
1834 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1835 AND L.BOOKED_FLAG = 'Y'
1836 AND DECODE(PICK_SHIPPING_INTERVAL
1837 ,-1
1838 ,TRUNC(SYSDATE)
1839 ,NVL(L.REQUEST_DATE
1840 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1841 AND not exists (
1842 SELECT
1843 'x'
1844 FROM
1845 OE_ORDER_HOLDS OH
1846 WHERE OH.HEADER_ID = H.HEADER_ID
1847 AND OH.HOLD_RELEASE_ID is null );
1848 L_UNINV_ORD_PICK := L_TOTAL1_PICK + L_TOTAL3_PICK;
1849 END IF;
1850 ELSE
1851 DEBUG := 71;
1852 L_UNINV_ORD_PICK := 0;
1853 END IF;
1854 ELSE
1855 IF PICK_UNINVOICED_FLAG = 'Y' THEN
1856 IF PICK_ON_HOLD_FLAG = 'Y' THEN
1857 DEBUG := 72;
1858 SELECT
1859 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1860 ,0) * NVL(L.ORDERED_QUANTITY
1861 ,0))
1862 ,0)
1863 INTO L_TOTAL1_PICK
1864 FROM
1865 OE_ORDER_LINES_ALL L,
1866 OE_ORDER_HEADERS H,
1867 HZ_CUST_SITE_USES_ALL SU,
1868 HZ_PARTY_SITES PARTY_SITE,
1869 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1870 HZ_LOCATIONS LOC,
1871 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1872 WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
1873 AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1874 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1875 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1876 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1877 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1878 AND NVL(ACCT_SITE.ORG_ID
1879 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1880 ,-99)
1881 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1882 AND L.HEADER_ID = H.HEADER_ID
1883 AND L.LINE_CATEGORY_CODE = 'ORDER'
1884 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1885 ,'X') not in ( 'PARTIAL' , 'YES' )
1886 AND L.BOOKED_FLAG = 'Y'
1887 AND DECODE(PICK_SHIPPING_INTERVAL
1888 ,-1
1889 ,TRUNC(SYSDATE)
1890 ,NVL(L.REQUEST_DATE
1891 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1892 DEBUG := 74;
1893 SELECT
1894 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1895 ,0) * (NVL(L.ORDERED_QUANTITY
1896 ,0) - NVL(L.SHIPPED_QUANTITY
1897 ,0)))
1898 ,0)
1899 INTO L_TOTAL3_PICK
1900 FROM
1901 OE_ORDER_LINES_ALL L,
1902 OE_ORDER_HEADERS H,
1903 HZ_CUST_SITE_USES_ALL SU,
1904 HZ_PARTY_SITES PARTY_SITE,
1905 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1906 HZ_LOCATIONS LOC,
1907 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1908 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1909 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1910 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1911 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1912 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1913 AND NVL(ACCT_SITE.ORG_ID
1914 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1915 ,-99)
1916 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1917 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1918 AND L.HEADER_ID = H.HEADER_ID
1919 AND L.LINE_CATEGORY_CODE = 'ORDER'
1920 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
1921 AND L.BOOKED_FLAG = 'Y'
1922 AND DECODE(PICK_SHIPPING_INTERVAL
1923 ,-1
1924 ,TRUNC(SYSDATE)
1925 ,NVL(L.REQUEST_DATE
1926 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
1927 L_UNINV_ORD_PICK := L_TOTAL1_PICK + L_TOTAL3_PICK;
1928 ELSE
1929 DEBUG := 75;
1930 SELECT
1931 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1932 ,0) * NVL(L.ORDERED_QUANTITY
1933 ,0))
1934 ,0)
1935 INTO L_TOTAL1_PICK
1936 FROM
1937 OE_ORDER_LINES_ALL L,
1938 OE_ORDER_HEADERS H,
1939 HZ_CUST_SITE_USES_ALL SU,
1940 HZ_PARTY_SITES PARTY_SITE,
1941 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1942 HZ_LOCATIONS LOC,
1943 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1944 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1945 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1946 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1947 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1948 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1949 AND NVL(ACCT_SITE.ORG_ID
1950 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1951 ,-99)
1952 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1953 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1954 AND L.HEADER_ID = H.HEADER_ID
1955 AND L.LINE_CATEGORY_CODE = 'ORDER'
1956 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
1957 ,'X') not in ( 'PARTIAL' , 'YES' )
1958 AND L.BOOKED_FLAG = 'Y'
1959 AND DECODE(PICK_SHIPPING_INTERVAL
1960 ,-1
1961 ,TRUNC(SYSDATE)
1962 ,NVL(L.REQUEST_DATE
1963 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
1964 AND not exists (
1965 SELECT
1966 'x'
1967 FROM
1968 OE_ORDER_HOLDS OH
1969 WHERE OH.HEADER_ID = H.HEADER_ID
1970 AND OH.HOLD_RELEASE_ID is null );
1971 DEBUG := 76;
1972 SELECT
1973 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
1974 ,0) * (NVL(L.ORDERED_QUANTITY
1975 ,0) - NVL(L.SHIPPED_QUANTITY
1976 ,0)))
1977 ,0)
1978 INTO L_TOTAL3_PICK
1979 FROM
1980 OE_ORDER_LINES_ALL L,
1981 OE_ORDER_HEADERS H,
1982 HZ_CUST_SITE_USES_ALL SU,
1983 HZ_PARTY_SITES PARTY_SITE,
1984 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1985 HZ_LOCATIONS LOC,
1986 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1987 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
1988 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
1989 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1990 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1991 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1992 AND NVL(ACCT_SITE.ORG_ID
1993 ,-99) = NVL(LOC_ASSIGN.ORG_ID
1994 ,-99)
1995 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
1996 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
1997 AND L.HEADER_ID = H.HEADER_ID
1998 AND L.LINE_CATEGORY_CODE = 'ORDER'
1999 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
2000 AND L.BOOKED_FLAG = 'Y'
2001 AND DECODE(PICK_SHIPPING_INTERVAL
2002 ,-1
2003 ,TRUNC(SYSDATE)
2004 ,NVL(L.REQUEST_DATE
2005 ,H.REQUEST_DATE) + PICK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
2006 AND not exists (
2007 SELECT
2008 'x'
2009 FROM
2010 OE_ORDER_HOLDS OH
2011 WHERE OH.HEADER_ID = H.HEADER_ID
2012 AND OH.HOLD_RELEASE_ID is null );
2013 L_UNINV_ORD_PICK := L_TOTAL1_PICK + L_TOTAL3_PICK;
2014 END IF;
2015 ELSE
2016 DEBUG := 77;
2017 L_UNINV_ORD_PICK := 0;
2018 END IF;
2019 END IF;
2020 C_UNINV_ORD_PICK := L_UNINV_ORD_PICK;
2021 IF SITE_USE_ID = 0 THEN
2022 IF PACK_UNINVOICED_FLAG = 'Y' THEN
2023 IF PACK_ON_HOLD_FLAG = 'Y' THEN
2024 DEBUG := 78;
2025 SELECT
2026 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2027 ,0) * NVL(L.ORDERED_QUANTITY
2028 ,0))
2029 ,0)
2030 INTO L_TOTAL1_PACK
2031 FROM
2032 OE_ORDER_LINES_ALL L,
2033 OE_ORDER_HEADERS H,
2034 HZ_CUST_SITE_USES_ALL SU,
2035 HZ_PARTY_SITES PARTY_SITE,
2036 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2037 HZ_LOCATIONS LOC,
2038 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2039 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
2040 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2041 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2042 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2043 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2044 AND NVL(ACCT_SITE.ORG_ID
2045 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2046 ,-99)
2047 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2048 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2049 AND L.HEADER_ID = H.HEADER_ID
2050 AND L.LINE_CATEGORY_CODE = 'ORDER'
2051 AND L.BOOKED_FLAG = 'Y'
2052 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
2053 ,'X') not in ( 'PARTIAL' , 'YES' )
2054 AND DECODE(PACK_SHIPPING_INTERVAL
2055 ,-1
2056 ,TRUNC(SYSDATE)
2057 ,NVL(L.REQUEST_DATE
2058 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
2059 DEBUG := 79;
2060 SELECT
2061 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2062 ,0) * (NVL(L.ORDERED_QUANTITY
2063 ,0) - NVL(L.SHIPPED_QUANTITY
2064 ,0)))
2065 ,0)
2066 INTO L_TOTAL3_PACK
2067 FROM
2068 OE_ORDER_LINES_ALL L,
2069 OE_ORDER_HEADERS H,
2070 HZ_CUST_SITE_USES_ALL SU,
2071 HZ_PARTY_SITES PARTY_SITE,
2072 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2073 HZ_LOCATIONS LOC,
2074 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2075 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
2076 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2077 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2078 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2079 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2080 AND NVL(ACCT_SITE.ORG_ID
2081 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2082 ,-99)
2083 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2084 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2085 AND L.HEADER_ID = H.HEADER_ID
2086 AND L.LINE_CATEGORY_CODE = 'ORDER'
2087 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
2088 AND L.BOOKED_FLAG = 'Y'
2089 AND DECODE(PACK_SHIPPING_INTERVAL
2090 ,-1
2091 ,TRUNC(SYSDATE)
2092 ,NVL(L.REQUEST_DATE
2093 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
2094 L_UNINV_ORD_PACK := L_TOTAL1_PACK + L_TOTAL3_PACK;
2095 ELSE
2096 DEBUG := 80;
2097 SELECT
2098 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2099 ,0) * NVL(L.ORDERED_QUANTITY
2100 ,0))
2101 ,0)
2102 INTO L_TOTAL1_PACK
2103 FROM
2104 OE_ORDER_LINES_ALL L,
2105 OE_ORDER_HEADERS H,
2106 HZ_CUST_SITE_USES_ALL SU,
2107 HZ_PARTY_SITES PARTY_SITE,
2108 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2109 HZ_LOCATIONS LOC,
2110 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2111 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
2112 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2113 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2114 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2115 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2116 AND NVL(ACCT_SITE.ORG_ID
2117 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2118 ,-99)
2119 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2120 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2121 AND L.HEADER_ID = H.HEADER_ID
2122 AND L.LINE_CATEGORY_CODE = 'ORDER'
2123 AND L.BOOKED_FLAG = 'Y'
2124 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
2125 ,'X') not in ( 'PARTIAL' , 'YES' )
2126 AND DECODE(PACK_SHIPPING_INTERVAL
2127 ,-1
2128 ,TRUNC(SYSDATE)
2129 ,NVL(L.REQUEST_DATE
2130 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
2131 AND not exists (
2132 SELECT
2133 'x'
2134 FROM
2135 OE_ORDER_HOLDS OH
2136 WHERE OH.HEADER_ID = H.HEADER_ID
2137 AND OH.HOLD_RELEASE_ID is null );
2138 DEBUG := 81;
2139 SELECT
2140 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2141 ,0) * (NVL(L.ORDERED_QUANTITY
2142 ,0) - NVL(L.SHIPPED_QUANTITY
2143 ,0)))
2144 ,0)
2145 INTO L_TOTAL3_PACK
2146 FROM
2147 OE_ORDER_LINES_ALL L,
2148 OE_ORDER_HEADERS H,
2149 HZ_CUST_SITE_USES_ALL SU,
2150 HZ_PARTY_SITES PARTY_SITE,
2151 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2152 HZ_LOCATIONS LOC,
2153 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2154 WHERE ACCT_SITE.CUST_ACCOUNT_ID = CUSTOMER_ID
2155 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2156 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2157 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2158 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2159 AND NVL(ACCT_SITE.ORG_ID
2160 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2161 ,-99)
2162 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2163 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2164 AND L.HEADER_ID = H.HEADER_ID
2165 AND L.LINE_CATEGORY_CODE = 'ORDER'
2166 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
2167 AND L.BOOKED_FLAG = 'Y'
2168 AND DECODE(PACK_SHIPPING_INTERVAL
2169 ,-1
2170 ,TRUNC(SYSDATE)
2171 ,NVL(L.REQUEST_DATE
2172 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
2173 AND not exists (
2174 SELECT
2175 'x'
2176 FROM
2177 OE_ORDER_HOLDS OH
2178 WHERE OH.HEADER_ID = H.HEADER_ID
2179 AND OH.HOLD_RELEASE_ID is null );
2180 L_UNINV_ORD_PACK := L_TOTAL1_PACK + L_TOTAL3_PACK;
2181 END IF;
2182 ELSE
2183 DEBUG := 82;
2184 L_UNINV_ORD_PACK := 0;
2185 END IF;
2186 ELSE
2187 IF PACK_UNINVOICED_FLAG = 'Y' THEN
2188 IF PACK_ON_HOLD_FLAG = 'Y' THEN
2189 DEBUG := 83;
2190 SELECT
2191 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2192 ,0) * NVL(L.ORDERED_QUANTITY
2193 ,0))
2194 ,0)
2195 INTO L_TOTAL1_PACK
2196 FROM
2197 OE_ORDER_LINES_ALL L,
2198 OE_ORDER_HEADERS H,
2199 HZ_CUST_SITE_USES_ALL SU,
2200 HZ_PARTY_SITES PARTY_SITE,
2201 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2202 HZ_LOCATIONS LOC,
2203 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2204 WHERE H.INVOICE_TO_ORG_ID = SU.SITE_USE_ID
2205 AND SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
2206 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2207 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2208 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2209 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2210 AND NVL(ACCT_SITE.ORG_ID
2211 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2212 ,-99)
2213 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2214 AND L.HEADER_ID = H.HEADER_ID
2215 AND L.LINE_CATEGORY_CODE = 'ORDER'
2216 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
2217 ,'X') not in ( 'PARTIAL' , 'YES' )
2218 AND L.BOOKED_FLAG = 'Y'
2219 AND DECODE(PACK_SHIPPING_INTERVAL
2220 ,-1
2221 ,TRUNC(SYSDATE)
2222 ,NVL(L.REQUEST_DATE
2223 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
2224 DEBUG := 84;
2225 SELECT
2226 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2227 ,0) * (NVL(L.ORDERED_QUANTITY
2228 ,0) - NVL(L.SHIPPED_QUANTITY
2229 ,0)))
2230 ,0)
2231 INTO L_TOTAL3_PACK
2232 FROM
2233 OE_ORDER_LINES_ALL L,
2234 OE_ORDER_HEADERS H,
2235 HZ_CUST_SITE_USES_ALL SU,
2236 HZ_PARTY_SITES PARTY_SITE,
2237 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2238 HZ_LOCATIONS LOC,
2239 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2240 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
2241 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2242 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2243 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2244 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2245 AND NVL(ACCT_SITE.ORG_ID
2246 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2247 ,-99)
2248 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2249 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2250 AND L.HEADER_ID = H.HEADER_ID
2251 AND L.LINE_CATEGORY_CODE = 'ORDER'
2252 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
2253 AND L.BOOKED_FLAG = 'Y'
2254 AND DECODE(PACK_SHIPPING_INTERVAL
2255 ,-1
2256 ,TRUNC(SYSDATE)
2257 ,NVL(L.REQUEST_DATE
2258 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE);
2259 L_UNINV_ORD_PACK := L_TOTAL1_PACK + L_TOTAL3_PACK;
2260 ELSE
2261 DEBUG := 85;
2262 SELECT
2263 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2264 ,0) * NVL(L.ORDERED_QUANTITY
2265 ,0))
2266 ,0)
2267 INTO L_TOTAL1_PACK
2268 FROM
2269 OE_ORDER_LINES_ALL L,
2270 OE_ORDER_HEADERS H,
2271 HZ_CUST_SITE_USES_ALL SU,
2272 HZ_PARTY_SITES PARTY_SITE,
2273 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2274 HZ_LOCATIONS LOC,
2275 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2276 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
2277 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2278 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2279 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2280 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2281 AND NVL(ACCT_SITE.ORG_ID
2282 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2283 ,-99)
2284 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2285 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2286 AND L.HEADER_ID = H.HEADER_ID
2287 AND L.LINE_CATEGORY_CODE = 'ORDER'
2288 AND NVL(L.INVOICE_INTERFACE_STATUS_CODE
2289 ,'X') not in ( 'PARTIAL' , 'YES' )
2290 AND L.BOOKED_FLAG = 'Y'
2291 AND DECODE(PACK_SHIPPING_INTERVAL
2292 ,-1
2293 ,TRUNC(SYSDATE)
2294 ,NVL(L.REQUEST_DATE
2295 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
2296 AND not exists (
2297 SELECT
2298 'x'
2299 FROM
2300 OE_ORDER_HOLDS OH
2301 WHERE OH.HEADER_ID = H.HEADER_ID
2302 AND OH.HOLD_RELEASE_ID is null );
2303 DEBUG := 86;
2304 SELECT
2305 NVL(SUM(NVL(L.UNIT_SELLING_PRICE
2306 ,0) * (NVL(L.ORDERED_QUANTITY
2307 ,0) - NVL(L.SHIPPED_QUANTITY
2308 ,0)))
2309 ,0)
2310 INTO L_TOTAL3_PACK
2311 FROM
2312 OE_ORDER_LINES_ALL L,
2313 OE_ORDER_HEADERS H,
2314 HZ_CUST_SITE_USES_ALL SU,
2315 HZ_PARTY_SITES PARTY_SITE,
2316 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2317 HZ_LOCATIONS LOC,
2318 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2319 WHERE SU.SITE_USE_ID = C_COMPUTE_AMOUNTSFORMULA.SITE_USE_ID
2320 AND ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
2321 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2322 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2323 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2324 AND NVL(ACCT_SITE.ORG_ID
2325 ,-99) = NVL(LOC_ASSIGN.ORG_ID
2326 ,-99)
2327 AND SU.SITE_USE_ID = H.INVOICE_TO_ORG_ID
2328 AND H.TRANSACTIONAL_CURR_CODE = CURRENCY1
2329 AND L.HEADER_ID = H.HEADER_ID
2330 AND L.LINE_CATEGORY_CODE = 'ORDER'
2331 AND L.INVOICE_INTERFACE_STATUS_CODE = 'PARTIAL'
2332 AND L.BOOKED_FLAG = 'Y'
2333 AND DECODE(PACK_SHIPPING_INTERVAL
2334 ,-1
2335 ,TRUNC(SYSDATE)
2336 ,NVL(L.REQUEST_DATE
2337 ,H.REQUEST_DATE) + PACK_SHIPPING_INTERVAL) >= TRUNC(SYSDATE)
2338 AND not exists (
2339 SELECT
2340 'x'
2341 FROM
2342 OE_ORDER_HOLDS OH
2343 WHERE OH.HEADER_ID = H.HEADER_ID
2344 AND OH.HOLD_RELEASE_ID is null );
2345 L_UNINV_ORD_PACK := L_TOTAL1_PACK + L_TOTAL3_PACK;
2346 END IF;
2347 ELSE
2348 DEBUG := 87;
2349 L_UNINV_ORD_PACK := 0;
2350 END IF;
2351 END IF;
2352 C_UNINV_ORD_PACK := L_UNINV_ORD_PACK;
2353 RETURN (0);
2354 EXCEPTION
2355 WHEN OTHERS THEN
2356 /*SRW.MESSAGE(1000
2357 ,'debug: ' || TO_CHAR(DEBUG))*/NULL;
2358 /*SRW.MESSAGE(1000
2359 ,SQLCODE || ' ' || SQLERRM)*/NULL;
2360 RETURN (0);
2361 END;
2362 RETURN NULL;
2363 END C_COMPUTE_AMOUNTSFORMULA;
2364
2365 FUNCTION C_DATA_NOT_FOUNDFORMULA(CUSTOMER_NAME IN VARCHAR2) RETURN NUMBER IS
2366 BEGIN
2367 RP_DATA_FOUND := CUSTOMER_NAME;
2368 RETURN (0);
2369 END C_DATA_NOT_FOUNDFORMULA;
2370
2371 FUNCTION C_ADDRESSFORMULA(ADDRESS1 IN VARCHAR2
2372 ,CITY IN VARCHAR2
2373 ,STATE IN VARCHAR2) RETURN VARCHAR2 IS
2374 BEGIN
2375 /*SRW.REFERENCE(ADDRESS1)*/NULL;
2376 /*SRW.REFERENCE(CITY)*/NULL;
2377 /*SRW.REFERENCE(STATE)*/NULL;
2378 IF ADDRESS1 IS NOT NULL THEN
2379 RETURN (ADDRESS1 || ' , ' || CITY || ' , ' || STATE);
2380 ELSE
2381 RETURN (NULL);
2382 END IF;
2383 RETURN NULL;
2384 END C_ADDRESSFORMULA;
2385
2386 FUNCTION C_DAYS_ON_HOLD_CRFORMULA(S_DAYS_ON_HOLD_CR IN NUMBER) RETURN NUMBER IS
2387 BEGIN
2388 RETURN (ROUND(S_DAYS_ON_HOLD_CR
2389 ,0));
2390 END C_DAYS_ON_HOLD_CRFORMULA;
2391
2392 FUNCTION C_DAYS_ON_HOLD_CUFORMULA(S_DAYS_ON_HOLD_CU IN NUMBER) RETURN NUMBER IS
2393 BEGIN
2394 RETURN (ROUND(S_DAYS_ON_HOLD_CU
2395 ,0));
2396 END C_DAYS_ON_HOLD_CUFORMULA;
2397
2398 FUNCTION C_UNINV_ORD_SHIP_P RETURN NUMBER IS
2399 BEGIN
2400 RETURN C_UNINV_ORD_SHIP;
2401 END C_UNINV_ORD_SHIP_P;
2402
2403 FUNCTION C_UNINV_ORD_CREDIT_P RETURN NUMBER IS
2404 BEGIN
2405 RETURN C_UNINV_ORD_CREDIT;
2406 END C_UNINV_ORD_CREDIT_P;
2407
2408 FUNCTION C_REC_BAL_SHIP_P RETURN NUMBER IS
2409 BEGIN
2410 RETURN C_REC_BAL_SHIP;
2411 END C_REC_BAL_SHIP_P;
2412
2413 FUNCTION C_REC_BAL_CREDIT_P RETURN NUMBER IS
2414 BEGIN
2415 RETURN C_REC_BAL_CREDIT;
2416 END C_REC_BAL_CREDIT_P;
2417
2418 FUNCTION C_TOT_ORDER_LIMIT_P RETURN NUMBER IS
2419 BEGIN
2420 RETURN C_TOT_ORDER_LIMIT;
2421 END C_TOT_ORDER_LIMIT_P;
2422
2423 FUNCTION C_ORDER_LIMIT_P RETURN NUMBER IS
2424 BEGIN
2425 RETURN C_ORDER_LIMIT;
2426 END C_ORDER_LIMIT_P;
2427
2428 FUNCTION C_REC_BAL_PICK_P RETURN NUMBER IS
2429 BEGIN
2430 RETURN C_REC_BAL_PICK;
2431 END C_REC_BAL_PICK_P;
2432
2433 FUNCTION C_REC_BAL_PACK_P RETURN NUMBER IS
2434 BEGIN
2435 RETURN C_REC_BAL_PACK;
2436 END C_REC_BAL_PACK_P;
2437
2438 FUNCTION C_UNINV_ORD_PICK_P RETURN NUMBER IS
2439 BEGIN
2440 RETURN C_UNINV_ORD_PICK;
2441 END C_UNINV_ORD_PICK_P;
2442
2443 FUNCTION C_UNINV_ORD_PACK_P RETURN NUMBER IS
2444 BEGIN
2445 RETURN C_UNINV_ORD_PACK;
2446 END C_UNINV_ORD_PACK_P;
2447
2448 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
2449 BEGIN
2450 RETURN RP_REPORT_NAME;
2451 END RP_REPORT_NAME_P;
2452
2453 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
2454 BEGIN
2455 RETURN RP_SUB_TITLE;
2456 END RP_SUB_TITLE_P;
2457
2458 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
2459 BEGIN
2460 RETURN RP_COMPANY_NAME;
2461 END RP_COMPANY_NAME_P;
2462
2463 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
2464 BEGIN
2465 RETURN RP_FUNCTIONAL_CURRENCY;
2466 END RP_FUNCTIONAL_CURRENCY_P;
2467
2468 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
2469 BEGIN
2470 RETURN RP_DATA_FOUND;
2471 END RP_DATA_FOUND_P;
2472
2473 FUNCTION RP_DATE_HOLD_APPLIED_RANGE_P RETURN VARCHAR2 IS
2474 BEGIN
2475 RETURN RP_DATE_HOLD_APPLIED_RANGE;
2476 END RP_DATE_HOLD_APPLIED_RANGE_P;
2477
2478 FUNCTION RP_SHIP_P RETURN VARCHAR2 IS
2479 BEGIN
2480 RETURN RP_SHIP;
2481 END RP_SHIP_P;
2482
2483 FUNCTION RP_ORDER_P RETURN VARCHAR2 IS
2484 BEGIN
2485 RETURN RP_ORDER;
2486 END RP_ORDER_P;
2487
2488 FUNCTION RP_VAT_PROFILE_P RETURN VARCHAR2 IS
2489 BEGIN
2490 RETURN RP_VAT_PROFILE;
2491 END RP_VAT_PROFILE_P;
2492
2493 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
2494 BEGIN
2495 RETURN RP_CURR_PROFILE;
2496 END RP_CURR_PROFILE_P;
2497
2498 FUNCTION RP_ORDER_AMOUNT_P RETURN NUMBER IS
2499 BEGIN
2500 RETURN RP_ORDER_AMOUNT;
2501 END RP_ORDER_AMOUNT_P;
2502
2503 FUNCTION RP_TOTAL_AMOUNT_P RETURN NUMBER IS
2504 BEGIN
2505 RETURN RP_TOTAL_AMOUNT;
2506 END RP_TOTAL_AMOUNT_P;
2507
2508 FUNCTION RP_PICK_P RETURN VARCHAR2 IS
2509 BEGIN
2510 RETURN RP_PICK;
2511 END RP_PICK_P;
2512
2513 FUNCTION RP_PACK_P RETURN VARCHAR2 IS
2514 BEGIN
2515 RETURN RP_PACK;
2516 END RP_PACK_P;
2517
2518 FUNCTION ORDER_AMOUNT_DSPFORMULA( currency1 in varchar2,order_amount in number) RETURN VARCHAR2 IS
2519 BEGIN
2520
2521 --Bug 3466261 Starts
2522 declare
2523 L_STD_PRECISION NUMBER;
2524 L_EXT_PRECISION NUMBER;
2525 L_MIN_ACCT_UNIT NUMBER;
2526 L_ORDER_AMT NUMBER;
2527 begin
2528 /*SRW.REFERENCE(:RP_CURR_PROFILE);
2529 SRW.REFERENCE(:RP_ORDER_AMOUNT);
2530 srw.reference (:currency1);
2531 srw.reference (:order_amount);*/
2532
2533 L_ORDER_AMT := order_amount;
2534
2535 FND_CURRENCY_CACHE.GET_INFO(currency1,L_STD_PRECISION,L_EXT_PRECISION,L_MIN_ACCT_UNIT);
2536
2537
2538 IF( RP_CURR_PROFILE = 'EXTENDED' ) THEN
2539 L_ORDER_AMT := ROUND(L_ORDER_AMT,L_EXT_PRECISION);
2540 ELSE
2541 L_ORDER_AMT := ROUND(L_ORDER_AMT,L_STD_PRECISION);
2542 END IF;
2543 RP_ORDER_AMOUNT := L_ORDER_AMT;
2544
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 RP_ORDER_AMOUNT := order_amount;
2548 END;
2549 --Bug 3466261 End
2550
2551 /* srw.user_exit (
2552 'FND FORMAT_CURRENCY
2553 CODE=":currency1"
2554 DISPLAY_WIDTH="17"
2555 AMOUNT=":RP_ORDER_AMOUNT"
2556 DISPLAY=":order_amount_dsp"
2557 ');*/
2558 RETURN (RP_ORDER_AMOUNT);
2559
2560 END;
2561
2562 FUNCTION S_ORDER_AMOUNT_CR_DSPFORMULA(currency1 in varchar2, s_order_amount_cr in number ) RETURN VARCHAR2 IS
2563 BEGIN
2564
2565 --Bug 3466261 Starts
2566 declare
2567 L_STD_PRECISION NUMBER;
2568 L_EXT_PRECISION NUMBER;
2569 L_MIN_ACCT_UNIT NUMBER;
2570 L_TOTAL_AMT NUMBER;
2571 begin
2572 /*SRW.REFERENCE(:RP_CURR_PROFILE);
2573 SRW.REFERENCE(:RP_TOTAL_AMOUNT);
2574 srw.reference (:currency1);
2575 srw.reference (:s_order_amount_cr);*/
2576
2577 L_TOTAL_AMT := s_order_amount_cr;
2578
2579 FND_CURRENCY_CACHE.GET_INFO(currency1,L_STD_PRECISION,L_EXT_PRECISION,L_MIN_ACCT_UNIT);
2580
2581 IF( RP_CURR_PROFILE = 'EXTENDED' ) THEN
2582 L_TOTAL_AMT := ROUND(L_TOTAL_AMT,L_EXT_PRECISION);
2583 ELSE
2584 L_TOTAL_AMT := ROUND(L_TOTAL_AMT,L_STD_PRECISION);
2585 END IF;
2586 RP_TOTAL_AMOUNT := L_TOTAL_AMT;
2587
2588 EXCEPTION
2589 WHEN OTHERS THEN
2590 RP_TOTAL_AMOUNT := s_order_amount_cr;
2591 END;
2592 --Bug 3466261 End
2593
2594 /*srw.user_exit (
2595 'FND FORMAT_CURRENCY
2596 CODE=":currency1"
2597 DISPLAY_WIDTH="17"
2598 AMOUNT=":RP_TOTAL_AMOUNT"
2599 DISPLAY=":s_order_amount_cr_dsp"
2600 ');*/
2601 RETURN (RP_TOTAL_AMOUNT);
2602
2603 END;
2604
2605
2606
2607 END ONT_OEXOECCH_XMLP_PKG;
2608