[Home] [Help]
PACKAGE BODY: APPS.PO_ACCOUNT_HELPER
Source
1 PACKAGE BODY PO_ACCOUNT_HELPER AS
2 -- $Header: PO_ACCOUNT_HELPER.plb 120.8.12020000.6 2013/04/26 08:21:10 yuandli ship $
3
4 --<Bug 15917496>: added for logging.
5 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_ACCOUNT_HELPER');
6
7 --<Bug 15917496>: forward declare insert_report_autonomous
8 PROCEDURE INSERT_REPORT_AUTONOMOUS(
9 P_MESSAGE_TEXT IN VARCHAR2
10 , P_USER_ID IN NUMBER
11 , P_SEQUENCE_NUM IN OUT NOCOPY po_online_report_text.sequence%TYPE
12 , P_LINE_NUM IN po_online_report_text.line_num%TYPE
13 , p_shipment_num IN po_online_report_text.shipment_num%TYPE
14 , p_distribution_num IN po_online_report_text.distribution_num%TYPE
15 , p_transaction_id IN po_online_report_text.transaction_id%TYPE
16 , p_transaction_type IN po_online_report_text.transaction_type%TYPE
17 , p_message_type IN po_online_report_text.message_type%TYPE
18 , p_text_line IN po_online_report_text.text_line%TYPE
19 , p_segment1 IN po_online_report_text.segment1%TYPE
20 , p_online_report_id IN NUMBER
21 , x_return_status IN OUT NOCOPY VARCHAR2
22 );
23
24 -------------------------------------------------------------------------
25 PROCEDURE generate_acrn_from_lov(l_key number);
26 PROCEDURE cascade_acrn_values(l_key number);
27
28
29 -----------------------------------------------------------------------
30 -- Procedure: build_accounts
31 --
32 -- Wrapper around PO_WF_BUILD_ACCOUNT_INIT, which creates Award
33 -- Distributions through the GMS API when Grants is used. The
34 -- award distributions are temporary, and will be rolled back
35 -- after the workflow has run.
36 --
37 -- Params of note:
38 --
39 -- @param po_distribution_id
40 -- The distribution to generate accounts for.
41 --
42 -- @param p_award_number
43 -- The current displayed award number.
44 --
45 -- @return
46 -- See return value for PO_WF_BUILD_ACCOUNT_INIT.start_workflow.
47 --
48 -- @depends PO_GMS_INTEGRATION_PVT.maintain_po_adl(),
49 -- PO_WF_BUILD_ACCOUNT_INIT.start_workflow()
50 -----------------------------------------------------------------------
51 FUNCTION build_accounts(
52 x_purchasing_ou_id IN NUMBER -- POU's org ID
53 , x_transaction_flow_header_id IN NUMBER
54 , x_dest_charge_success IN OUT NOCOPY BOOLEAN
55 , x_dest_variance_success IN OUT NOCOPY BOOLEAN
56 , x_dest_charge_account_id IN OUT NOCOPY NUMBER
57 , x_dest_variance_account_id IN OUT NOCOPY NUMBER
58 , x_dest_charge_account_desc IN OUT NOCOPY VARCHAR2
59 , x_dest_variance_account_desc IN OUT NOCOPY VARCHAR2
60 , x_dest_charge_account_flex IN OUT NOCOPY VARCHAR2
61 , x_dest_variance_account_flex IN OUT NOCOPY VARCHAR2
62
63 , x_charge_success IN OUT NOCOPY BOOLEAN
64 , x_budget_success IN OUT NOCOPY BOOLEAN
65 , x_accrual_success IN OUT NOCOPY BOOLEAN
66 , x_variance_success IN OUT NOCOPY BOOLEAN
67 , x_code_combination_id IN OUT NOCOPY NUMBER
68 , x_budget_account_id IN OUT NOCOPY NUMBER
69 , x_accrual_account_id IN OUT NOCOPY NUMBER
70 , x_variance_account_id IN OUT NOCOPY NUMBER
71 , x_charge_account_flex IN OUT NOCOPY VARCHAR2
72 , x_budget_account_flex IN OUT NOCOPY VARCHAR2
73 , x_accrual_account_flex IN OUT NOCOPY VARCHAR2
74 , x_variance_account_flex IN OUT NOCOPY VARCHAR2
75 , x_charge_account_desc IN OUT NOCOPY VARCHAR2
76 , x_budget_account_desc IN OUT NOCOPY VARCHAR2
77 , x_accrual_account_desc IN OUT NOCOPY VARCHAR2
78 , x_variance_account_desc IN OUT NOCOPY VARCHAR2
79 , x_coa_id NUMBER
80 , x_bom_resource_id NUMBER
81 , x_bom_cost_element_id NUMBER
82 , x_category_id NUMBER
83 , x_destination_type_code VARCHAR2
84 , x_deliver_to_location_id NUMBER
85 , x_destination_organization_id NUMBER
86 , x_destination_subinventory VARCHAR2
87 , x_expenditure_type VARCHAR2
88 , x_expenditure_organization_id NUMBER
89 , x_expenditure_item_date DATE
90 , x_item_id NUMBER
91 , x_line_type_id NUMBER
92 , x_result_billable_flag VARCHAR2
93 , x_agent_id NUMBER
94 , x_project_id NUMBER
95 , x_from_type_lookup_code VARCHAR2
96 , x_from_header_id NUMBER
97 , x_from_line_id NUMBER
98 , x_task_id NUMBER
99 , x_deliver_to_person_id NUMBER
100 , x_type_lookup_code VARCHAR2
101 , x_vendor_id NUMBER
102 , x_wip_entity_id NUMBER
103 , x_wip_entity_type VARCHAR2
104 , x_wip_line_id NUMBER
105 , x_wip_repetitive_schedule_id NUMBER
106 , x_wip_operation_seq_num NUMBER
107 , x_wip_resource_seq_num NUMBER
108 , x_po_encumberance_flag VARCHAR2
109 , x_gl_encumbered_date DATE
110
111 , wf_itemkey IN OUT NOCOPY VARCHAR2
112 , x_new_combination IN OUT NOCOPY BOOLEAN
113
114 , header_att1 VARCHAR2
115 , header_att2 VARCHAR2
116 , header_att3 VARCHAR2
117 , header_att4 VARCHAR2
118 , header_att5 VARCHAR2
119 , header_att6 VARCHAR2
120 , header_att7 VARCHAR2
121 , header_att8 VARCHAR2
122 , header_att9 VARCHAR2
123 , header_att10 VARCHAR2
124 , header_att11 VARCHAR2
125 , header_att12 VARCHAR2
126 , header_att13 VARCHAR2
127 , header_att14 VARCHAR2
128 , header_att15 VARCHAR2
129
130 , line_att1 VARCHAR2
131 , line_att2 VARCHAR2
132 , line_att3 VARCHAR2
133 , line_att4 VARCHAR2
134 , line_att5 VARCHAR2
135 , line_att6 VARCHAR2
136 , line_att7 VARCHAR2
137 , line_att8 VARCHAR2
138 , line_att9 VARCHAR2
139 , line_att10 VARCHAR2
140 , line_att11 VARCHAR2
141 , line_att12 VARCHAR2
142 , line_att13 VARCHAR2
143 , line_att14 VARCHAR2
144 , line_att15 VARCHAR2
145
146 , shipment_att1 VARCHAR2
147 , shipment_att2 VARCHAR2
148 , shipment_att3 VARCHAR2
149 , shipment_att4 VARCHAR2
150 , shipment_att5 VARCHAR2
151 , shipment_att6 VARCHAR2
152 , shipment_att7 VARCHAR2
153 , shipment_att8 VARCHAR2
154 , shipment_att9 VARCHAR2
155 , shipment_att10 VARCHAR2
156 , shipment_att11 VARCHAR2
157 , shipment_att12 VARCHAR2
158 , shipment_att13 VARCHAR2
159 , shipment_att14 VARCHAR2
160 , shipment_att15 VARCHAR2
161
162 , distribution_att1 VARCHAR2
163 , distribution_att2 VARCHAR2
164 , distribution_att3 VARCHAR2
165 , distribution_att4 VARCHAR2
166 , distribution_att5 VARCHAR2
167 , distribution_att6 VARCHAR2
168 , distribution_att7 VARCHAR2
169 , distribution_att8 VARCHAR2
170 , distribution_att9 VARCHAR2
171 , distribution_att10 VARCHAR2
172 , distribution_att11 VARCHAR2
173 , distribution_att12 VARCHAR2
174 , distribution_att13 VARCHAR2
175 , distribution_att14 VARCHAR2
176 , distribution_att15 VARCHAR2
177
178 , FB_ERROR_MSG IN OUT NOCOPY VARCHAR2
179 , p_distribution_type IN VARCHAR2 DEFAULT NULL
180 , p_payment_type IN VARCHAR2 DEFAULT NULL
181 , x_award_id NUMBER DEFAULT NULL
182 , x_vendor_site_id NUMBER DEFAULT NULL
183 , p_func_unit_price IN NUMBER DEFAULT NULL
184 , p_distribution_id IN NUMBER --<HTML Orders/Agreements R12>
185 , p_award_number IN VARCHAR2 --<HTML Orders/Agreements R12>
186 , p_clm_misc_loa IN VARCHAR2 DEFAULT NULL --<CLM fed fields>
187 ) RETURN BOOLEAN
188 IS
189 l_award_set_id NUMBER;
190 l_gms_processing_required BOOLEAN;
191 l_success BOOLEAN;
192
193 BEGIN
194
195 -- establish savepoint to roll back to (general exception)
196 SAVEPOINT PO_ACCOUNT_HELPER_BEGIN_SP;
197
198 l_gms_processing_required := (p_award_number IS NOT NULL);
199
200 IF l_gms_processing_required THEN
201
202 -- savepoint for award distribution creation
203 SAVEPOINT PO_ACCOUNT_HELPER_GMS_SP;
204
205 -- Create/update the Award Distributions before calling the Account
206 -- Generator.
207 PO_GMS_INTEGRATION_PVT.maintain_po_adl(
208 p_dml_operation => PO_GMS_INTEGRATION_PVT.c_DML_OPERATION_DELETE
209 , p_dist_id => p_distribution_id
210 , p_project_id => x_project_id
211 , p_task_id => x_task_id
212 , p_award_number => p_award_number
213 , x_award_set_id => l_award_set_id
214 );
215
216 END IF;
217
218 -- call the Account Generator; award_id set to l_award_set_id; all
219 -- other parameters same as input parameters
220 l_success :=
221 PO_WF_BUILD_ACCOUNT_INIT.start_workflow(
222 x_purchasing_ou_id => x_purchasing_ou_id
223 , x_transaction_flow_header_id => x_transaction_flow_header_id
224 , x_dest_charge_success => x_dest_charge_success
225 , x_dest_variance_success => x_dest_variance_success
226 , x_dest_charge_account_id => x_dest_charge_account_id
227 , x_dest_variance_account_id => x_dest_variance_account_id
228 , x_dest_charge_account_desc => x_dest_charge_account_desc
229 , x_dest_variance_account_desc => x_dest_variance_account_desc
230 , x_dest_charge_account_flex => x_dest_charge_account_flex
231 , x_dest_variance_account_flex => x_dest_variance_account_flex
232 , x_charge_success => x_charge_success
233 , x_budget_success => x_budget_success
234 , x_accrual_success => x_accrual_success
235 , x_variance_success => x_variance_success
236 , x_code_combination_id => x_code_combination_id
237 , x_budget_account_id => x_budget_account_id
238 , x_accrual_account_id => x_accrual_account_id
239 , x_variance_account_id => x_variance_account_id
240 , x_charge_account_flex => x_charge_account_flex
241 , x_budget_account_flex => x_budget_account_flex
242 , x_accrual_account_flex => x_accrual_account_flex
243 , x_variance_account_flex => x_variance_account_flex
244 , x_charge_account_desc => x_charge_account_desc
245 , x_budget_account_desc => x_budget_account_desc
246 , x_accrual_account_desc => x_accrual_account_desc
247 , x_variance_account_desc => x_variance_account_desc
248 , x_coa_id => x_coa_id
249 , x_bom_resource_id => x_bom_resource_id
250 , x_bom_cost_element_id => x_bom_cost_element_id
251 , x_category_id => x_category_id
252 , x_destination_type_code => x_destination_type_code
253 , x_deliver_to_location_id => x_deliver_to_location_id
254 , x_destination_organization_id => x_destination_organization_id
255 , x_destination_subinventory => x_destination_subinventory
256 , x_expenditure_type => x_expenditure_type
257 , x_expenditure_organization_id => x_expenditure_organization_id
258 , x_expenditure_item_date => x_expenditure_item_date
259 , x_item_id => x_item_id
260 , x_line_type_id => x_line_type_id
261 , x_result_billable_flag => x_result_billable_flag
262 , x_agent_id => x_agent_id
263 , x_project_id => x_project_id
264 , x_from_type_lookup_code => x_from_type_lookup_code
265 , x_from_header_id => x_from_header_id
266 , x_from_line_id => x_from_line_id
267 , x_task_id => x_task_id
268 , x_deliver_to_person_id => x_deliver_to_person_id
269 , x_type_lookup_code => x_type_lookup_code
270 , x_vendor_id => x_vendor_id
271 , x_wip_entity_id => x_wip_entity_id
272 , x_wip_entity_type => x_wip_entity_type
273 , x_wip_line_id => x_wip_line_id
274 , x_wip_repetitive_schedule_id => x_wip_repetitive_schedule_id
275 , x_wip_operation_seq_num => x_wip_operation_seq_num
276 , x_wip_resource_seq_num => x_wip_resource_seq_num
277 , x_po_encumberance_flag => x_po_encumberance_flag
278 , x_gl_encumbered_date => x_gl_encumbered_date
279 , wf_itemkey => wf_itemkey
280 , x_new_combination => x_new_combination
281 , header_att1 => header_att1
282 , header_att2 => header_att2
283 , header_att3 => header_att3
284 , header_att4 => header_att4
285 , header_att5 => header_att5
286 , header_att6 => header_att6
287 , header_att7 => header_att7
288 , header_att8 => header_att8
289 , header_att9 => header_att9
290 , header_att10 => header_att10
291 , header_att11 => header_att11
292 , header_att12 => header_att12
293 , header_att13 => header_att13
294 , header_att14 => header_att14
295 , header_att15 => header_att15
296 , line_att1 => line_att1
297 , line_att2 => line_att2
298 , line_att3 => line_att3
299 , line_att4 => line_att4
300 , line_att5 => line_att5
301 , line_att6 => line_att6
302 , line_att7 => line_att7
303 , line_att8 => line_att8
304 , line_att9 => line_att9
305 , line_att10 => line_att10
306 , line_att11 => line_att11
307 , line_att12 => line_att12
308 , line_att13 => line_att13
309 , line_att14 => line_att14
310 , line_att15 => line_att15
311 , shipment_att1 => shipment_att1
312 , shipment_att2 => shipment_att2
313 , shipment_att3 => shipment_att3
314 , shipment_att4 => shipment_att4
315 , shipment_att5 => shipment_att5
316 , shipment_att6 => shipment_att6
317 , shipment_att7 => shipment_att7
318 , shipment_att8 => shipment_att8
319 , shipment_att9 => shipment_att9
320 , shipment_att10 => shipment_att10
321 , shipment_att11 => shipment_att11
322 , shipment_att12 => shipment_att12
323 , shipment_att13 => shipment_att13
324 , shipment_att14 => shipment_att14
325 , shipment_att15 => shipment_att15
326 , distribution_att1 => distribution_att1
327 , distribution_att2 => distribution_att2
328 , distribution_att3 => distribution_att3
329 , distribution_att4 => distribution_att4
330 , distribution_att5 => distribution_att5
331 , distribution_att6 => distribution_att6
332 , distribution_att7 => distribution_att7
333 , distribution_att8 => distribution_att8
334 , distribution_att9 => distribution_att9
335 , distribution_att10 => distribution_att10
336 , distribution_att11 => distribution_att11
337 , distribution_att12 => distribution_att12
338 , distribution_att13 => distribution_att13
339 , distribution_att14 => distribution_att14
340 , distribution_att15 => distribution_att15
341 , FB_ERROR_MSG => FB_ERROR_MSG
342 , p_distribution_type => p_distribution_type
343 , p_payment_type => p_payment_type
344 , x_award_id => l_award_set_id
345 , x_vendor_site_id => x_vendor_site_id
346 , p_func_unit_price => p_func_unit_price
347 , p_clm_misc_loa => p_clm_misc_loa
348 );
349
350 IF l_gms_processing_required THEN
351 -- Revert the Award Distribution changes back to the saved state.
352 ROLLBACK TO PO_ACCOUNT_HELPER_GMS_SP;
353 END IF;
354
355 RETURN(l_success);
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 ROLLBACK TO PO_ACCOUNT_HELPER_BEGIN_SP;
360 RAISE;
361
362 END build_accounts;
363
364 -------------------------------------------------------------------------------
365 -- ACRN Project Start
366 --Start of Comments
367 --Name: GET_LINE_NUM
368 --Pre-reqs:
369 -- None.
370 --Modifies:
371 -- None.
372 --Locks:
373 -- None.
374 --Function:
375 -- Derive PO Line Number to pass it as token for messages PO_ACRN_RESERVE_LETTER and PO_ACRN_INVALID_LETTER
376 --Parameters:
377 --IN:
378 --p_distibution_id
379 -- PO Distribution ID.
380 -- OUT:
381 -- x_token
382 -- Line Number for the Distribution ID passed.
383 --Notes:
384 -- This procedure was written for ACRN (CLM Phase 2) project.
385 -- The requirement is to derive Line Number for Distribution ID passed.
386 --Testing:
387 --
388 --End of Comments
389
390 PROCEDURE GET_LINE_NUM(p_distibution_id IN NUMBER,x_token OUT NOCOPY VARCHAR2)
391
392 IS
393
394 BEGIN
395
396 SELECT DISTINCT line_num into x_token FROM po_lines_all pol, po_distributions_all pod
397 WHERE pol.po_line_id=pod.po_line_id
398 AND pod.po_distribution_id= p_distibution_id;
399
400
401 EXCEPTION
402
403 WHEN OTHERS THEN
404 x_token := null;
405
406 END GET_LINE_NUM;
407
408 -------------------------------------------------------------------------------
409 -- ACRN Project Start
410 --Start of Comments
411 --Name: GET_CHARGE_ACCOUNT
412 --Pre-reqs:
413 -- None.
414 --Modifies:
415 -- None.
416 --Locks:
417 -- None.
418 --Function:
419 -- Derive Charge Account to pass it as token for messages PO_ACRN_CHARGE_ACCOUNT
420 --Parameters:
421 --IN:
422 --p_distibution_id
423 -- PO Distribution ID.
424 -- OUT:
425 -- x_token
426 -- Charge Account for the Distribution ID passed.
427 --Notes:
428 -- This procedure was written for ACRN (CLM Phase 2) project.
429 -- The requirement is to derive Charge Account for Distribution ID passed.
430 --Testing:
431 --
432 --End of Comments
433
434 PROCEDURE GET_CHARGE_ACCOUNT(p_distibution_id IN NUMBER,x_token OUT NOCOPY VARCHAR2)
435
436 IS
437
438 l_structure_number number;
439 l_combination_id number;
440 BEGIN
441
442 select distinct pod.CODE_COMBINATION_ID,CHART_OF_ACCOUNTS_ID
443 into l_combination_id,l_structure_number
444 from po_distributions_all pod,gl_code_combinations gcc
445 where pod.po_distribution_id= p_distibution_id
446 and pod.code_combination_id=gcc.code_combination_id;
447
448 x_token := FND_FLEX_EXT.GET_SEGS
449 (
450 application_short_name => 'SQLGL',
451 key_flex_code => 'GL#',
452 structure_number => l_structure_number,
453 combination_id => l_combination_id
454 );
455
456
457
458 EXCEPTION
459
460 WHEN OTHERS THEN
461 x_token := null;
462
463 END GET_CHARGE_ACCOUNT;
464
465 -------------------------------------------------------------------------------
466 -- ACRN Project Start
467 --Start of Comments
468 --Name: BUILD_ACRN
469 --Pre-reqs:
470 -- None.
471 --Modifies:
472 -- None.
473 --Locks:
474 -- None.
475 --Function:
476 -- Derive ACRN for Award and Modification
477 -- Check if call is for Award or Modification
478 -- Populate the po_session_gt table
479 -- Check if input ACRN is not null, then call is_valid_acrn to validate the ACRN.
480 -- If ACRN passed is null, then derive ACRN from fnd_lookups PO_ACRN.
481 -- Get the ACRN value and validate with other ACRNS for the PO.
482 --Parameters:
483 --IN:
484 --p_header_id
485 -- PO Header ID.
486 --P_DIST_ID_TBL
487 -- PL/SQL table which stores the PO distribution IDs.
488 --P_CODE_ID_TBL
489 -- PL/SQL table which stores the Code Combination IDs.
490 --P_ACRN_TBL
491 -- PL/SQL table which stores the input ACRN table.
492 --P_LOA_TBL
493 -- PL/SQL table which stores input LOA
494 --P_CHANGE_STAT_TBL
495 -- PL/SQL table which stores input change status
496 -- OUT:
497 -- X_ACRN_TBL
498 -- PL/SQL table which stores the derived ACRN table values.
499 -- X_RETURN_STATUS
500 -- Return Status - S - Success , E- Error.
501 -- X_RETURN_MESSAGE
502 -- X_RETURN_MESSAGE stores the error messages PO_DUPLICATE_ACRN,PO_ACRN_CHARGE_ACCOUNT, PO_ACRN_RESERVE_LETTER,PO_ACRN_INVALID_LETTER
503 -- X_MESSAGE_TOKEN
504 -- X_MESSAGE_TOKEN stores the tokens for error messages PO_DUPLICATE_ACRN,PO_ACRN_CHARGE_ACCOUNT,
505 -- PO_ACRN_RESERVE_LETTER,PO_ACRN_INVALID_LETTER
506 --Notes:
507 -- This procedure was written for ACRN (CLM Phase 2) project.
508 -- The requirement is to derive ACRN for AWARD and Modification.
509 --Testing:
510 --
511 --End of Comments
512 /*ACRN REFACTORING CHANGES BEGIN*/
513 PROCEDURE BUILD_ACRN(p_header_id IN NUMBER,
514 P_DIST_ID_TBL IN PO_TBL_NUMBER,
515 P_CODE_ID_TBL IN PO_TBL_NUMBER,
516 P_ACRN_TBL IN PO_TBL_VARCHAR30,
517 P_LOA_TBL IN PO_TBL_VARCHAR30,
518 P_CHANGE_STAT_TBL IN PO_TBL_VARCHAR30,
519 P_REGEN_Y_N IN VARCHAR2,
520 X_ACRN_TBL OUT NOCOPY PO_TBL_VARCHAR30,
521 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
522 X_RETURN_MESSAGE OUT NOCOPY PO_TBL_VARCHAR2000,
523 X_MESSAGE_TOKEN OUT NOCOPY PO_TBL_VARCHAR2000) IS
524 l_charge_count NUMBER;
525 l_acrn_code NUMBER := 1;
526 l_return_status varchar2(1);
527 l_return_message varchar2(2000);
528 l_approved_flag VARCHAR2(1);
529 l_doc_type varchar2(30);
530 x_error VARCHAR2(1300);
531 l_dist_id NUMBER;
532 l_error_message PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
533 l_message_token PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
534 l_token varchar2(2000);
535 l_dist_count number;
536 l_key NUMBER;
537 l_ACRN_TBL PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
538
539 BEGIN
540
541 --Finding out if the whether the it is an award or modification.
542 IF (P_DIST_ID_TBL.count > 0) THEN
543 BEGIN
544 SELECT approved_flag
545 INTO l_approved_flag
546 FROM po_headers_all poh
547 WHERE poh.po_header_id = p_header_id;
548 IF l_approved_flag = 'Y' THEN
549 l_doc_type := 'MOD';
550 ELSE
551 l_doc_type := 'AWARD';
552 END IF;
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN
555 l_doc_type := 'AWARD';
556 END;
557 END IF;
558
559 l_key := PO_CORE_S.get_session_gt_nextval;
560 /*
561 Inserting the data passed to the procedure into the PO_SESSION_GT table
562 KEY - SESSION KEY
563 NUM1 - DISTRIBUTION ID
564 CHAR1 - ACCOUNT NUMBER
565 CHAR2 - LOA VALUE
566 CHAR3 - Value based on which we have to generate the
567 ACRN if LOA is not available consider Account number
568 CHAR4 - Change status
569 CHAR5 - ACRN
570
571 */
572 INSERT INTO po_session_gt
573 (KEY,num1,num2,char1,char2,char3,char4,char5
574 )
575 WITH test_tab AS
576 (SELECT dist.dist_num,
577 dist.rn rnum,
578 acc.acc_nbr,
579 loa.loa_val,
580 NVL(loa.loa_val,acc.acc_nbr) gen_val,
581 changestat.change_stat,
582 acrn.acr
583 FROM
584 (SELECT column_value acr,rownum rn FROM TABLE(P_ACRN_TBL)
585 ) acrn,
586 (SELECT column_value acc_nbr,rownum rn FROM TABLE(P_CODE_ID_TBL)
587 )acc,
588 (SELECT column_value loa_val,rownum rn FROM TABLE(P_LOA_TBL)
589 ) loa,
590 (SELECT column_value dist_num,rownum rn FROM TABLE(P_DIST_ID_TBL)
591 ) dist,
592 (SELECT column_value change_stat,rownum rn FROM TABLE(P_CHANGE_STAT_TBL)
593 ) changestat
594 WHERE loa.rn = acc.rn
595 AND acc.rn = acrn.rn
596 AND loa.rn = acrn.rn
597 AND dist.rn = loa.rn
598 AND dist.rn = acc.rn
599 AND dist.rn = acrn.rn
600 AND changestat.rn = acrn.rn
601 )
602 SELECT l_key,dist_num,rnum,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
603 /*
604 Logic Explination:
605
606 Building ACRN Values:
607 ====================
608 Build ACRN (Build the values which were not given by user)
609 ----------
610 Award : 1. Cascade the Given Values to the distributions
611 ----- having the same LOA/Charge ACC.
612 2. Generate the Values for remaining Distinct LOAs
613 3. Do a post cascade to populate the generated ACRN
614 values remaining distributions and LOAs.
615 Modifications:
616 -------------
617 1. Cascade the Given Values to the distributions
618 having the same LOA/Charge ACC.
619 2. Generate the Values for remaining Distinct LOAs
620 3. Do a post cascade to populate the generated ACRN
621 values remaining distributions and LOAs.
622 Rebuild Acrn:
623 ------------
624 Awards:
625 ------
626 1. Clear all the ACRN values provided by the users
627 for all the distributions.
628 2. Generate the Values for the remaining Distributions
629 3. Do a post cascade to populate the generated ACRN
630 values remaining distributions and LOAs.
631 Modifications:
632 -------------
633 1. Clear All the ACRN values for all lines having
634 change status other than "NOCHANGE".
635 2. Cascade the ACRN values.
636 3. Generate the ACRN values for the distributions not having
637 ACRN values.
638 4. Post Cascade the values.
639
640 */
641
642
643 IF (P_REGEN_Y_N = 'N') THEN --Build Acrn Logic is same for both Award and Modification
644
645 --Pre Cascade Values as they are given by user
646 cascade_acrn_values(l_key);
647
648 ----Generate ACRN from LOV values for those which are left.
649 generate_acrn_from_lov(l_key);
650
651 --Post Cascade the values as the generate acrn generates Distinct values only
652 cascade_acrn_values(l_key);
653
654 ELSIF(l_doc_type='AWARD' AND P_REGEN_Y_N <> 'N') THEN
655 --Regenerate in Award means clean up all the acrn values given by the user and then regenerate from the lov
656 update po_session_gt set char5 = null where key = l_key;
657 ----Generate ACRN from LOV values
658 generate_acrn_from_lov(l_key);
659 --Post Cascade the values as the generate acrn generates Distinct values only
660 cascade_acrn_values(l_key);
661
662 ELSIF(l_doc_type='MOD' AND P_REGEN_Y_N <> 'N') THEN
663 --Regenerate in Award means clean up all the acrn values given by the user and then regenerate from the lov
664 --rows with NOCHANGE char4 (change_status) should be ignored, as those come from the base document
665 update po_session_gt set char5 = null where key = l_key and char4 not in ('NOCHANGE');
666
667 --Pre Cascade Values as they are given by user
668 cascade_acrn_values(l_key);
669
670 ----Generate ACRN from LOV values
671 generate_acrn_from_lov(l_key);
672
673 --Post Cascade the values as the generate acrn generates Distinct values only
674 cascade_acrn_values(l_key);
675 END IF;
676 X_RETURN_STATUS := 'S';
677 x_return_message := l_error_message;
678 X_MESSAGE_TOKEN := l_message_token;
679 -- Ordered by Num2 (rownumber of dist id table )to ensure that
680 -- the acrns are returned in the same order as They were inserted.
681 select char5 bulk collect
682 into l_acrn_tbl
683 from po_session_gt
684 where
685 key = l_key
686 order by num2;
687 X_ACRN_TBL := l_ACRN_TBL;
688 DELETE from PO_SESSION_GT where key = l_key;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 X_RETURN_STATUS := 'E';
693 x_error := SQLERRM;
694
695 END BUILD_ACRN;
696 -------------------------------------------------------------------------------
697 /*This procedure will update the po_session gt with the ACRN values pulled from
698 the lookup table for distinct remaining generatable values. It works on the
699 the values present for the session key provided as the parameter*/
700 PROCEDURE generate_acrn_from_lov(l_key number)
701 is
702 BEGIN
703 UPDATE po_session_gt gt2
704 SET char5 =
705 (SELECT acrn
706 FROM
707 (SELECT avail_acrn.acrn,
708 avail_dist.gen_val
709 FROM
710 (SELECT gen_val,rownum rn
711 from
712 (SELECT num1 dist_num,
713 char3 gen_val,
714 char5 acrn
715 FROM po_session_gt gt
716 WHERE KEY = l_key
717 AND char5 IS NULL
718 and not exists(
719 select l_key from po_session_gt gtx where gtx.char3 = gt.char3 and gtx.num1>gt.num1
720 and KEY=l_key
721 )
722 ORDER BY dist_num
723 ))avail_dist,
724 (SELECT acrn,
725 rownum rn
726 FROM
727 (SELECT meaning acrn
728 FROM fnd_lookups
729 WHERE lookup_type = 'PO_ACRN'
730 AND enabled_flag ='Y'
731 AND start_date_active<=sysdate
732 AND (end_date_active IS NULL
733 OR end_date_active >=sysdate)
734 AND( meaning NOT IN
735 (SELECT DISTINCT TO_CHAR(NVL(char5,0))
736 FROM po_session_gt acrn_test
737 WHERE KEY = l_key
738 ))
739 ORDER BY to_number(lookup_code)
740 )
741 )avail_acrn
742 WHERE avail_dist.rn = avail_acrn.rn
743 )
744 WHERE gt2.char3 = gen_val
745 )
746 WHERE gt2.char5 IS NULL;
747 END;
748 -------------------------------------------------------------------------------
749 /* THIS PROCEDURE CASCADES The ACRN Values to the remaining non populated
750 distributions having the same LOA or Charge Account combination values.
751 */
752 PROCEDURE cascade_acrn_values(l_key number)
753 is
754 Begin
755 UPDATE po_session_gt gt2
756 SET char5 =
757 (SELECT ACRN
758 FROM
759 (SELECT DISTINCT not_provided.gen_val,
760 provided.acrn
761 FROM
762 (SELECT char5 acrn,
763 char3 GEN_VAL
764 FROM po_session_gt
765 WHERE KEY = l_key
766 AND char5 IS NULL
767 ) not_provided
768 LEFT OUTER JOIN
769 (SELECT DISTINCT char5 acrn,
770 char3 GEN_VAL
771 FROM po_session_gt gt1
772 WHERE KEY = l_key
773 AND CHAR5 IS NOT NULL
774 AND NOT EXISTS
775 (SELECT 1
776 FROM PO_SESSION_GT GT2
777 WHERE KEY = l_key
778 AND CHAR5 IS NOT NULL
779 AND GT1.CHAR3 = GT2.CHAR3
780 AND GT1.ROWID > GT2.ROWID
781 )
782 ) provided
783 ON provided.gen_val = not_provided.gen_val
784 ) generat
785 WHERE generat.gen_val = gt2.char3
786 )
787 where gt2.char5 IS NULL;
788
789 End;
790
791 -------------------------------------------------------------------------------
792 /*ACRN REFACTORING CHANGES END*/
793 -------------------------------------------------------------------------------
794
795
796 -------------------------------------------------------------------------------
797 -- ACRN Project Start
798 --Start of Comments
799 --Name: IS_VALID_ACRN
800 --Pre-reqs:
801 -- None.
802 --Modifies:
803 -- None.
804 --Locks:
805 -- None.
806 --Function:
807 -- Validates the input ACRN and checks if its valid. If not valid, then return error messages.
808 --Parameters:
809 --IN:
810 --P_ACRN
811 -- Input ACRN Value.
812 -- OUT:
813 -- X_RETURN_STATUS
814 -- Return Status - S - Success , E- Error.
815 -- X_RETURN_MESSAGE
816 -- X_RETURN_MESSAGE stores the error messages PO_ACRN_RESERVE_LETTER,PO_ACRN_INVALID_LETTER
817 --Notes:
818 -- This procedure was written for ACRN (CLM Phase 2) project.
819 -- Validates the input ACRN and checks if its valid. If not valid, then return error messages.
820 --Testing:
821 --
822 --End of Comments
823
824 PROCEDURE IS_VALID_ACRN(P_ACRN IN VARCHAR2,
825 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
826 X_RETURN_MESSAGE OUT NOCOPY VARCHAR2
827 ) IS
828 l_count NUMBER;
829 l_count1 NUMBER;
830 l_count2 NUMBER;
831 BEGIN
832 SELECT COUNT(1)
833 INTO l_count
834 FROM fnd_lookups
835 WHERE lookup_type = 'PO_ACRN'
836 AND meaning = p_acrn;
837
838 if l_count > 0 then
839
840 x_return_status := 'S';
841
842 else
843
844 select instr(upper(p_acrn), 'I'), instr(upper(p_acrn), 'O')
845 into l_count1, l_count2
846 from dual;
847
848 if l_count1 > 0 or l_count2 > 0 then
849
850 x_return_status := 'E';
851 x_return_message := 'PO_ACRN_RESERVE_LETTER';
852
853 elsif p_acrn is not null then
854 x_return_status := 'E';
855 x_return_message := 'PO_ACRN_INVALID_LETTER';
856
857 ELSE
858
859 x_return_status := 'S';
860
861 end if;
862
863 end if;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 x_return_status := 'E';
868 END IS_VALID_ACRN;
869
870 -----------------------------------------------------------------------
871 -- Procedure: online_rebuild_accounts
872 --
873 -- Wrapper around build_accounts, which rebuild accounts for each distribution.
874 --
875 -- Params of note:
876 --
877 -- @param p_document_id
878 -- The document to generate accounts for.
879 --
880 -- @param p_document_type
881 -- PO
882 -- @param p_document_subtype
883 --
884 -- @param p_draft_id
885 -- The draft to generate accounts for.
886 --
887 -- @param x_online_report_id
888 --
889 -- @param x_return_status
890 --
891 -- @return
892 -- See return value for PO_WF_BUILD_ACCOUNT_INIT.start_workflow.
893 --
894 -- @depends build_accounts
895 -----------------------------------------------------------------------
896 PROCEDURE online_rebuild_accounts
897 (
898 p_document_id IN NUMBER
899 , p_document_type IN VARCHAR2
900 , p_document_subtype IN VARCHAR2
901 , p_draft_id IN NUMBER DEFAULT NULL
902 , x_online_report_id OUT NOCOPY NUMBER
903 , x_return_status OUT NOCOPY VARCHAR2)
904 IS
905 d_api_name CONSTANT VARCHAR2(30) := 'online_rebuild_accounts';
906 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
907 d_position NUMBER;
908
909 l_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
910 x_sequence po_online_report_text.sequence%TYPE;
911 l_return_status VARCHAR2(1) := 'S';
912 l_success BOOLEAN;
913
914 --Variables for getting billable flag.
915 l_msg_application VARCHAR2(5);
916 l_msg_type VARCHAR2(1);
917 l_msg_token1 VARCHAR2(2000);
918 l_msg_token2 VARCHAR2(2000);
919 l_msg_token3 VARCHAR2(2000);
920 l_msg_count NUMBER;
921 l_msg_data VARCHAR2(2000);
922 l_billable_flag VARCHAR2(2000);
923
924 l_dest_charge_success BOOLEAN;
925 l_dest_variance_success BOOLEAN;
926 l_charge_success BOOLEAN;
927 l_budget_success BOOLEAN;
928 l_accrual_success BOOLEAN;
929 l_variance_success BOOLEAN;
930 l_new_combination BOOLEAN;
931 l_wf_item_key VARCHAR2(2000) := NULL;
932 l_bom_cost_element_id NUMBER := NULL;
933 l_fb_error_msg VARCHAR2(2000);
934 l_dummy VARCHAR2(240);
935 l_product VARCHAR2(3);
936 l_status VARCHAR2(1);
937 l_retvar BOOLEAN;
938 l_eam_installed BOOLEAN;
939 l_isPoChargeAccountReadOnly BOOLEAN;
940 l_isSPSDistribution BOOLEAN;
941 l_is_dd_shopfloor VARCHAR2(1);
942 l_is_pa_flex_override VARCHAR2(1);
943 l_req_encum_on VARCHAR2(1);
944 l_po_encum_on VARCHAR2(1);
945 xx_return_status VARCHAR2(1);
946 l_entity_type wip_entities.entity_type%type;
947 l_osp_flag po_line_types_b.outside_operation_flag%TYPE;
948 l_expense_accrual_code po_system_parameters_all.EXPENSE_ACCRUAL_CODE%TYPE;
949 l_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
950 x_ou_id po_distributions_all.org_id%TYPE;
951 l_current_ou_id po_headers_all.org_id%TYPE;
952 l_old_code_combination_id po_distributions_all.CODE_COMBINATION_ID%TYPE;
953 l_dest_charge_account_id po_distributions_all.DEST_CHARGE_ACCOUNT_ID%TYPE;
954 l_dest_variance_account_id po_distributions_all.DEST_VARIANCE_ACCOUNT_ID%TYPE;
955 l_dest_charge_account_desc VARCHAR2(2000);
956 l_dest_variance_account_desc VARCHAR2(2000);
957 l_dest_charge_account_flex VARCHAR2(2000);
958 l_dest_variance_account_flex VARCHAR2(2000);
959 l_charge_account_flex VARCHAR2(2000);
960 l_budget_account_flex VARCHAR2(2000);
961 l_accrual_account_flex VARCHAR2(2000);
962 l_variance_account_flex VARCHAR2(2000);
963 l_charge_account_desc VARCHAR2(2000);
964 l_budget_account_desc VARCHAR2(2000);
965 l_accrual_account_desc VARCHAR2(2000);
966 l_variance_account_desc VARCHAR2(2000);
967 l_code_combination_id po_distributions_all.CODE_COMBINATION_ID%TYPE;
968 l_budget_account_id po_distributions_all.BUDGET_ACCOUNT_ID%TYPE;
969 l_accrual_account_id po_distributions_all.ACCRUAL_ACCOUNT_ID%TYPE;
970 l_variance_account_id po_distributions_all.VARIANCE_ACCOUNT_ID%TYPE;
971 l_award_number gms_awards_all.award_number%TYPE;
972
973 CURSOR dists_csr(p_doc_id IN NUMBER, p_draftid IN NUMBER) --bug16628805
974 IS
975 SELECT pod.po_distribution_id,
976 pod.distribution_num,
977 pod.deliver_to_location_id,
978 pod.deliver_to_person_id,
979 pod.destination_type_code,
980 pod.destination_organization_id,
981 pod.encumbered_flag,
982 pod.WIP_ENTITY_ID,
983 pod.wip_line_id,
984 pod.wip_repetitive_schedule_id,
985 pod.wip_operation_seq_num,
986 pod.wip_resource_seq_num,
987 pod.gl_encumbered_date,
988 pod.req_distribution_id,
989 pod.project_id,
990 pod.task_id,
991 pod.expenditure_item_date,
992 pod.expenditure_type,
993 pod.expenditure_organization_id,
994 pod.bom_resource_id,
995 pod.DESTINATION_SUBINVENTORY,
996 pod.org_id,
997 pod.DEST_CHARGE_ACCOUNT_ID,
998 pod.DEST_VARIANCE_ACCOUNT_ID,
999 pod.CODE_COMBINATION_ID,
1000 pod.BUDGET_ACCOUNT_ID,
1001 pod.ACCRUAL_ACCOUNT_ID,
1002 pod.VARIANCE_ACCOUNT_ID,
1003 pod.distribution_type,
1004 pod.award_id,
1005 pod.attribute1 attribute1,
1006 pod.attribute2 attribute2,
1007 pod.attribute3 attribute3,
1008 pod.attribute4 attribute4,
1009 pod.attribute5 attribute5,
1010 pod.attribute6 attribute6,
1011 pod.attribute7 attribute7,
1012 pod.attribute8 attribute8,
1013 pod.attribute9 attribute9,
1014 pod.attribute10 attribute10,
1015 pod.attribute11 attribute11,
1016 pod.attribute12 attribute12,
1017 pod.attribute13 attribute13,
1018 pod.attribute14 attribute14,
1019 pod.attribute15 attribute15,
1020 pol.line_num,
1021 pol.item_id line_item_id,
1022 pol.line_type_id,
1023 pol.unit_price line_unit_price,
1024 pol.category_id line_category_id,
1025 pol.from_line_id line_from_line_id,
1026 pol.attribute1 line_attribute1,
1027 pol.attribute2 line_attribute2,
1028 pol.attribute3 line_attribute3,
1029 pol.attribute4 line_attribute4,
1030 pol.attribute5 line_attribute5,
1031 pol.attribute6 line_attribute6,
1032 pol.attribute7 line_attribute7,
1033 pol.attribute8 line_attribute8,
1034 pol.attribute9 line_attribute9,
1035 pol.attribute10 line_attribute10,
1036 pol.attribute11 line_attribute11,
1037 pol.attribute12 line_attribute12,
1038 pol.attribute13 line_attribute13,
1039 pol.attribute14 line_attribute14,
1040 pol.attribute15 line_attribute15,
1041 poll.shipment_num,
1042 poll.consigned_flag,
1043 poll.quantity_billed ship_quantity_billed,
1044 poll.quantity_received ship_quantity_received,
1045 poll.closed_code ship_closed_code,
1046 poll.ship_to_organization_id,
1047 poll.Transaction_Flow_Header_Id,
1048 poll.payment_type ship_payment_type,
1049 poll.attribute1 ship_attribute1,
1050 poll.attribute2 ship_attribute2,
1051 poll.attribute3 ship_attribute3,
1052 poll.attribute4 ship_attribute4,
1053 poll.attribute5 ship_attribute5,
1054 poll.attribute6 ship_attribute6,
1055 poll.attribute7 ship_attribute7,
1056 poll.attribute8 ship_attribute8,
1057 poll.attribute9 ship_attribute9,
1058 poll.attribute10 ship_attribute10,
1059 poll.attribute11 ship_attribute11,
1060 poll.attribute12 ship_attribute12,
1061 poll.attribute13 ship_attribute13,
1062 poll.attribute14 ship_attribute14,
1063 poll.attribute15 ship_attribute15,
1064 poh.segment1,
1065 poh.org_id header_org_id,
1066 poh.agent_id header_agent_id,
1067 poh.from_header_id header_from_header_id,
1068 poh.type_lookup_code header_type_lookup_code,
1069 poh.vendor_id header_vendor_id,
1070 poh.vendor_site_id header_vendor_site_id,
1071 poh.attribute1 header_attribute1,
1072 poh.attribute2 header_attribute2,
1073 poh.attribute3 header_attribute3,
1074 poh.attribute4 header_attribute4,
1075 poh.attribute5 header_attribute5,
1076 poh.attribute6 header_attribute6,
1077 poh.attribute7 header_attribute7,
1078 poh.attribute8 header_attribute8,
1079 poh.attribute9 header_attribute9,
1080 poh.attribute10 header_attribute10,
1081 poh.attribute11 header_attribute11,
1082 poh.attribute12 header_attribute12,
1083 poh.attribute13 header_attribute13,
1084 poh.attribute14 header_attribute14,
1085 poh.attribute15 header_attribute15
1086 FROM PO_DISTRIBUTIONS_MERGE_V pod,
1087 PO_LINE_LOCATIONS_MERGE_V poll,
1088 PO_LINES_MERGE_V pol,
1089 PO_HEADERS_MERGE_V poh
1090 WHERE pod.po_line_id = poll.po_line_id
1091 AND pod.line_location_id = poll.line_location_id
1092 AND poll.po_line_id = pol.po_line_id
1093 AND pol.po_header_id = poh.po_header_id
1094 AND poh.po_header_id = p_doc_id
1095 AND NVL(poll.cancel_flag,'N') = 'N'
1096 AND poh.draft_id = pol.draft_id
1097 AND pol.draft_id = poll.draft_id
1098 AND poll.draft_id = pod.draft_id
1099 AND pod.draft_id = p_draftid; --bug 16628805
1100
1101 BEGIN
1102
1103 IF (PO_LOG.d_proc) THEN
1104 PO_LOG.proc_begin(d_module);
1105 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1106 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1107 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1108 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
1109 END IF;
1110
1111 -- establish savepoint to roll back to (general exception)
1112 SAVEPOINT PO_ACCOUNT_HELPER_RB_ACC_SP;
1113 -- START MAIN LOGIC
1114
1115 SELECT PO_ONLINE_REPORT_TEXT_S.nextval
1116 INTO l_report_id
1117 FROM dual;
1118
1119 x_online_report_id := l_report_id;
1120 x_sequence := 0;
1121 x_return_status := l_return_status;
1122
1123 d_position := 10;
1124 IF (PO_LOG.d_stmt) THEN
1125 PO_LOG.stmt(d_module,d_position,'l_report_id',l_report_id);
1126 END IF;
1127
1128 --Check if EAM installed.
1129 l_product:= 'EAM';
1130 l_retvar := FND_INSTALLATION.get_app_info ( l_product, l_status, l_dummy, l_dummy );
1131 IF l_status = 'I' THEN
1132 l_eam_installed := TRUE;
1133 ELSE
1134 l_eam_installed := FALSE;
1135 END IF;
1136
1137 d_position := 20;
1138 IF (PO_LOG.d_stmt) THEN
1139 PO_LOG.stmt(d_module,d_position,'l_eam_installed',l_status);
1140 END IF;
1141 --Check profile.
1142 l_is_dd_shopfloor := NVL(FND_PROFILE.VALUE('PO_DIRECT_DELIVERY_TO_SHOPFLOOR'),'N');
1143 l_is_pa_flex_override := NVL(FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES'),'N');
1144
1145 d_position := 30;
1146 IF (PO_LOG.d_stmt) THEN
1147 PO_LOG.stmt(d_module,d_position,'l_is_dd_shopfloor',l_is_dd_shopfloor);
1148 PO_LOG.stmt(d_module,d_position,'l_is_pa_flex_override',l_is_pa_flex_override);
1149 END IF;
1150 -- Get OU ID.
1151 SELECT org_id
1152 INTO l_current_ou_id
1153 FROM PO_HEADERS_MERGE_V poh
1154 WHERE poh.po_header_id = p_document_id
1155 AND poh.draft_id = NVL(p_draft_id, -1); --bug 16628805
1156
1157 d_position := 40;
1158 IF (PO_LOG.d_stmt) THEN
1159 PO_LOG.stmt(d_module,d_position,'l_current_ou_id',l_current_ou_id);
1160 END IF;
1161 --Get OU Info.
1162 SELECT NVL(FSP.req_encumbrance_flag, 'N') req_encumbrance_flag,
1163 NVL(FSP.purch_encumbrance_flag, 'N') purch_encumbrance_flag,
1164 PSP.EXPENSE_ACCRUAL_CODE,
1165 GLS.chart_of_accounts_id
1166 INTO l_req_encum_on,
1167 l_po_encum_on,
1168 l_expense_accrual_code,
1169 l_coa_id
1170 FROM po_system_parameters_all PSP,
1171 financials_system_params_all FSP,
1172 gl_sets_of_books GLS,
1173 fnd_id_flex_structures COAFS
1174 WHERE FSP.org_id = PSP.org_id
1175 AND FSP.set_of_books_id = GLS.set_of_books_id
1176 AND COAFS.id_flex_num = GLS.chart_of_accounts_id
1177 AND COAFS.application_id = 101 --SQLGL
1178 AND COAFS.id_flex_code = 'GL#'
1179 and PSP.org_id = l_current_ou_id;
1180
1181 d_position := 50;
1182 IF (PO_LOG.d_stmt) THEN
1183 PO_LOG.stmt(d_module,d_position,'l_req_encum_on',l_req_encum_on);
1184 PO_LOG.stmt(d_module,d_position,'l_po_encum_on',l_po_encum_on);
1185 PO_LOG.stmt(d_module,d_position,'l_expense_accrual_code',l_expense_accrual_code);
1186 PO_LOG.stmt(d_module,d_position,'l_coa_id',l_coa_id);
1187 END IF;
1188 --Lock document
1189 BEGIN
1190 IF NVL(p_draft_id,-1) = -1 THEN --bug 16628805
1191 SELECT NULL INTO l_dummy
1192 FROM
1193 PO_HEADERS_ALL POH
1194 WHERE POH.po_header_id = p_document_id
1195 FOR UPDATE
1196 NOWAIT;
1197 ELSE
1198 SELECT NULL INTO l_dummy
1199 FROM
1200 PO_HEADERS_DRAFT_ALL POH
1201 WHERE POH.po_header_id = p_document_id
1202 AND POH.draft_id = p_draft_id
1203 FOR UPDATE
1204 NOWAIT;
1205 END IF;
1206 END;
1207
1208 d_position := 60;
1209 IF (PO_LOG.d_stmt) THEN
1210 PO_LOG.stmt(d_module,d_position,'locked document',p_document_id);
1211 END IF;
1212 --Loop each distribution.
1213 FOR l_dists in dists_csr(p_document_id, p_draft_id) --bug 16628805
1214 LOOP
1215 d_position := 61;
1216 IF (PO_LOG.d_stmt) THEN
1217 PO_LOG.stmt(d_module,d_position,'Checking distribution',l_dists.po_distribution_id);
1218 END IF;
1219 --Lock distribution
1220 BEGIN
1221 IF NVL(p_draft_id,-1) = -1 THEN --bug 16628805
1222 SELECT NULL INTO l_dummy
1223 FROM
1224 PO_DISTRIBUTIONS_ALL POD
1225 WHERE POD.po_distribution_id = l_dists.po_distribution_id
1226 FOR UPDATE
1227 NOWAIT;
1228 ELSE
1229 SELECT NULL INTO l_dummy
1230 FROM
1231 PO_DISTRIBUTIONS_DRAFT_ALL POD
1232 WHERE POD.po_distribution_id = l_dists.po_distribution_id
1233 AND POD.draft_id = p_draft_id
1234 FOR UPDATE
1235 NOWAIT;
1236 END IF;
1237 END;
1238
1239 d_position := 62;
1240 IF (PO_LOG.d_stmt) THEN
1241 PO_LOG.stmt(d_module,d_position,'locked distribution',l_dists.po_distribution_id);
1242 END IF;
1243 --Get OSP flag.
1244 SELECT nvl(pltb.outside_operation_flag, 'N')
1245 INTO l_osp_flag
1246 FROM po_line_types_b pltb
1247 WHERE pltb.line_type_id = l_dists.line_type_id;
1248
1249 --#1. Validate if the account should be built.
1250 /*Only build the account if all of the following are true:
1251 1) Distr is not encumbered
1252 2) Dest org id is not null
1253 3) OSP fields are valid
1254 */
1255 /* Do not build accounts if the destination type is shop floor and
1256 any of the following is true:
1257 1) wip_entity_id is null
1258 2) bom_resource_id is null and EAM conditions are not met
1259 EAM conditions require that EAM be installed, direct delivery to
1260 shop floor profile option is Y, and outside operation flag is N.
1261 */
1262 d_position := 63;
1263 IF (PO_LOG.d_stmt) THEN
1264 PO_LOG.stmt(d_module,d_position,'l_osp_flag',l_osp_flag);
1265 END IF;
1266 IF ('Y' = l_dists.encumbered_flag)
1267 OR l_dists.DESTINATION_ORGANIZATION_ID IS NULL
1268 OR ('SHOP FLOOR' = l_dists.destination_type_code
1269 AND (l_dists.WIP_ENTITY_ID IS NULL
1270 OR (l_dists.BOM_RESOURCE_ID IS NULL
1271 AND ((NOT l_eam_installed)
1272 OR ('N' <> l_osp_flag)
1273 OR ('Y' <> l_is_dd_shopfloor) ) ) ) ) THEN
1274 CONTINUE;
1275 END IF;
1276
1277 d_position := 64;
1278 IF (PO_LOG.d_stmt) THEN
1279 PO_LOG.stmt(d_module,d_position,'first validation passed', 'Y');
1280 END IF;
1281 --#2. Validate if the account is read-only.
1282 l_isPoChargeAccountReadOnly := FALSE;
1283 l_isSPSDistribution := FALSE;
1284 IF --1. Shipment is consigned
1285 ('Y' = l_dists.consigned_flag)
1286 --2. Destination Type is Shop Floor or Inventory
1287 OR ('SHOP FLOOR' = l_dists.destination_type_code)
1288 OR ('INVENTORY' = l_dists.destination_type_code)
1289 --3. Distribution is Encumbered
1290 OR ('Y' = l_dists.encumbered_flag)
1291 --4. Distribution is autocreated from req and req encumbrance is on
1292 OR (l_dists.REQ_DISTRIBUTION_ID IS NOT NULL AND 'Y' = l_req_encum_on)
1293 --5. Destination type is expense and project has been entered and
1294 -- profile PO_ALLOW_FLEXBUILDER_OVERRIDES does not allow the update
1295 OR (l_dists.destination_type_code = 'EXPENSE'
1296 AND l_dists.project_id IS NOT NULL
1297 AND 'N' = l_is_pa_flex_override)
1298 --6. Destination type is expense and Accrual Method is RECEIPT and
1299 -- qty billed or received is > 0
1300 OR (l_dists.destination_type_code = 'EXPENSE'
1301 AND 'RECEIPT' = l_expense_accrual_code
1302 AND (NVL(l_dists.ship_quantity_billed,0) >0
1303 OR NVL(l_dists.ship_quantity_received,0) > 0 ) )
1304 --7. Destination type is expense and Accrual Method is PERION END and
1305 -- shipment closure status is CLOSED
1306 OR (l_dists.destination_type_code = 'EXPENSE'
1307 AND 'PERION END' = l_expense_accrual_code
1308 AND 'CLOSED_CODE' = l_dists.ship_closed_code) THEN
1309 --po charge account read-only
1310 --dest charge account read-only
1311 l_isPoChargeAccountReadOnly := TRUE;
1312 ELSE
1313 --8. If it is a Shared Procurement Services (SPS) distribution,
1314 -- charge account is read only
1315 PO_SHARED_PROC_PVT.get_ou_and_coa_from_inv_org(
1316 p_inv_org_id => l_dists.ship_to_organization_id,
1317 x_coa_id => l_coa_id,
1318 x_ou_id => x_ou_id,
1319 x_return_status => xx_return_status );
1320 IF xx_return_status = FND_API.g_ret_sts_success THEN
1321 IF p_document_type = PO_CORE_S.g_doc_type_PO --#1.The PO is a Standard PO.
1322 AND p_document_subtype in ('STANDARD','PLANNED')
1323 AND x_ou_id <> l_dists.org_id --#2.Destination OU is not Purchasing OU.
1324 AND l_dists.Transaction_Flow_Header_Id IS NOT NULL THEN --#3.A transaction flow is defined between DOU and POU.
1325 --po charge account read-only
1326 l_isPoChargeAccountReadOnly := TRUE;
1327 l_isSPSDistribution := TRUE;
1328 END IF;
1329 END IF;
1330 END IF;
1331
1332 d_position := 65;
1333 IF (PO_LOG.d_stmt) THEN
1334 PO_LOG.stmt(d_module,d_position,'l_isPoChargeAccountReadOnly', l_isPoChargeAccountReadOnly);
1335 PO_LOG.stmt(d_module,d_position,'l_isSPSDistribution', l_isSPSDistribution);
1336 END IF;
1337
1338 IF l_isPoChargeAccountReadOnly = TRUE THEN
1339 CONTINUE;
1340 END IF;
1341
1342 --#3. Get billable flag
1343 BEGIN
1344 PA_TRANSACTIONS_PUB.validate_transaction (
1345 X_project_id => l_dists.project_id,
1346 X_task_id => l_dists.task_id,
1347 X_ei_date => l_dists.expenditure_item_date,
1348 X_expenditure_type => l_dists.expenditure_type,
1349 X_non_labor_resource => '',
1350 X_person_id => NVL(l_dists.deliver_to_person_id, l_dists.header_agent_id),
1351 X_quantity => '',
1352 X_denom_currency_code => '',
1353 X_acct_currency_code => '',
1354 X_denom_raw_cost => '',
1355 X_acct_raw_cost => '',
1356 X_acct_rate_type => '',
1357 X_acct_rate_date => '',
1358 X_acct_exchange_rate => '',
1359 X_transfer_ei => '',
1360 X_incurred_by_org_id => l_dists.expenditure_organization_id,
1361 X_nl_resource_org_id => '',
1362 X_transaction_source => '',
1363 X_calling_module => 'POXPOEPO',
1364 X_vendor_id => '',
1365 X_entered_by_user_id => '',
1366 X_attribute_category => '',
1367 X_attribute1 => l_dists.attribute1,
1368 X_attribute2 => l_dists.attribute2,
1369 X_attribute3 => l_dists.attribute3,
1370 X_attribute4 => l_dists.attribute4,
1371 X_attribute5 => l_dists.attribute5,
1372 X_attribute6 => l_dists.attribute6,
1373 X_attribute7 => l_dists.attribute7,
1374 X_attribute8 => l_dists.attribute8,
1375 X_attribute9 => l_dists.attribute9,
1376 X_attribute10 => l_dists.attribute10,
1377 X_attribute11 => l_dists.attribute11,
1378 X_attribute12 => l_dists.attribute12,
1379 X_attribute13 => l_dists.attribute13,
1380 X_attribute14 => l_dists.attribute14,
1381 X_attribute15 => l_dists.attribute15,
1382 X_msg_application => l_msg_application,
1383 X_msg_type => l_msg_type,
1384 X_msg_token1 => l_msg_token1,
1385 X_msg_token2 => l_msg_token2,
1386 X_msg_token3 => l_msg_token3,
1387 X_msg_count => l_msg_count,
1388 X_msg_data => l_msg_data,
1389 X_billable_flag => l_billable_flag);
1390 END;
1391
1392 d_position := 66;
1393 IF (PO_LOG.d_stmt) THEN
1394 PO_LOG.stmt(d_module,d_position,'l_billable_flag', l_billable_flag);
1395 END IF;
1396 --#4: Get entity type
1397 BEGIN
1398 select entity_type
1399 into l_entity_type
1400 from wip_entities
1401 where wip_entity_id = l_dists.wip_entity_id
1402 and organization_id = l_dists.org_id;
1403 --Check if it's EAM job whose entity_type=6.
1404 IF l_entity_type <> 6 THEN
1405 l_entity_type := NULL;
1406 END IF;
1407 EXCEPTION
1408 WHEN NO_DATA_FOUND THEN
1409 l_entity_type := NULL;
1410 END;
1411
1412 d_position := 67;
1413 IF (PO_LOG.d_stmt) THEN
1414 PO_LOG.stmt(d_module,d_position,'l_entity_type', l_entity_type);
1415 END IF;
1416 --#5: store into variables
1417 l_dest_charge_account_id := l_dists.DEST_CHARGE_ACCOUNT_ID;
1418 l_dest_variance_account_id := l_dists.DEST_VARIANCE_ACCOUNT_ID;
1419 l_old_code_combination_id := l_dists.CODE_COMBINATION_ID;
1420 l_code_combination_id := l_dists.CODE_COMBINATION_ID;
1421 l_budget_account_id := l_dists.BUDGET_ACCOUNT_ID;
1422 l_accrual_account_id := l_dists.ACCRUAL_ACCOUNT_ID;
1423 l_variance_account_id := l_dists.VARIANCE_ACCOUNT_ID;
1424
1425 --l_award_number := PO_GMS_INTEGRATION_PVT.get_number_from_award_set_id(
1426 -- p_award_set_id => l_dists.award_id);
1427
1428 d_position := 68;
1429 IF (PO_LOG.d_stmt) THEN
1430 PO_LOG.stmt(d_module,d_position,'l_award_number', l_award_number);
1431 PO_LOG.stmt(d_module,d_position,'old_dest_charge_account_id', l_dest_charge_account_id);
1432 PO_LOG.stmt(d_module,d_position,'old_dest_variance_account_id', l_dest_variance_account_id);
1433 PO_LOG.stmt(d_module,d_position,'old_code_combination_id', l_old_code_combination_id);
1434 PO_LOG.stmt(d_module,d_position,'old_budget_account_id', l_budget_account_id);
1435 PO_LOG.stmt(d_module,d_position,'old_accrual_account_id', l_accrual_account_id);
1436 PO_LOG.stmt(d_module,d_position,'old_variance_account_id', l_variance_account_id);
1437 END IF;
1438
1439 --#6: call workflow
1440 l_code_combination_id := NULL;
1441 l_success := PO_ACCOUNT_HELPER.build_accounts(
1442 --IN Params:
1443 x_purchasing_ou_id => l_dists.header_org_id,
1444 x_transaction_flow_header_id => l_dists.transaction_flow_header_id,
1445 --IN OUT Params:
1446 x_dest_charge_success => l_dest_charge_success,
1447 x_dest_variance_success => l_dest_variance_success,
1448 x_dest_charge_account_id => l_dest_charge_account_id,
1449 x_dest_variance_account_id => l_dest_variance_account_id,
1450 x_dest_charge_account_desc => l_dest_charge_account_desc,
1451 x_dest_variance_account_desc => l_dest_variance_account_desc,
1452 x_dest_charge_account_flex => l_dest_charge_account_flex,
1453 x_dest_variance_account_flex => l_dest_variance_account_flex,
1454 x_charge_success => l_charge_success,
1455 x_budget_success => l_budget_success,
1456 x_accrual_success => l_accrual_success,
1457 x_variance_success => l_variance_success,
1458 x_code_combination_id => l_code_combination_id,
1459 x_budget_account_id => l_budget_account_id,
1460 x_accrual_account_id => l_accrual_account_id,
1461 x_variance_account_id => l_variance_account_id,
1462 x_charge_account_flex => l_charge_account_flex,
1463 x_budget_account_flex => l_budget_account_flex,
1464 x_accrual_account_flex => l_accrual_account_flex,
1465 x_variance_account_flex => l_variance_account_flex,
1466 x_charge_account_desc => l_charge_account_desc,
1467 x_budget_account_desc => l_budget_account_desc,
1468 x_accrual_account_desc => l_accrual_account_desc,
1469 x_variance_account_desc => l_variance_account_desc,
1470 --IN Params:
1471 x_coa_id => l_coa_id,
1472 x_bom_resource_id => l_dists.bom_resource_id,
1473 x_bom_cost_element_id => l_bom_cost_element_id,
1474 x_category_id => l_dists.line_category_id,
1475 x_destination_type_code => l_dists.DESTINATION_TYPE_CODE,
1476 x_deliver_to_location_id => l_dists.DELIVER_TO_LOCATION_ID,
1477 x_destination_organization_id => l_dists.DESTINATION_ORGANIZATION_ID,
1478 x_destination_subinventory => l_dists.DESTINATION_SUBINVENTORY,
1479 x_expenditure_type => l_dists.EXPENDITURE_TYPE,
1480 x_expenditure_organization_id => l_dists.EXPENDITURE_ORGANIZATION_ID,
1481 x_expenditure_item_date => l_dists.EXPENDITURE_ITEM_DATE,
1482 x_item_id => l_dists.line_item_id,
1483 x_line_type_id => l_dists.line_type_id,
1484 x_result_billable_flag => l_billable_flag,
1485 x_agent_id => l_dists.header_agent_id,
1486 x_project_id => l_dists.project_id,
1487 x_from_type_lookup_code => NULL,
1488 x_from_header_id => l_dists.header_from_header_id,
1489 x_from_line_id => l_dists.line_from_line_id,
1490 x_task_id => l_dists.task_id,
1491 x_deliver_to_person_id => l_dists.deliver_to_person_id,
1492 x_type_lookup_code => l_dists.header_type_lookup_code,
1493 x_vendor_id => l_dists.header_vendor_id,
1494 x_wip_entity_id => l_dists.wip_entity_id,
1495 x_wip_entity_type => l_entity_type,
1496 x_wip_line_id => l_dists.wip_line_id,
1497 x_wip_repetitive_schedule_id => l_dists.wip_repetitive_schedule_id,
1498 x_wip_operation_seq_num => l_dists.wip_operation_seq_num,
1499 x_wip_resource_seq_num => l_dists.wip_resource_seq_num,
1500 x_po_encumberance_flag => l_po_encum_on,
1501 x_gl_encumbered_date => l_dists.gl_encumbered_date,
1502 --IN OUT Params:
1503 wf_itemkey => l_wf_item_key,
1504 x_new_combination => l_new_combination,
1505 --IN Params:
1506 header_att1 => l_dists.header_attribute1,
1507 header_att2 => l_dists.header_attribute2,
1508 header_att3 => l_dists.header_attribute3,
1509 header_att4 => l_dists.header_attribute4,
1510 header_att5 => l_dists.header_attribute5,
1511 header_att6 => l_dists.header_attribute6,
1512 header_att7 => l_dists.header_attribute7,
1513 header_att8 => l_dists.header_attribute8,
1514 header_att9 => l_dists.header_attribute9,
1515 header_att10 => l_dists.header_attribute10,
1516 header_att11 => l_dists.header_attribute11,
1517 header_att12 => l_dists.header_attribute12,
1518 header_att13 => l_dists.header_attribute13,
1519 header_att14 => l_dists.header_attribute14,
1520 header_att15 => l_dists.header_attribute15,
1521 line_att1 => l_dists.line_attribute1,
1522 line_att2 => l_dists.line_attribute2,
1523 line_att3 => l_dists.line_attribute3,
1524 line_att4 => l_dists.line_attribute4,
1525 line_att5 => l_dists.line_attribute5,
1526 line_att6 => l_dists.line_attribute6,
1527 line_att7 => l_dists.line_attribute7,
1528 line_att8 => l_dists.line_attribute8,
1529 line_att9 => l_dists.line_attribute9,
1530 line_att10 => l_dists.line_attribute10,
1531 line_att11 => l_dists.line_attribute11,
1532 line_att12 => l_dists.line_attribute12,
1533 line_att13 => l_dists.line_attribute13,
1534 line_att14 => l_dists.line_attribute14,
1535 line_att15 => l_dists.line_attribute15,
1536 shipment_att1 => l_dists.ship_attribute1,
1537 shipment_att2 => l_dists.ship_attribute2,
1538 shipment_att3 => l_dists.ship_attribute3,
1539 shipment_att4 => l_dists.ship_attribute4,
1540 shipment_att5 => l_dists.ship_attribute5,
1541 shipment_att6 => l_dists.ship_attribute6,
1542 shipment_att7 => l_dists.ship_attribute7,
1543 shipment_att8 => l_dists.ship_attribute8,
1544 shipment_att9 => l_dists.ship_attribute9,
1545 shipment_att10 => l_dists.ship_attribute10,
1546 shipment_att11 => l_dists.ship_attribute11,
1547 shipment_att12 => l_dists.ship_attribute12,
1548 shipment_att13 => l_dists.ship_attribute13,
1549 shipment_att14 => l_dists.ship_attribute14,
1550 shipment_att15 => l_dists.ship_attribute15,
1551 distribution_att1 => l_dists.attribute1,
1552 distribution_att2 => l_dists.attribute2,
1553 distribution_att3 => l_dists.attribute3,
1554 distribution_att4 => l_dists.attribute4,
1555 distribution_att5 => l_dists.attribute5,
1556 distribution_att6 => l_dists.attribute6,
1557 distribution_att7 => l_dists.attribute7,
1558 distribution_att8 => l_dists.attribute8,
1559 distribution_att9 => l_dists.attribute9,
1560 distribution_att10 => l_dists.attribute10,
1561 distribution_att11 => l_dists.attribute11,
1562 distribution_att12 => l_dists.attribute12,
1563 distribution_att13 => l_dists.attribute13,
1564 distribution_att14 => l_dists.attribute14,
1565 distribution_att15 => l_dists.attribute15,
1566 fb_error_msg => l_fb_error_msg, --IN OUT
1567 p_distribution_type => l_dists.distribution_type,
1568 p_payment_type => l_dists.ship_payment_type,
1569 x_award_id => NULL, --to set dynamically based on p_award_number
1570 x_vendor_site_id => l_dists.header_vendor_site_id,
1571 p_func_unit_price => l_dists.line_unit_price,
1572 p_distribution_id => l_dists.po_distribution_id,
1573 p_award_number => l_award_number);
1574
1575 d_position := 69;
1576 IF (PO_LOG.d_stmt) THEN
1577 PO_LOG.stmt(d_module,d_position,'l_success', l_success);
1578 PO_LOG.stmt(d_module,d_position,'new_code_combination_id', l_code_combination_id);
1579 END IF;
1580 --#7: handle workflow result
1581 IF l_success
1582 AND l_code_combination_id IS NOT NULL
1583 AND l_code_combination_id <> 0
1584 THEN
1585 IF l_isPoChargeAccountReadOnly = FALSE
1586 THEN
1587 --update current distribution code_combination_id with l_code_combination_id
1588 IF NVL(p_draft_id,-1) = -1 THEN --bug 16628805
1589 update po_distributions_all
1590 set code_combination_id = l_code_combination_id
1591 , last_update_date = sysdate
1592 , last_updated_by = FND_GLOBAL.user_id
1593 where po_distribution_id = l_dists.po_distribution_id;
1594 ELSE
1595 update po_distributions_draft_all
1596 set code_combination_id = l_code_combination_id
1597 , last_update_date = sysdate
1598 , last_updated_by = FND_GLOBAL.user_id
1599 where po_distribution_id = l_dists.po_distribution_id
1600 and draft_id = p_draft_id;
1601 END IF;
1602 END IF;
1603
1604 IF 'Y' = l_po_encum_on
1605 AND (l_dists.destination_type_code <> 'SHOP FLOOR'
1606 OR (l_dists.destination_type_code = 'SHOP FLOOR'
1607 AND l_entity_type = 6)) --EAM JOB
1608 AND l_dists.distribution_type <> 'PREPAYMENT'
1609 THEN
1610 IF (PO_LOG.d_stmt) THEN
1611 PO_LOG.stmt(d_module,d_position,'new_budget_account_id', l_budget_account_id);
1612 END IF;
1613 --update current distribution budget_account_id with l_budget_account_id
1614 IF NVL(p_draft_id, -1) = -1 THEN --bug 16628805
1615 update po_distributions_all
1616 set budget_account_id = l_budget_account_id
1617 , last_update_date = sysdate
1618 , last_updated_by = FND_GLOBAL.user_id
1619 where po_distribution_id = l_dists.po_distribution_id;
1620 ELSE
1621 update po_distributions_draft_all
1622 set budget_account_id = l_budget_account_id
1623 , last_update_date = sysdate
1624 , last_updated_by = FND_GLOBAL.user_id
1625 where po_distribution_id = l_dists.po_distribution_id
1626 and draft_id = p_draft_id;
1627 END IF;
1628 END IF;
1629
1630 IF (PO_LOG.d_stmt) THEN
1631 PO_LOG.stmt(d_module,d_position,'new_accrual_account_id', l_accrual_account_id);
1632 PO_LOG.stmt(d_module,d_position,'new_variance_account_id', l_variance_account_id);
1633 END IF;
1634 --update current distribution accrual_account_id with l_accrual_account_id
1635 --update current distribution variance_account_id with l_variance_account_id
1636 IF NVL(p_draft_id, -1) = -1 THEN --bug 16628805
1637 update po_distributions_all
1638 set accrual_account_id = l_accrual_account_id
1639 , variance_account_id = l_variance_account_id
1640 , last_update_date = sysdate
1641 , last_updated_by = FND_GLOBAL.user_id
1642 where po_distribution_id = l_dists.po_distribution_id;
1643 ELSE
1644 update po_distributions_draft_all
1645 set accrual_account_id = l_accrual_account_id
1646 , variance_account_id = l_variance_account_id
1647 , last_update_date = sysdate
1648 , last_updated_by = FND_GLOBAL.user_id
1649 where po_distribution_id = l_dists.po_distribution_id
1650 and draft_id = p_draft_id;
1651 END IF;
1652
1653 IF (PO_LOG.d_stmt) THEN
1654 PO_LOG.stmt(d_module,d_position,'new_dest_charge_account_id', l_dest_charge_account_id);
1655 PO_LOG.stmt(d_module,d_position,'new_dest_variance_account_id', l_dest_variance_account_id);
1656 END IF;
1657 IF l_isSPSDistribution THEN
1658 --update current distribution dest_charge_account_id with l_dest_charge_account_id
1659 --update current distribution dest_variance_account_id with l_dest_variance_account_id
1660 IF NVL(p_draft_id, -1) = -1 THEN --bug 16628805
1661 update po_distributions_all
1662 set dest_charge_account_id = l_dest_charge_account_id
1663 , dest_variance_account_id = l_dest_variance_account_id
1664 , last_update_date = sysdate
1665 , last_updated_by = FND_GLOBAL.user_id
1666 where po_distribution_id = l_dists.po_distribution_id;
1667 ELSE
1668 update po_distributions_draft_all
1669 set dest_charge_account_id = l_dest_charge_account_id
1670 , dest_variance_account_id = l_dest_variance_account_id
1671 , last_update_date = sysdate
1672 , last_updated_by = FND_GLOBAL.user_id
1673 where po_distribution_id = l_dists.po_distribution_id
1674 and draft_id = p_draft_id;
1675 END IF;
1676 END IF;
1677 END IF;
1678
1679 d_position := 70;
1680 IF (PO_LOG.d_stmt) THEN
1681 PO_LOG.stmt(d_module,d_position,'l_return_status', l_return_status);
1682 END IF;
1683 IF (NOT l_success) AND l_fb_error_msg IS NOT NULL THEN
1684 IF (PO_LOG.d_stmt) THEN
1685 PO_LOG.stmt(d_module,d_position,'l_fb_error_msg', l_fb_error_msg);
1686 END IF;
1687 INSERT_REPORT_AUTONOMOUS(
1688 P_MESSAGE_TEXT => l_fb_error_msg
1689 , P_USER_ID => FND_GLOBAL.user_id
1690 , P_SEQUENCE_NUM => x_sequence
1691 , P_LINE_NUM => l_dists.line_num
1692 , p_shipment_num => l_dists.shipment_num
1693 , p_distribution_num => l_dists.distribution_num
1694 , p_transaction_id => l_dists.po_distribution_id
1695 , p_transaction_type => 'ACCOUNT_GENERATION'
1696 , p_message_type => 'E'
1697 , p_text_line => NULL
1698 , p_segment1 => l_dists.segment1
1699 , p_online_report_id => l_report_id
1700 , x_return_status => l_return_status
1701 );
1702 ELSE
1703
1704 IF (NOT l_charge_success) THEN
1705 IF (PO_LOG.d_stmt) THEN
1706 PO_LOG.stmt(d_module,d_position,'error msg', fnd_message.get_string('PO', 'PO_ALL_NO_CHARGE_FLEX'));
1707 END IF;
1708 INSERT_REPORT_AUTONOMOUS(
1709 P_MESSAGE_TEXT => NULL
1710 , P_USER_ID => FND_GLOBAL.user_id
1711 , P_SEQUENCE_NUM => x_sequence
1712 , P_LINE_NUM => l_dists.line_num
1713 , p_shipment_num => l_dists.shipment_num
1714 , p_distribution_num => l_dists.distribution_num
1715 , p_transaction_id => l_dists.po_distribution_id
1716 , p_transaction_type => 'ACCOUNT_GENERATION'
1717 , p_message_type => 'E'
1718 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_CHARGE_FLEX')
1719 , p_segment1 => l_dists.segment1
1720 , p_online_report_id => l_report_id
1721 , x_return_status => l_return_status
1722 );
1723 END IF;
1724
1725 IF (NOT l_accrual_success) THEN
1726 IF (PO_LOG.d_stmt) THEN
1727 PO_LOG.stmt(d_module,d_position,'error msg', fnd_message.get_string('PO', 'PO_ALL_NO_ACCRUAL_FLEX'));
1728 END IF;
1729 INSERT_REPORT_AUTONOMOUS(
1730 P_MESSAGE_TEXT => NULL
1731 , P_USER_ID => FND_GLOBAL.user_id
1732 , P_SEQUENCE_NUM => x_sequence
1733 , P_LINE_NUM => l_dists.line_num
1734 , p_shipment_num => l_dists.shipment_num
1735 , p_distribution_num => l_dists.distribution_num
1736 , p_transaction_id => l_dists.po_distribution_id
1737 , p_transaction_type => 'ACCOUNT_GENERATION'
1738 , p_message_type => 'E'
1739 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_ACCRUAL_FLEX')
1740 , p_segment1 => l_dists.segment1
1741 , p_online_report_id => l_report_id
1742 , x_return_status => l_return_status
1743 );
1744 END IF;
1745
1746 IF (NOT l_budget_success) THEN
1747 IF (PO_LOG.d_stmt) THEN
1748 PO_LOG.stmt(d_module,d_position,'error msg', fnd_message.get_string('PO', 'PO_ALL_NO_BUDGET_FLEX'));
1749 END IF;
1750 INSERT_REPORT_AUTONOMOUS(
1751 P_MESSAGE_TEXT => NULL
1752 , P_USER_ID => FND_GLOBAL.user_id
1753 , P_SEQUENCE_NUM => x_sequence
1754 , P_LINE_NUM => l_dists.line_num
1755 , p_shipment_num => l_dists.shipment_num
1756 , p_distribution_num => l_dists.distribution_num
1757 , p_transaction_id => l_dists.po_distribution_id
1758 , p_transaction_type => 'ACCOUNT_GENERATION'
1759 , p_message_type => 'E'
1760 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_BUDGET_FLEX')
1761 , p_segment1 => l_dists.segment1
1762 , p_online_report_id => l_report_id
1763 , x_return_status => l_return_status
1764 );
1765 END IF;
1766
1767 IF (NOT l_variance_success) THEN
1768 INSERT_REPORT_AUTONOMOUS(
1769 P_MESSAGE_TEXT => NULL
1770 , P_USER_ID => FND_GLOBAL.user_id
1771 , P_SEQUENCE_NUM => x_sequence
1772 , P_LINE_NUM => l_dists.line_num
1773 , p_shipment_num => l_dists.shipment_num
1774 , p_distribution_num => l_dists.distribution_num
1775 , p_transaction_id => l_dists.po_distribution_id
1776 , p_transaction_type => 'ACCOUNT_GENERATION'
1777 , p_message_type => 'E'
1778 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_VARIANCE_FLEX')
1779 , p_segment1 => l_dists.segment1
1780 , p_online_report_id => l_report_id
1781 , x_return_status => l_return_status
1782 );
1783 END IF;
1784
1785 IF (NOT l_dest_charge_success) THEN
1786 IF (PO_LOG.d_stmt) THEN
1787 PO_LOG.stmt(d_module,d_position,'error msg', fnd_message.get_string('PO', 'PO_ALL_NO_DEST_CHARGE_FLEX'));
1788 END IF;
1789 INSERT_REPORT_AUTONOMOUS(
1790 P_MESSAGE_TEXT => NULL
1791 , P_USER_ID => FND_GLOBAL.user_id
1792 , P_SEQUENCE_NUM => x_sequence
1793 , P_LINE_NUM => l_dists.line_num
1794 , p_shipment_num => l_dists.shipment_num
1795 , p_distribution_num => l_dists.distribution_num
1796 , p_transaction_id => l_dists.po_distribution_id
1797 , p_transaction_type => 'ACCOUNT_GENERATION'
1798 , p_message_type => 'E'
1799 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_DEST_CHARGE_FLEX')
1800 , p_segment1 => l_dists.segment1
1801 , p_online_report_id => l_report_id
1802 , x_return_status => l_return_status
1803 );
1804 END IF;
1805
1806 IF (NOT l_dest_variance_success) THEN
1807 IF (PO_LOG.d_stmt) THEN
1808 PO_LOG.stmt(d_module,d_position,'error msg', fnd_message.get_string('PO', 'PO_ALL_NO_DEST_VARIANCE_FLEX'));
1809 END IF;
1810 INSERT_REPORT_AUTONOMOUS(
1811 P_MESSAGE_TEXT => NULL
1812 , P_USER_ID => FND_GLOBAL.user_id
1813 , P_SEQUENCE_NUM => x_sequence
1814 , P_LINE_NUM => l_dists.line_num
1815 , p_shipment_num => l_dists.shipment_num
1816 , p_distribution_num => l_dists.distribution_num
1817 , p_transaction_id => l_dists.po_distribution_id
1818 , p_transaction_type => 'ACCOUNT_GENERATION'
1819 , p_message_type => 'E'
1820 , p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_DEST_VARIANCE_FLEX')
1821 , p_segment1 => l_dists.segment1
1822 , p_online_report_id => l_report_id
1823 , x_return_status => l_return_status
1824 );
1825 END IF;
1826
1827 END IF; --Error handling
1828
1829 END LOOP; --Loop each distribution
1830
1831 x_return_status := l_return_status;
1832
1833 IF (PO_LOG.d_proc) THEN
1834 PO_LOG.proc_end(d_module);
1835 END IF;
1836
1837 EXCEPTION
1838 WHEN OTHERS THEN
1839 x_return_status := 'E';
1840 ROLLBACK TO PO_ACCOUNT_HELPER_RB_ACC_SP;
1841 po_message_s.sql_error(d_pkg_name, d_api_name, d_position, SQLCODE, SQLERRM);
1842 fnd_msg_pub.add;
1843 RAISE;
1844 END online_rebuild_accounts;
1845
1846 --<Bug 15917496>: added insert_report_autonomous
1847 PROCEDURE INSERT_REPORT_AUTONOMOUS(
1848 P_MESSAGE_TEXT IN VARCHAR2
1849 , P_USER_ID IN NUMBER
1850 , P_SEQUENCE_NUM IN OUT NOCOPY po_online_report_text.sequence%TYPE
1851 , P_LINE_NUM IN po_online_report_text.line_num%TYPE
1852 , p_shipment_num IN po_online_report_text.shipment_num%TYPE
1853 , p_distribution_num IN po_online_report_text.distribution_num%TYPE
1854 , p_transaction_id IN po_online_report_text.transaction_id%TYPE
1855 , p_transaction_type IN po_online_report_text.transaction_type%TYPE
1856 , p_message_type IN po_online_report_text.message_type%TYPE
1857 , p_text_line IN po_online_report_text.text_line%TYPE
1858 , p_segment1 IN po_online_report_text.segment1%TYPE
1859 , p_online_report_id IN NUMBER
1860 , x_return_status IN OUT NOCOPY VARCHAR2
1861 ) IS
1862 PRAGMA AUTONOMOUS_TRANSACTION;
1863 d_api_name CONSTANT VARCHAR2(30) := 'insert_report_autonomous';
1864 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1865 d_position NUMBER;
1866
1867 l_message_text PO_ONLINE_REPORT_TEXT.text_line%TYPE;
1868 l_user_id NUMBER;
1869
1870 BEGIN
1871
1872 IF (PO_LOG.d_proc) THEN
1873 PO_LOG.proc_begin(d_module);
1874 PO_LOG.proc_begin(d_module, 'p_message_text', p_message_text);
1875 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1876 PO_LOG.proc_begin(d_module, 'p_text_line', p_text_line);
1877 PO_LOG.proc_begin(d_module, 'p_message_type', p_message_type);
1878 PO_LOG.proc_begin(d_module, 'p_online_report_id', p_online_report_id);
1879 END IF;
1880
1881 d_position := 10;
1882 x_return_status := 'E';
1883 l_user_id := NVL(p_user_id,0);
1884
1885 IF (PO_LOG.d_stmt) THEN
1886 PO_LOG.stmt(d_module,d_position,'l_user_id',l_user_id);
1887 END IF;
1888
1889 d_position := 20;
1890 IF (p_message_text IS NULL) THEN
1891 FND_MESSAGE.set_name('PO', 'PO_MSG_NULL_MESSAGE');
1892 l_message_text := FND_MESSAGE.get;
1893 ELSE
1894 l_message_text := p_message_text;
1895 END IF;
1896
1897 d_position := 30;
1898 INSERT INTO PO_ONLINE_REPORT_TEXT(
1899 online_report_id
1900 , sequence
1901 , last_updated_by
1902 , last_update_date
1903 , created_by
1904 , creation_date
1905 , line_num
1906 , shipment_num
1907 , distribution_num
1908 , transaction_id
1909 , transaction_type
1910 , message_type
1911 , text_line
1912 , segment1
1913 )
1914 VALUES(
1915 p_online_report_id
1916 , p_sequence_num
1917 , l_user_id
1918 , SYSDATE
1919 , l_user_id
1920 , SYSDATE
1921 , p_line_num
1922 , p_shipment_num
1923 , p_distribution_num
1924 , p_transaction_id
1925 , p_transaction_type
1926 , p_message_type
1927 , NVL(p_text_line,l_message_text)
1928 , p_segment1
1929 );
1930
1931 p_sequence_num := p_sequence_num + 1;
1932
1933 IF (PO_LOG.d_proc) THEN
1934 PO_LOG.proc_end(d_module);
1935 END IF;
1936
1937 COMMIT;
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 COMMIT;
1941 --add message to the stack and log a debug msg if necessary
1942 po_message_s.sql_error(d_pkg_name, d_api_name, d_position, SQLCODE, SQLERRM);
1943 fnd_msg_pub.add;
1944 RAISE;
1945 END insert_report_autonomous;
1946
1947 --Bug 16518955
1948
1949 -------------------------------------------------------------------------------
1950 -- ACRN Project Start
1951 --Start of Comments
1952 --Name: BUILD_ACRN_WRAP
1953 --Pre-reqs:
1954 -- None.
1955 --Modifies:
1956 -- None.
1957 --Locks:
1958 -- None.
1959 --Function:
1960 -- Derive ACRN for Award and Modification/ PAR via BWC
1961 -- capture all the distribution ids and their related data from
1962 -- po_distributions_merge_v to consider all possible values for ACRNs.
1963 -- CALL the BUILD_ACRN API to get the acrns
1964 -- Return the values to the calling programs.
1965 --Parameters:
1966 --IN:
1967 --p_header_id
1968 -- PO Header ID.
1969 --P_DIST_ID_TBL
1970 -- PL/SQL table which stores the PO distribution IDs.
1971 --P_CODE_ID_TBL
1972 -- PL/SQL table which stores the Code Combination IDs.
1973 --P_ACRN_TBL
1974 -- PL/SQL table which stores the input ACRN table.
1975 --P_LOA_TBL
1976 -- PL/SQL table which stores input LOA
1977 --P_CHANGE_STAT_TBL
1978 -- PL/SQL table which stores input change status
1979 -- OUT:
1980 -- X_ACRN_TBL
1981 -- PL/SQL table which stores the derived ACRN table values.
1982 -- X_RETURN_STATUS
1983 -- Return Status - S - Success , E- Error.
1984 -- X_RETURN_MESSAGE
1985 -- X_RETURN_MESSAGE stores the error messages PO_DUPLICATE_ACRN,PO_ACRN_CHARGE_ACCOUNT, PO_ACRN_RESERVE_LETTER,PO_ACRN_INVALID_LETTER
1986 -- X_MESSAGE_TOKEN
1987 -- X_MESSAGE_TOKEN stores the tokens for error messages PO_DUPLICATE_ACRN,PO_ACRN_CHARGE_ACCOUNT,
1988 -- PO_ACRN_RESERVE_LETTER,PO_ACRN_INVALID_LETTER
1989 --Notes:
1990 --End of Comments
1991
1992 PROCEDURE BUILD_ACRN_WRAP(p_header_id IN NUMBER,
1993 P_DIST_ID_TBL IN PO_TBL_NUMBER,
1994 P_CODE_ID_TBL IN PO_TBL_NUMBER,
1995 P_ACRN_TBL IN PO_TBL_VARCHAR30,
1996 P_LOA_TBL IN PO_TBL_VARCHAR30,
1997 P_CHANGE_STAT_TBL IN PO_TBL_VARCHAR30,
1998 P_REGEN_Y_N IN VARCHAR2,
1999 X_ACRN_TBL OUT NOCOPY PO_TBL_VARCHAR30,
2000 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2001 X_RETURN_MESSAGE OUT NOCOPY PO_TBL_VARCHAR2000,
2002 X_MESSAGE_TOKEN OUT NOCOPY PO_TBL_VARCHAR2000)
2003 IS
2004
2005 d_api_name CONSTANT VARCHAR2(30) := 'BUILD_ACRN_WRAP';
2006 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2007 d_position NUMBER;
2008
2009 L_DIST_ID_TBL PO_TBL_NUMBER ;
2010 L_CODE_ID_TBL PO_TBL_NUMBER ;
2011 L_LOA_TBL PO_TBL_VARCHAR30 ;
2012 L_CHANGE_STAT_TBL PO_TBL_VARCHAR30;
2013 L_ACRN VARCHAR2(2) :='';
2014
2015 O_ACRN_TBL PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
2016 N_ACRN_TBL PO_TBL_VARCHAR30;
2017 L_RETURN_STATUS VARCHAR2(22) := '';
2018 L_RETURN_MESSAGE PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2019 L_MESSAGE_TOKEN PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2020 l_charge_count NUMBER;
2021 l_acrn_code NUMBER := 1;
2022
2023 l_approved_flag VARCHAR2(1);
2024 l_doc_type varchar2(30);
2025 x_error VARCHAR2(1300);
2026 l_dist_id NUMBER;
2027 l_error_message PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2028 l_token varchar2(2000);
2029 l_dist_count number;
2030 l_key NUMBER;
2031 l_ACRN_TBL PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
2032 l_count NUMBER;
2033
2034
2035 BEGIN
2036 l_key := PO_CORE_S.get_session_gt_nextval;
2037
2038 IF (PO_LOG.d_proc) THEN
2039 PO_LOG.proc_end(d_module);
2040 END IF;
2041
2042 -- INSERT INTO PO_SESSION GT values for all the records you got from
2043 -- UI.
2044 /*
2045 Inserting the data passed to the procedure into the PO_SESSION_GT table
2046 KEY - SESSION KEY
2047 NUM1 - DISTRIBUTION ID
2048 CHAR1 - ACCOUNT NUMBER
2049 CHAR2 - LOA VALUE
2050 CHAR3 - Value based on which we have to generate the
2051 ACRN if LOA is not available consider Account number
2052 CHAR4 - Change status
2053 CHAR5 - ACRN
2054
2055 */
2056
2057
2058
2059 INSERT INTO po_session_gt
2060 (KEY,num1,char1,char2,char3,char4,char5)
2061 WITH test_tab AS
2062 (SELECT dist.dist_num,
2063 dist.rn rnum,
2064 acc.acc_nbr,
2065 loa.loa_val,
2066 NVL(loa.loa_val,acc.acc_nbr) gen_val,
2067 changestat.change_stat,
2068 acrn.acr
2069 FROM
2070 (SELECT column_value acr,rownum rn FROM TABLE(P_ACRN_TBL)
2071 ) acrn,
2072 (SELECT column_value acc_nbr,rownum rn FROM TABLE(P_CODE_ID_TBL)
2073 )acc,
2074 (SELECT column_value loa_val,rownum rn FROM TABLE(P_LOA_TBL)
2075 ) loa,
2076 (SELECT column_value dist_num,rownum rn FROM TABLE(P_DIST_ID_TBL)
2077 ) dist,
2078 (SELECT column_value change_stat,rownum rn FROM TABLE(P_CHANGE_STAT_TBL)
2079 ) changestat
2080 WHERE loa.rn = acc.rn
2081 AND acc.rn = acrn.rn
2082 AND loa.rn = acrn.rn
2083 AND dist.rn = loa.rn
2084 AND dist.rn = acc.rn
2085 AND dist.rn = acrn.rn
2086 AND changestat.rn = acrn.rn
2087 )
2088 SELECT l_key,dist_num,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
2089 l_count := SQL%rowcount;
2090 d_position := 10;
2091 IF (PO_LOG.d_stmt) THEN
2092 PO_LOG.stmt(d_module,d_position,'No Of records ',l_count);
2093 END IF;
2094
2095
2096 -- INSERT INTO PO_SESSION_GT all the values available from po_distributions_merge_v
2097 -- THIS IS TO MAKE SURE THAT WE WILL CONSIDER ALL DISTRIBUTIONS FOR THAT DOCUMENT.
2098 INSERT INTO po_session_gt
2099 (KEY,
2100 num1,
2101 char1,
2102 char2,
2103 char3,
2104 char4,
2105 char5)
2106 SELECT DISTINCT l_key,
2107 po_distribution_id,
2108 code_combination_id,
2109 clm_misc_loa,
2110 Nvl(clm_misc_loa, code_combination_id),
2111 change_status,
2112 acrn
2113 FROM po_distributions_merge_v
2114 WHERE po_header_id = p_header_id;
2115
2116 l_count := SQL%rowcount;
2117 d_position := 20;
2118 IF (PO_LOG.d_stmt) THEN
2119 PO_LOG.stmt(d_module,d_position,'No Of records ',l_count);
2120 END IF;
2121
2122 -- SELECT DISTINCT DISTRIBUTIONS FROM THE LOT TO CALL
2123 -- THIS IS TO MAKE SURE THAT WE WILL CONSIDER ALL DISTRIBUTIONS FOR THAT DOCUMENT.
2124
2125 SELECT DISTINCT num1 dist_id,
2126 To_number(char1) code_combination_id,
2127 char2,
2128 char4,
2129 Nvl(char5, '')
2130 bulk collect INTO l_dist_id_tbl, l_code_id_tbl, l_loa_tbl, l_change_stat_tbl,
2131 l_acrn_tbl
2132 FROM po_session_gt gt1
2133 WHERE KEY = l_key
2134 AND NOT EXISTS (SELECT 1
2135 FROM po_session_gt gt2
2136 WHERE KEY = l_key
2137 AND gt1.ROWID > gt2.ROWID
2138 AND gt1.num1 = gt2.num1);
2139 -- Calling build acrn values.
2140
2141 l_count := SQL%rowcount;
2142 d_position := 30;
2143 IF (PO_LOG.d_stmt) THEN
2144 PO_LOG.stmt(d_module,d_position,'No Of records ',l_count);
2145 END IF;
2146
2147
2148 PO_ACCOUNT_HELPER.BUILD_ACRN(p_header_id=>p_header_id,
2149 P_DIST_ID_TBL =>L_DIST_ID_TBL,
2150 P_CODE_ID_TBL=>L_CODE_ID_TBL,
2151 P_ACRN_TBL=>L_ACRN_TBL,
2152 P_LOA_TBL=>L_LOA_TBL,
2153 P_CHANGE_STAT_TBL =>L_CHANGE_STAT_TBL,
2154 P_REGEN_Y_N=>P_REGEN_Y_N,
2155 X_ACRN_TBL=>N_ACRN_TBL,
2156 X_RETURN_STATUS=>L_RETURN_STATUS,
2157 X_RETURN_MESSAGE=> L_RETURN_MESSAGE,
2158 X_MESSAGE_TOKEN =>L_MESSAGE_TOKEN);
2159
2160 delete from po_session_gt where key = l_key;
2161
2162 l_key := PO_CORE_S.get_session_gt_nextval;
2163
2164 INSERT INTO po_session_gt
2165 (KEY,num1,num2,char1,char2,char3,char4,char5)
2166 WITH test_tab AS
2167 (SELECT dist.dist_num,
2168 dist.rn rnum,
2169 acc.acc_nbr,
2170 loa.loa_val,
2171 NVL(loa.loa_val,acc.acc_nbr) gen_val,
2172 changestat.change_stat,
2173 acrn.acr
2174 FROM
2175 (SELECT column_value acr,rownum rn FROM TABLE(N_ACRN_TBL)
2176 ) acrn,
2177 (SELECT column_value acc_nbr,rownum rn FROM TABLE(L_CODE_ID_TBL)
2178 )acc,
2179 (SELECT column_value loa_val,rownum rn FROM TABLE(L_LOA_TBL)
2180 ) loa,
2181 (SELECT column_value dist_num,rownum rn FROM TABLE(L_DIST_ID_TBL)
2182 ) dist,
2183 (SELECT column_value change_stat,rownum rn FROM TABLE(L_CHANGE_STAT_TBL)
2184 ) changestat
2185 WHERE loa.rn = acc.rn
2186 AND acc.rn = acrn.rn
2187 AND loa.rn = acrn.rn
2188 AND dist.rn = loa.rn
2189 AND dist.rn = acc.rn
2190 AND dist.rn = acrn.rn
2191 AND changestat.rn = acrn.rn
2192 )
2193 SELECT l_key,dist_num,rnum,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
2194
2195 -- Retrieving the acrn values for the records passed by ui in the same order
2196 -- It has to be in the same order as the distribution ids table , so using loop.
2197 l_count := SQL%rowcount;
2198 d_position := 30;
2199 IF (PO_LOG.d_stmt) THEN
2200 PO_LOG.stmt(d_module,d_position,'No Of records ',l_count);
2201 END IF;
2202
2203
2204 for i in 1 .. P_DIST_ID_TBL.count LOOP
2205
2206 select char5
2207 into l_acrn
2208 from po_session_gt
2209 where
2210 key = l_key
2211 and num1 = P_DIST_ID_TBL(i);
2212 O_ACRN_TBL.extend;
2213 O_ACRN_TBL(i) := l_acrn;
2214 END LOOP;
2215
2216 X_ACRN_TBL := O_ACRN_TBL;
2217 X_RETURN_STATUS := L_RETURN_STATUS;
2218 X_RETURN_MESSAGE := L_RETURN_MESSAGE;
2219 X_MESSAGE_TOKEN := L_MESSAGE_TOKEN;
2220
2221
2222 delete from po_session_gt where key = l_key;
2223
2224 IF (PO_LOG.d_proc) THEN
2225 PO_LOG.proc_end(d_module);
2226 END IF;
2227
2228 EXCEPTION
2229 WHEN OTHERS THEN
2230 IF ( po_log.d_exc ) THEN
2231 po_log.Exc(d_module, d_position, SQLCODE ||SQLERRM);
2232 END IF;
2233 RAISE;
2234 END BUILD_ACRN_WRAP;
2235 --Bug 16518955
2236 END PO_ACCOUNT_HELPER;