[Home] [Help]
311: ,sum(decode(pkt.parent_bc_packet_id, NULL,(nvl(pkt.accounted_dr,0) - nvl(pkt.accounted_cr,0)),0)
312: ) pktRawAmt
313: Into l_pktbdamt
314: ,l_pktrawamt
315: from pa_bc_packets pkt
316: Where pkt.project_id = p_project_id
317: and pkt.task_id = p_task_id
318: and pkt.budget_version_id = p_budget_version_id
319: and pkt.document_line_id = p_document_line_id
569: cursor cur_potrxs IS
570: select 'Y'
571: from dual
572: where exists (select null
573: from pa_bc_packets pkt
574: where pkt.packet_id = p_packet_id
575: and pkt.document_type = 'PO'
576: );
577:
587: ,NULL) expenditure_type
588: ,decode(pt.burden_amt_display_method,'D'
589: ,decode(pkt.parent_bc_packet_id,NULL,'RAW','BURDEN')
590: ,'RAW') line_type
591: from pa_bc_packets pkt
592: ,pa_projects_all pp
593: ,pa_project_types_all pt
594: where pkt.packet_id = p_packet_id
595: and pkt.document_line_id is NOT NULL -- with R12 this check is not sufficient to find if the PO is an CWK PO
655: select 'Y'
656: from dual
657: where exists
658: (select null
659: from pa_bc_packets pkts1
660: where pkts1.document_line_id is NOT NULL
661: and pkts1.status_code in ('A','P','C','I')
662: and substr(NVL(pkts1.result_code,'P'),1,1) = 'P'
663: and nvl(pkts1.funds_process_mode,'T') <> 'B'
700: decode(pkt.document_type,'PO'
701: ,decode(pkt.parent_bc_packet_id,NULL ,0
702: ,decode(lv_bd_disp_method, 'D'
703: ,decode(pkt.expenditure_type,lv_expenditure_type,1,0),1)),0)) relevd_comm_bd_amt
704: FROM pa_bc_packets pkt
705: WHERE pkt.project_id = lv_project_id
706: AND pkt.budget_version_id = lv_budget_version_id
707: AND pkt.task_id = lv_task_id
708: AND pkt.document_line_id = lv_document_line_id
844: CLOSE cur_cwk_amts;
845: pa_funds_control_pkg.log_message(p_msg_token1 => 'CommRawAmt['||l_comm_raw_amt||']CommbdAmt['||l_comm_bd_amt||
846: ']RelvdComm['||l_relvd_comm_raw_amt||']RelvdBd['||l_relvd_comm_bd_amt||']');
847: l_rows_updated := 0;
848: UPDATE pa_bc_packets pkt
849: SET pkt.comm_tot_raw_amt = nvl(pkt.comm_tot_raw_amt,0) +
850: decode(p_calling_module,'GL',decode(cwk.line_type,'RAW',nvl(l_comm_raw_amt,0),0),0)
851: ,pkt.comm_tot_bd_amt = nvl(pkt.comm_tot_bd_amt,0) +
852: decode(p_calling_module,'GL'
899: l_rows_updated := 0;
900: IF nvl(l_commsummrec,'N') = 'N' and nvl(l_pktsummrec,'N') = 'N' and l_po_exists = 'Y' Then
901: l_stage := 'Updating packets summary record flag';
902: pa_funds_control_pkg.log_message(p_msg_token1=> l_stage);
903: UPDATE pa_bc_packets pkt
904: SET pkt.summary_record_flag = decode (pkt.summary_record_flag,NULL,'Y',pkt.summary_record_flag)
905: WHERE pkt.packet_id = p_packet_id
906: AND pkt.document_type = 'PO'
907: AND nvl(pkt.funds_process_mode,'N') = 'T'
916: pkt.parent_bc_packet_id is NOT NULL
917: and pkt.expenditure_type = cwk.expenditure_type
918: and cwk.burden_amt_display_method = 'D'
919: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
920: from pa_bc_packets pkt1
921: where pkt1.packet_id = pkt.packet_id
922: and pkt1.project_id = pkt.project_id
923: and pkt1.task_id = pkt.task_id
924: and pkt1.budget_version_id = pkt.budget_version_id
932: ( -- sep line burden lines only one raw line should be stamped with summary record info
933: pkt.parent_bc_packet_id is NULL
934: and cwk.burden_amt_display_method = 'D'
935: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
936: from pa_bc_packets pkt1
937: where pkt1.packet_id = pkt.packet_id
938: and pkt1.project_id = pkt.project_id
939: and pkt1.task_id = pkt.task_id
940: and pkt1.budget_version_id = pkt.budget_version_id
948: ( -- raw line should be stamped with summary record info if display method is same
949: pkt.parent_bc_packet_id is NULL
950: and cwk.burden_amt_display_method <> 'D'
951: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
952: from pa_bc_packets pkt1
953: where pkt1.packet_id = pkt.packet_id
954: and pkt1.project_id = pkt.project_id
955: and pkt1.task_id = pkt.task_id
956: and pkt1.budget_version_id = pkt.budget_version_id
1015: If l_cwk_multiplier is NOT NULL Then
1016: l_cwk_multiplier := pa_currency.round_trans_currency_amt
1017: (l_cwk_multiplier,g_acct_currency_code);
1018: End If;
1019: UPDATE pa_bc_packets cmt
1020: SET cmt.compiled_multiplier = decode (cmt.document_line_id,NULL,cmt.compiled_multiplier
1021: ,l_cwk_multiplier)
1022: WHERE cmt.summary_record_flag = 'Y'
1023: AND cmt.document_line_id is NOT NULL
1074: -- Obsolete FUNCTION check_encum_type
1075:
1076: ----------------------------------------------------------------------------------------
1077: --This api copies the unreserved transaction into to the packet.
1078: -- when the calling mode is unreserved then copy all the transactions from pa_bc_packets
1079: -- for the old packet id(which is funds cheked and approved) to new packet by swapping the amount
1080: -- columns and all other columns values remain same. Approve the packets with status Approved
1081: -- donot create encumbrance liquidation as GL funds checker will create reversing lines
1082: -- for the old packet id and donot populate burden rows / donot check for the unreserved packet
1141: FETCH cur_packet INTO l_packet_id;
1142: CLOSE cur_packet;
1143:
1144:
1145: INSERT INTO pa_bc_packets
1146: ( ---- who columns------
1147: request_id,
1148: program_id,
1149: program_application_id,
1215: l_update_login,
1216: sysdate,
1217: l_update_login,
1218: l_packet_id,
1219: pa_bc_packets_s.nextval,
1220: pbc.budget_version_id,
1221: pbc.project_id,
1222: pbc.task_id,
1223: pbc.expenditure_type,
1265: pbc.source_event_id,
1266: pbc.document_distribution_type,
1267: pbc.document_header_id_2
1268: FROM
1269: pa_bc_packets pbc
1270: WHERE pbc.packet_id = x_packet_id;
1271:
1272: If sql%rowcount > 0 then
1273: --assign the new packet id to out parameter
1279: and p_reference1 is not null and p_reference2 is not null then -- unreserved
1280: OPEN cur_packet ;
1281: FETCH cur_packet INTO l_packet_id;
1282: CLOSE cur_packet;
1283: INSERT INTO pa_bc_packets
1284: ( ---- who columns------
1285: request_id,
1286: program_id,
1287: program_application_id,
1353: l_update_login,
1354: sysdate,
1355: l_update_login,
1356: l_packet_id,
1357: pa_bc_packets_s.nextval,
1358: pbc.budget_version_id,
1359: pbc.project_id,
1360: pbc.task_id,
1361: pbc.expenditure_type,
1403: pbc.source_event_id,
1404: pbc.document_distribution_type,
1405: pbc.document_header_id_2
1406: FROM
1407: pa_bc_packets pbc
1408: WHERE pbc.packet_id = x_packet_id
1409: AND document_type in ('CC_C_CO','CC_P_CO')
1410: AND document_header_id = p_reference2;
1411:
1444: RETURN VARCHAR2 IS
1445:
1446: l_return_flag varchar2(10) := 'N';
1447: l_req_found_flag varchar2(1) := 'N';
1448: l_parent_bc_packet_id PA_BC_PACKETS.PARENT_BC_PACKET_ID%TYPE;
1449:
1450: -- This cursor picks the po details for the given requisition
1451: CURSOR get_podetails Is
1452: SELECT 'PO'
1465: AND comm.document_type = 'REQ'
1466: UNION ALL
1467: SELECT null bc_commitment_id,
1468: pbc.parent_bc_packet_id
1469: FROM pa_bc_packets pbc
1470: WHERE pbc.document_distribution_id = p_req_distribution_id
1471: AND pbc.document_header_id = p_req_header_id
1472: AND pbc.document_type = 'REQ'
1473: AND pbc.balance_posted_flag = 'N'
1572: l_po_found_flag VARCHAR2(1) := 'N';
1573: l_cc_found_flag VARCHAR2(1) := 'N';
1574: l_return_flag VARCHAR2(1) := 'N';
1575: l_cc_det_pf_line_id varchar2(30); --Bug 6393954 changed from number to varchar2
1576: L_PARENT_BC_PACKET_ID PA_BC_PACKETS.PARENT_BC_PACKET_ID%TYPE;
1577: L_CC_HEADER_ID varchar2(30); --Bug 6393954 changed from PO_DISTRIBUTIONS_ALL.PO_HEADER_ID%TYPE
1578: L_PO_DESTINATION_TYPE PO_DISTRIBUTIONS_ALL.DESTINATION_TYPE_CODE%TYPE;
1579:
1580: -- this cursor checks whehter the AP is based on Purchase Order if so then
1597: AND comm.document_type = p_document_type
1598: AND NVL(comm.document_header_id_2 ,-99) = NVL(p_po_release_id,-99)
1599: UNION ALL
1600: SELECT pbc.parent_bc_packet_id
1601: FROM pa_bc_packets pbc
1602: WHERE pbc.document_distribution_id = p_distribution_id
1603: AND pbc.document_header_id = p_header_id
1604: AND pbc.document_type = p_document_type
1605: AND pbc.balance_posted_flag = 'N'
1779: g_tab_period_name(p_new_rec_index) := g_tab_period_name(p_copy_from_index);
1780: g_tab_parent_reversal_id(p_new_rec_index) := g_tab_parent_reversal_id(p_copy_from_index); -- Bug 5406690
1781:
1782: -- Bug 5406690
1783: select pa_bc_packets_s.nextval
1784: into g_tab_bc_packet_id(p_new_rec_index)
1785: from dual;
1786:
1787: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2161: PRAGMA AUTONOMOUS_TRANSACTION;
2162:
2163: CURSOR c_pkt_SOB IS
2164: SELECT DISTINCT pbc.set_of_books_id
2165: FROM pa_bc_packets pbc
2166: WHERE pbc.packet_id = p_packet_id
2167: AND pbc.status_code = 'I'
2168: AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F');
2169:
2177: pbc.budget_version_id,
2178: pm.entry_level_code,
2179: DECODE(pm.entry_level_code,'P',0,pt.top_task_id) top_task_id,
2180: pbc.resource_list_member_id
2181: FROM pa_bc_packets pbc,
2182: pa_tasks pt,
2183: pa_budget_versions bv,
2184: pa_budget_entry_methods pm
2185: WHERE pbc.packet_id = p_packet_id
2198: WHERE gl.application_id = 101
2199: AND gl.set_of_books_id = p_sob_id
2200: AND gl.period_name = p_period_name;
2201:
2202: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
2203: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
2204: l_return_status VARCHAR2(10) := 'S';
2205: l_error_message_code VARCHAR2(200) := NULL;
2206: l_gl_start_date DATE;
2199: AND gl.set_of_books_id = p_sob_id
2200: AND gl.period_name = p_period_name;
2201:
2202: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
2203: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
2204: l_return_status VARCHAR2(10) := 'S';
2205: l_error_message_code VARCHAR2(200) := NULL;
2206: l_gl_start_date DATE;
2207:
2266: pa_funds_control_pkg.log_message(p_msg_token1 => 'l_return_status = '||l_return_status);
2267: pa_funds_control_pkg.log_message(p_msg_token1 => 'l_error_message_code = '||l_error_message_code);
2268: End if;
2269:
2270: -- Fail pa bc packets if there is any error while deriving the budget ccid value
2271: IF l_return_status = 'E' OR NVL(l_budget_ccid,-999) = -999 OR NVL(l_budget_line_id,-999) = -999 THEN
2272:
2273:
2274: UPDATE pa_bc_packets
2270: -- Fail pa bc packets if there is any error while deriving the budget ccid value
2271: IF l_return_status = 'E' OR NVL(l_budget_ccid,-999) = -999 OR NVL(l_budget_line_id,-999) = -999 THEN
2272:
2273:
2274: UPDATE pa_bc_packets
2275: set budget_ccid = l_budget_ccid,
2276: budget_line_id = l_budget_line_id,
2277: status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
2278: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F132'),
2296:
2297: ELSE
2298:
2299:
2300: UPDATE pa_bc_packets
2301: set budget_ccid = l_budget_ccid,
2302: budget_line_id = l_budget_line_id
2303: WHERE packet_id = p_packet_id
2304: AND status_code ='I'
2401: PRAGMA AUTONOMOUS_TRANSACTION;
2402:
2403: CURSOR c_pkt_status IS
2404: SELECT 1
2405: FROM pa_bc_packets
2406: WHERE packet_id = p_packet_id
2407: AND (status_code in ('F','T','R')
2408: OR SUBSTR (result_code,1,1) = 'F');
2409:
2428: IF l_counter > 0 THEN
2429:
2430: x_return_code := 'F' ;
2431:
2432: UPDATE pa_bc_packets a
2433: SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
2434: a.result_code = DECODE( SUBSTR (result_code,1,1),'F',result_code,'F170'),
2435: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F170'),
2436: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F170'),
2471: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2472: pa_funds_control_pkg.log_message(p_msg_token1 => 'FAIL_NULL_EVENT_PKTS : Start ');
2473: END IF;
2474:
2475: UPDATE pa_bc_packets a
2476: SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
2477: a.result_code = 'F168' ,
2478: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F168'),
2479: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F168'),
2509: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2510: pa_funds_control_pkg.log_message(p_msg_token1 => 'FAIL_DANGLING_PKTS : Start ');
2511: END IF;
2512:
2513: UPDATE pa_bc_packets
2514: SET status_code = 'T',
2515: result_code = 'F142',
2516: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F142'),
2517: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F142'),
2536: -- Called from PSA_BC_XLA_PVT.Budgetary_control after creating events for AP/PO/REQ.
2537: -- and before It performs following tasks :
2538: -- 1. Driving table for this procedure is psa_bc_xla_events_gt .Picks all AP/PO/REQ events
2539: -- created by BCPSA FC engine.
2540: -- 2. Inserts raw records into pa_bc_packets by fetching PO/REQ data from po_bc_distributions
2541: -- and AP data from ap_invoice_distributions_all table for all events in psa_bc_xla_events_gt.
2542: -- 3. Fires populate_burden_cost procedure to Insert burden records for above raw components
2543: -- 4. Fires pa_funds_control_pkg.derive_rlmi to derive resource_list_member_id on bc packets
2544: -- 5. Fires pa_funds_control_utils.get_budegt_ccid to derive budget_ccid and budget_line_id
2631: -- Bug 5403775 : Added below columns to derive pkt reference columns for backing docs such that
2632: -- they will point to the main doc
2633: POBC.origin_sequence_num,
2634: pobc.applied_to_dist_id_2,
2635: pa_bc_packets_s.nextval bc_packet_id -- Bug 5406690
2636: FROM po_bc_distributions pobc ,
2637: po_encumbrance_gt pogt,
2638: psa_bc_xla_events_gt xlaevt
2639: WHERE pobc.ae_event_id = xlaevt.event_id
3004:
3005:
3006: CURSOR c_count_success_recs(p_packet_id NUMBER) IS
3007: SELECT count(*)
3008: FROM pa_bc_packets
3009: WHERE packet_id = p_packet_id
3010: AND (NVL(status_code,'I') NOT IN ('F','T','R')
3011: AND SUBSTR (NVL(result_code,'P'),1,1) <> 'F');
3012:
3031: l_req_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
3032: l_req_org_id PO_HEADERS_ALL.ORG_ID%TYPE;
3033: l_Po_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
3034: l_po_org_id PO_HEADERS_ALL.ORG_ID%TYPE;
3035: l_packet_id pa_bc_packets.packet_id%TYPE;
3036: l_prepay_exists VARCHAR2(1);
3037: l_stdinvoice_exists VARCHAR2(1);
3038:
3039: BEGIN
3083: pa_funds_control_pkg.log_message(p_msg_token1 => 'CREATE_PROJ_ENCUMBRANCE_EVENTS : p_partial_flag = '||p_partial_flag);
3084: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling init_plsql_tabs to initialize the pl/sql tabs ');
3085: End if;
3086:
3087: -- Initialize the pl/sql table which stores pa_bc_packets records
3088: init_plsql_tabs;
3089:
3090: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
3091: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling init_util_variables');
3212: CLOSE cur_ap_bc_dist ;
3213:
3214: -- Bug 5406690
3215: FOR i IN 1..g_tab_doc_header_id.count LOOP
3216: select pa_bc_packets_s.nextval
3217: into g_tab_bc_packet_id(i)
3218: from dual;
3219: END LOOP;
3220:
3669: g_tab_org_id(l_index) := l_po_org_id ;
3670:
3671: -- End of Code to populate vendor_id and org_id
3672:
3673: END IF; --IF g_pa_bc_packets_tab(l_index).document_type ='REQ' THEN
3674:
3675: -- Bug 5403775 : Below logic derives reference columns on backing documents such that
3676: -- they will point to main document
3677: -- Eg :
3780: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
3781: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling update_cwk_pkt_lines ');
3782: End if;
3783:
3784: -- To update CWK related columns of pa_bc_packets PO records
3785: update_cwk_pkt_lines (p_calling_module => 'GL',
3786: p_packet_id => l_packet_id);
3787:
3788: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
3917:
3918: END CREATE_PROJ_ENCUMBRANCE_EVENTS;
3919:
3920: ----------------------------------------------------------------------------------
3921: --This is an Autonmous api which inserts records into the pa bc packets from
3922: -- plsql tables and commits
3923: ---------------------------------------------------------------------------------
3924: PROCEDURE Load_pkts (p_packet_id IN NUMBER,
3925: p_bc_mode IN VARCHAR2,
3954: l_update_login := -1;
3955: End if;
3956:
3957: FORALL i in 1 .. g_tab_set_of_books_id.count
3958: INSERT INTO PA_BC_PACKETS (
3959: request_id,
3960: program_id,
3961: program_application_id,
3962: program_update_date,
4075: );
4076:
4077:
4078: /* Added for Bug fix: 3086398 */
4079: Update pa_bc_packets
4080: set status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
4081: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,result_code),
4082: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,result_code),
4083: task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,result_code),
4097: ---- the same while creating liquidation entries in IGC interface table.
4098:
4099: IF p_calling_module = 'CBC' THEN
4100: FORALL I in 1..g_tab_rowid.count
4101: Update pa_bc_packets set gl_row_number = g_tab_rowid(i)
4102: where bc_packet_id = g_tab_bc_packet_id (i); END IF;
4103:
4104: -------->6599207 ------END
4105:
4176: CURSOR is_cwk_po_unreserve is
4177: select distinct pbc.document_header_id,
4178: pbc.document_line_id,
4179: org_id
4180: from pa_bc_packets pbc
4181: where packet_id = p_packet_id
4182: and document_type = 'PO'
4183: and (nvl(accounted_dr,0) - nvl(accounted_cr,0)) < 0
4184: and not exists ( select 1
4181: where packet_id = p_packet_id
4182: and document_type = 'PO'
4183: and (nvl(accounted_dr,0) - nvl(accounted_cr,0)) < 0
4184: and not exists ( select 1
4185: from pa_bc_packets
4186: where packet_id = p_packet_id
4187: and document_type <> 'PO') ;
4188:
4189: PROCEDURE Rate_PO_Unreserve( p_packet_id number,
4192: p_accounted_cr_tab pa_plsql_datatypes.NumTabTyp) is
4193: PRAGMA AUTONOMOUS_TRANSACTION;
4194: begin
4195: FORALL i IN p_bc_packet_id_tab.first .. p_bc_packet_id_tab.last
4196: update pa_bc_packets
4197: set entered_cr = p_entered_cr_tab(i),
4198: accounted_cr = p_accounted_cr_tab(i),
4199: entered_dr = 0,
4200: accounted_dr = 0
4214: ** as actuals. In this case po receipt is not created and po cancellation
4215: ** would liquidate without considering the cwk timecards. This is resulting
4216: ** into -ve balance.
4217: ** Resolution :
4218: ** We are finding out the summary amounts from pa bc packets or bc commitments
4219: ** for a po line. Summary record has total raw amount and total amout relieved
4220: ** we are checking if the po credit is creted in bc packets and comparing the
4221: ** credit amount with the po balance at line level in the summary record.
4222: ** BC packet record is updated with the amount ( whichever is less credit amount or
4254: l_amt_balance_tab,
4255: g_bdamt_balance_tab,
4256: g_doc_line_id_tab,
4257: g_burden_type_tab
4258: from pa_bc_packets pbc ,
4259: pa_projects_all pp,
4260: pa_project_types ppt
4261: where pbc.document_type = 'PO'
4262: and pbc.summary_record_flag = 'Y'
4266: and pbc.parent_bc_packet_id is NULL
4267: and pbc.project_id = pp.project_id
4268: and pp.project_type = ppt.project_type
4269: and (pbc.project_id, task_id) in ( select distinct project_id, task_id
4270: from pa_bc_packets
4271: where packet_id = p_packet_id
4272: and document_header_id = c_rate_po.document_header_id
4273: and document_line_id = c_rate_po.document_line_id )
4274: and pbc.packet_id < p_packet_id ;
4299: and pbc.document_header_id = c_rate_po.document_header_id
4300: and pbc.project_id = pp.project_id
4301: and pp.project_type = ppt.project_type
4302: and (pbc.project_id, pbc.task_id) in ( select distinct project_id, task_id
4303: from pa_bc_packets
4304: where packet_id = p_packet_id
4305: and document_header_id = c_rate_po.document_header_id
4306: and document_line_id = c_rate_po.document_line_id )
4307: and pbc.packet_id < p_packet_id ;
4330: l_entered_dr_tab,
4331: l_entered_cr_tab,
4332: l_accounted_dr_tab,
4333: l_accounted_cr_tab
4334: from pa_bc_packets
4335: where packet_id = p_packet_id
4336: and document_line_id = g_doc_line_id_tab(line_indx)
4337: and document_header_id = c_rate_po.document_header_id
4338: and project_id = g_project_id_tab(line_indx)
4435: If p_calling_module in ('DISTBTC','TRXNIMPORT','DISTVIADJ','DISTERADJ','TRXIMPORT','DISTCWKST')
4436: AND p_mode not in ('A','U') then
4437:
4438: /* PA.M changes for contingent worker functionality */
4439: /* This check is not required as this is done even before inserting the record into pa_bc_packets
4440: * during distribute process. Having this check is redudant
4441: If p_calling_module = 'DISTCWKST' then
4442: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling checkCWKbdExp Api to check burden cost codes');
4443: -- check for the burden cost codes changed if so error out the transactions
4447: );
4448: pa_funds_control_pkg.log_message(p_msg_token1 => 'End of checkCWKbdExp Api');
4449: End If;
4450: ***/
4451: /* This Query insert records into pa_bc_packets
4452: * for the projects which is of burden on same
4453: * expenditure item
4454: */
4455:
4452: * for the projects which is of burden on same
4453: * expenditure item
4454: */
4455:
4456: INSERT INTO pa_bc_packets
4457: ( ---- who columns------
4458: request_id,
4459: program_id,
4460: program_application_id,
4517: sysdate,
4518: l_update_login,
4519: ------ main columns-----------
4520: pbc.packet_id,
4521: pa_bc_packets_s.nextval,
4522: pbc.budget_version_id,
4523: pbc.project_id,
4524: pbc.task_id,
4525: pbc.expenditure_type,
4613: ,pbc.reference1
4614: ,pbc.reference2
4615: ,pbc.reference3
4616: ,pbc.exp_item_id
4617: FROM pa_bc_packets pbc
4618: WHERE pbc.packet_id = p_packet_id
4619: AND pbc.parent_bc_packet_id = -1
4620: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
4621: AND pa_funds_control_pkg.check_bdn_on_sep_item
4630:
4631: pa_funds_control_pkg.log_message(p_msg_token1 =>
4632: 'Num of records inserted ='||sql%rowcount);
4633:
4634: /* This Query insert records into pa_bc_packets
4635: * for the projects which is of burden on different
4636: * expenditure item
4637: */
4638:
4635: * for the projects which is of burden on different
4636: * expenditure item
4637: */
4638:
4639: INSERT INTO pa_bc_packets
4640: ( ---- who columns------
4641: request_id,
4642: program_id,
4643: program_application_id,
4700: sysdate,
4701: l_update_login,
4702: ------ main columns-----------
4703: pbc.packet_id,
4704: pa_bc_packets_s.nextval,
4705: pbc.budget_version_id,
4706: pbc.project_id,
4707: pbc.task_id,
4708: et.expenditure_type,
4768: pa_cost_base_exp_types cbet,
4769: pa_ind_rate_schedules_all_bg irs,
4770: pa_ind_compiled_sets ics,
4771: pa_compiled_multipliers cm,
4772: pa_bc_packets pbc
4773: WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
4774: AND cb.cost_base = cbet.cost_base
4775: AND cb.cost_base_type = cbet.cost_base_type
4776: AND et.expenditure_type = icc.expenditure_type
4817: --
4818: -- Bug : 3703180
4819: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
4820: -- Resolution : Compare the burden cost calculated with the summary record in
4821: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
4822: -- table if summary has amounts less than the calculated in pa bc packets.
4823: -- If we have zero burden in pa bc packets then we use the entire amounts in
4824: -- the summary record.
4825: --
4818: -- Bug : 3703180
4819: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
4820: -- Resolution : Compare the burden cost calculated with the summary record in
4821: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
4822: -- table if summary has amounts less than the calculated in pa bc packets.
4823: -- If we have zero burden in pa bc packets then we use the entire amounts in
4824: -- the summary record.
4825: --
4826: PROCEDURE update_cwk_po_burden(p_packet_id NUMBER ) is
4819: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
4820: -- Resolution : Compare the burden cost calculated with the summary record in
4821: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
4822: -- table if summary has amounts less than the calculated in pa bc packets.
4823: -- If we have zero burden in pa bc packets then we use the entire amounts in
4824: -- the summary record.
4825: --
4826: PROCEDURE update_cwk_po_burden(p_packet_id NUMBER ) is
4827: PRAGMA AUTONOMOUS_TRANSACTION;
4865: l_entered_dr_tab,
4866: l_entered_cr_tab,
4867: l_accounted_dr_tab,
4868: l_accounted_cr_tab
4869: from pa_bc_packets
4870: where packet_id = p_packet_id
4871: and document_line_id = g_doc_line_id_tab(line_indx)
4872: and project_id = g_project_id_tab(line_indx)
4873: and task_id = g_task_id_tab(line_indx)
4877: IF l_bc_packet_id_tab.count > 0 THEN
4878: --
4879: -- bug 3703180
4880: -- compare the burden cost with the summary record and use the summary burden cost
4881: -- if calculated burden is ZERO or less then the pa bc packets burden.
4882: --
4883:
4884: for pkt_rec in 1..l_bc_packet_id_tab.count loop
4885:
4911: end loop ;
4912:
4913: --
4914: -- BUG 3703180
4915: -- Update the calculated burden cost to pa bc packets.
4916: --
4917: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
4918: update pa_bc_packets
4919: set entered_cr = l_entered_cr_tab(i),
4914: -- BUG 3703180
4915: -- Update the calculated burden cost to pa bc packets.
4916: --
4917: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
4918: update pa_bc_packets
4919: set entered_cr = l_entered_cr_tab(i),
4920: accounted_cr = l_accounted_cr_tab(i),
4921: entered_dr = 0,
4922: accounted_dr = 0
4926:
4927: --
4928: -- BUG 3703180
4929: -- Different line burdening setup.
4930: -- We determine the summary record from pa bc packets or pa bc commitments all table.
4931: -- compare the summary amounts with the pa bc packets burden and use the one less than
4932: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
4933: -- summary table record.
4934: --
4927: --
4928: -- BUG 3703180
4929: -- Different line burdening setup.
4930: -- We determine the summary record from pa bc packets or pa bc commitments all table.
4931: -- compare the summary amounts with the pa bc packets burden and use the one less than
4932: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
4933: -- summary table record.
4934: --
4935: elsif g_burden_type_tab(line_indx) = 'DIFFERENT' then
4928: -- BUG 3703180
4929: -- Different line burdening setup.
4930: -- We determine the summary record from pa bc packets or pa bc commitments all table.
4931: -- compare the summary amounts with the pa bc packets burden and use the one less than
4932: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
4933: -- summary table record.
4934: --
4935: elsif g_burden_type_tab(line_indx) = 'DIFFERENT' then
4936:
4937: l_exp_type_tab.delete ;
4938:
4939: --
4940: -- BUG 3703180
4941: -- Determine the burden expenditure type from pa bc packets.
4942: --
4943: select distinct pbc.expenditure_type
4944: bulk collect into l_exp_type_tab
4945: from pa_bc_packets pbc
4941: -- Determine the burden expenditure type from pa bc packets.
4942: --
4943: select distinct pbc.expenditure_type
4944: bulk collect into l_exp_type_tab
4945: from pa_bc_packets pbc
4946: where packet_id = p_packet_id
4947: and document_type = 'PO'
4948: and parent_bc_packet_id is not NULL
4949: and project_id = g_project_id_tab(line_indx)
4952:
4953: IF l_exp_type_tab.count > 0 THEN
4954: --
4955: -- BUG 3703180
4956: -- Determine the summary amounts from pa bc packets or pa bc commitments table.
4957: --
4958: for indx in 1..l_exp_type_tab.count loop
4959:
4960: g_bdamt_balance_tab.delete ;
4962: ** 3703180 : Determine the summary record amounts.. for a line
4963: */
4964: select (nvl(comm_tot_bd_amt,0) - nvl(comm_bd_amt_relieved,0) ) bd_amount
4965: bulk collect into g_bdamt_balance_tab
4966: from pa_bc_packets
4967: where document_type = 'PO'
4968: and summary_record_flag = 'Y'
4969: and document_line_id = g_doc_line_id_tab(line_indx)
4970: and status_code in ('A', 'C')
5003: l_accounted_cr_tab.delete ;
5004:
5005: --
5006: -- BUG 3703180
5007: -- Get the pa bc packets burden cost in the descending order
5008: --
5009: select bc_packet_id,
5010: entered_dr,
5011: entered_cr,
5015: l_entered_dr_tab,
5016: l_entered_cr_tab,
5017: l_accounted_dr_tab,
5018: l_accounted_cr_tab
5019: from pa_bc_packets
5020: where packet_id = p_packet_id
5021: and document_line_id = g_doc_line_id_tab(line_indx)
5022: and project_id = g_project_id_tab(line_indx)
5023: and task_id = g_task_id_tab(line_indx)
5063: end loop ;
5064:
5065: --
5066: -- BUG 3703180
5067: -- Update the burden cost to pa bc packets.
5068: --
5069: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5070: update pa_bc_packets
5071: set entered_cr = l_entered_cr_tab(i),
5066: -- BUG 3703180
5067: -- Update the burden cost to pa bc packets.
5068: --
5069: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5070: update pa_bc_packets
5071: set entered_cr = l_entered_cr_tab(i),
5072: accounted_cr = l_accounted_cr_tab(i),
5073: entered_dr = 0,
5074: accounted_dr = 0
5092:
5093:
5094: ---------------------------------------------------------------------
5095: -- this api creates the burden lines for the purchase order and
5096: -- supplier invoice lines in pa_bc_packets
5097: ---------------------------------------------------------------------
5098: FUNCTION create_ap_po_bdn_lines
5099: (p_packet_id IN NUMBER,
5100: p_bc_packet_id IN NUMBER,
5125: l_amount NUMBER ;
5126: l_prev_multiplier varchar2(1) ;
5127: l_tab_multiplier pa_plsql_datatypes.NumTabTyp ;
5128: l_tab_icc_exp_type pa_plsql_datatypes.char50TabTyp;
5129: l_doc_header_id pa_bc_packets.document_header_id%TYPE ;
5130: l_doc_distribution_id pa_bc_packets.document_distribution_id%TYPE ;
5131:
5132: /* Commented as part of Bug 5406690
5133: CURSOR C_ap_parent_reversal_dist IS --Bug 5515095
5126: l_prev_multiplier varchar2(1) ;
5127: l_tab_multiplier pa_plsql_datatypes.NumTabTyp ;
5128: l_tab_icc_exp_type pa_plsql_datatypes.char50TabTyp;
5129: l_doc_header_id pa_bc_packets.document_header_id%TYPE ;
5130: l_doc_distribution_id pa_bc_packets.document_distribution_id%TYPE ;
5131:
5132: /* Commented as part of Bug 5406690
5133: CURSOR C_ap_parent_reversal_dist IS --Bug 5515095
5134: SELECT parent_reversal_id
5162: (nvl(entered_dr,0) - NVL(entered_cr,0)) amount
5163: into l_doc_header_id,
5164: l_doc_distribution_id,
5165: l_amount
5166: from pa_bc_packets
5167: where packet_id = p_packet_id
5168: and bc_packet_id = p_bc_packet_id ;
5169:
5170: IF p_doc_type = 'AP' THEN
5224: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_commitments packet id '||l_max_packet_id);
5225:
5226: select max(packet_id)
5227: into l_max_packet_id_b
5228: from pa_bc_packets gbc1
5229: where packet_id <> p_packet_id
5230: and packet_id > NVL(l_max_packet_id,0)
5231: and document_type = p_doc_type
5232: and document_header_id = l_doc_header_id
5232: and document_header_id = l_doc_header_id
5233: and document_distribution_id = l_doc_distribution_id
5234: and status_code in ( 'A','C') ;
5235:
5236: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_packets packet id '||l_max_packet_id_b);
5237: -- bug : 3717214
5238: -- Spool the existing multipliers.
5239: BEGIN
5240: IF NVL(l_max_packet_id,0) >= nvl(l_max_packet_id_b,0) THEN
5248: and document_distribution_id = l_doc_distribution_id
5249: and document_type = p_doc_type
5250: -- and parent_bc_packet_id is not NULL ;
5251: -- Bug 5514074 : For AP with qty/amount variance there will be multiple RAW records in
5252: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5253: -- we should pick burden lines against one of the raw line else it will result in burden
5254: -- duplication
5255: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5256: FROM pa_bc_commitments bc1
5267: select compiled_multiplier ,
5268: expenditure_type
5269: bulk collect into l_tab_multiplier,
5270: l_tab_icc_exp_type
5271: from pa_bc_packets
5272: where packet_id = l_max_packet_id_b
5273: and document_header_id = l_doc_header_id
5274: and document_distribution_id = l_doc_distribution_id
5275: and document_type = p_doc_type
5274: and document_distribution_id = l_doc_distribution_id
5275: and document_type = p_doc_type
5276: -- and parent_bc_packet_id is not NULL ;
5277: -- Bug 5514074 : For AP with qty/amount variance there will be multiple RAW records in
5278: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5279: -- we should pick burden lines against one of the raw line else it will result in burden
5280: -- duplication
5281: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5282: FROM pa_bc_packets bc1
5278: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5279: -- we should pick burden lines against one of the raw line else it will result in burden
5280: -- duplication
5281: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5282: FROM pa_bc_packets bc1
5283: WHERE bc1.packet_id = l_max_packet_id_b
5284: AND bc1.document_header_id = l_doc_header_id
5285: AND bc1.document_distribution_id = l_doc_distribution_id
5286: AND bc1.document_type = p_doc_type
5286: AND bc1.document_type = p_doc_type
5287: AND bc1.parent_bc_packet_id IS NULL
5288: AND ROWNUM = 1 );
5289:
5290: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_packets multiplier used');
5291:
5292: END IF ;
5293: EXCEPTION
5294: when no_data_found then
5326: ), 0),
5327: expenditure_type
5328: BULK COLLECT into l_tab_multiplier ,
5329: l_tab_icc_exp_type
5330: FROM pa_bc_packets pbc
5331: WHERE pbc.packet_id = p_packet_id
5332: AND pbc.bc_packet_id = p_bc_packet_id
5333: AND pbc.document_type = p_doc_type
5334: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
5352: pa_cost_base_exp_types cbet,
5353: pa_ind_rate_schedules_all_bg irs,
5354: pa_ind_compiled_sets ics,
5355: pa_compiled_multipliers cm,
5356: pa_bc_packets pbc
5357: WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
5358: AND cb.cost_base = cbet.cost_base
5359: AND cb.cost_base_type = cbet.cost_base_type
5360: AND et.expenditure_type = icc.expenditure_type
5386: IF p_burden_type = 'SAME' THEN
5387: If p_related_link = 'N' then
5388: IF l_tab_multiplier.count > 0 THEN
5389: forall indx in 1..l_tab_multiplier.count
5390: INSERT INTO pa_bc_packets
5391: ( ---- who columns------
5392: request_id,
5393: program_id,
5394: program_application_id,
5461: sysdate,
5462: l_update_login,
5463: ------ main columns-----------
5464: pbc.packet_id,
5465: pa_bc_packets_s.nextval,
5466: pbc.budget_version_id,
5467: pbc.project_id,
5468: pbc.task_id,
5469: pbc.expenditure_type,
5536: ,pbc.ext_bdgt_flag
5537: ,pbc.document_distribution_type
5538: ,pbc.document_header_id_2
5539: ,pbc.proj_encumbrance_type_id
5540: FROM pa_bc_packets pbc
5541: WHERE pbc.packet_id = p_packet_id
5542: AND pbc.bc_packet_id = p_bc_packet_id
5543: AND pbc.document_type = p_doc_type
5544: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F' ;
5567: End if;
5568:
5569: END IF ; -- l_tab_multiplier.count endif
5570: Elsif p_related_link = 'Y' then
5571: INSERT INTO pa_bc_packets
5572: ( ---- who columns------
5573: request_id,
5574: program_id,
5575: program_application_id,
5641: sysdate,
5642: l_update_login,
5643: ------ main columns-----------
5644: pbc.packet_id,
5645: pa_bc_packets_s.nextval,
5646: pbc.budget_version_id,
5647: pbc.project_id,
5648: pbc.task_id,
5649: pbc.expenditure_type, --- p_exp_type
5718: pbc.ext_bdgt_flag,
5719: pbc.document_distribution_type,
5720: pbc.document_header_id_2,
5721: pbc.proj_encumbrance_type_id
5722: FROM pa_bc_packets pbc
5723: WHERE pbc.packet_id = p_packet_id
5724: AND pbc.bc_packet_id = p_bc_packet_id
5725: AND pbc.document_type = p_doc_type
5726: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
5753: ELSIF p_burden_type = 'DIFFERENT' then
5754: If p_related_link = 'N' then
5755: IF l_tab_multiplier.COUNT > 0 THEN
5756: forall indx in 1..l_tab_multiplier.COUNT
5757: INSERT INTO pa_bc_packets
5758: ( ---- who columns------
5759: request_id,
5760: program_id,
5761: program_application_id,
5828: sysdate,
5829: l_update_login,
5830: ------ main columns-----------
5831: pbc.packet_id,
5832: pa_bc_packets_s.nextval,
5833: pbc.budget_version_id,
5834: pbc.project_id,
5835: pbc.task_id,
5836: l_tab_icc_exp_type(indx),
5900: ,ext_bdgt_flag
5901: ,pbc.document_distribution_type
5902: ,pbc.document_header_id_2
5903: ,pbc.proj_encumbrance_type_id
5904: FROM pa_bc_packets pbc
5905: WHERE pbc.packet_id = p_packet_id
5906: AND pbc.bc_packet_id = p_bc_packet_id
5907: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
5908:
5934:
5935:
5936: END IF ; -- endif for l_tab_multiplier.COUNT
5937: Elsif p_related_link = 'Y' then
5938: INSERT INTO pa_bc_packets
5939: ( ---- who columns------
5940: request_id,
5941: program_id,
5942: program_application_id,
6008: sysdate,
6009: l_update_login,
6010: ------ main columns-----------
6011: pbc.packet_id,
6012: pa_bc_packets_s.nextval,
6013: pbc.budget_version_id,
6014: pbc.project_id,
6015: pbc.task_id,
6016: /* Bug fix:3026988
6091: ext_bdgt_flag,
6092: pbc.document_distribution_type,
6093: pbc.document_header_id_2,
6094: pbc.proj_encumbrance_type_id
6095: FROM pa_bc_packets pbc
6096: WHERE pbc.packet_id = p_packet_id
6097: AND pbc.bc_packet_id = p_bc_packet_id
6098: AND pbc.document_type = p_doc_type
6099: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
6134:
6135: END create_ap_po_bdn_lines;
6136:
6137: ------------------------------------------------------------------------------------------------------------------
6138: --This Api insert new records into pa bc packets if the project type is burdened.If the PO is based on REQ, or
6139: --Invoice is based on PO then it takes the burden amount for the REQ or PO from pa_bc_commitments table
6140: --and ensures that for req or po the old burden amount is used when reversing lines are passed in gl_bc_packets
6141: ------------------------------------------------------------------------------------------------------------------
6142: PROCEDURE Populate_burden_cost
6172: l_po_header_id NUMBER;
6173: l_status_flag VARCHAR2(1) := 'N';
6174: l_commitment_rows_flag VARCHAR2(10) := 'N';
6175: l_pkt_rows_flag VARCHAR2(10) := 'N';
6176: l_doc_header_id PA_BC_PACKETS.document_header_id%type;
6177: l_doc_distribution_id PA_BC_PACKETS.document_header_id%type;
6178: l_task_id NUMBER;
6179: l_ei_date date;
6180: l_base VARCHAR2(100);
6173: l_status_flag VARCHAR2(1) := 'N';
6174: l_commitment_rows_flag VARCHAR2(10) := 'N';
6175: l_pkt_rows_flag VARCHAR2(10) := 'N';
6176: l_doc_header_id PA_BC_PACKETS.document_header_id%type;
6177: l_doc_distribution_id PA_BC_PACKETS.document_header_id%type;
6178: l_task_id NUMBER;
6179: l_ei_date date;
6180: l_base VARCHAR2(100);
6181: l_cp_structure VARCHAR2(100);
6221: pktburd.expenditure_type,
6222: pktburd.compiled_multiplier
6223: FROM pa_bc_commitments_all pktburd
6224: ,pa_bc_commitments_all pktraw
6225: ,pa_bc_packets pkttrx
6226: WHERE pktburd.document_distribution_id = l_req_id
6227: AND pktburd.document_header_id = l_po_header_id
6228: AND pktburd.document_type = l_comm_doc_type
6229: AND pktburd.parent_bc_packet_id is NOT NULL
6244: AND pkttrx.packet_id = p_packet_id
6245: AND pkttrx.bc_packet_id = l_bc_packet_id;
6246:
6247:
6248: -- this cursor picks up the burden amount and details from pa_bc_packets table
6249: -- for the given distribution id ,document type and document header id
6250: -- the transactions which are approved but not yet swept
6251: CURSOR po_amount(l_req_id NUMBER,
6252: l_bc_packet_id NUMBER,
6278: 'BCPKT',
6279: 'A')) accounted_cr,
6280: pktburd.expenditure_type,
6281: pktburd.compiled_multiplier
6282: FROM pa_bc_packets pktburd
6283: ,pa_bc_packets pktraw
6284: ,pa_bc_packets pkttrx
6285: WHERE pktburd.document_distribution_id = l_req_id
6286: AND pktburd.document_header_id = l_po_header_id
6279: 'A')) accounted_cr,
6280: pktburd.expenditure_type,
6281: pktburd.compiled_multiplier
6282: FROM pa_bc_packets pktburd
6283: ,pa_bc_packets pktraw
6284: ,pa_bc_packets pkttrx
6285: WHERE pktburd.document_distribution_id = l_req_id
6286: AND pktburd.document_header_id = l_po_header_id
6287: AND pktburd.document_type = l_pkt_doc_type
6280: pktburd.expenditure_type,
6281: pktburd.compiled_multiplier
6282: FROM pa_bc_packets pktburd
6283: ,pa_bc_packets pktraw
6284: ,pa_bc_packets pkttrx
6285: WHERE pktburd.document_distribution_id = l_req_id
6286: AND pktburd.document_header_id = l_po_header_id
6287: AND pktburd.document_type = l_pkt_doc_type
6288: AND pktburd.parent_bc_packet_id is NOT NULL
6290: AND pktburd.status_code in ('A','C')
6291: AND substr(nvl(pktburd.result_code,'P'),1,1) = 'P'
6292: AND (pktburd.packet_id,pktburd.parent_bc_packet_id) in
6293: ( SELECT MAX(pbc.packet_id),max(bc_packet_id)
6294: FROM pa_bc_packets pbc
6295: WHERE pbc.document_distribution_id = pktburd.document_distribution_id
6296: AND pbc.document_header_id = pktburd.document_header_id
6297: AND pbc.document_type = pktburd.document_type
6298: AND pbc.parent_bc_packet_id is NULL
6324: pbc.expenditure_type
6325: FROM pa_project_types ppt,
6326: pa_projects_all pp,
6327: pa_tasks ptk,
6328: pa_bc_packets pbc
6329: WHERE
6330: ppt.project_type = pp.project_type
6331: AND pp.project_id = pbc.project_id
6332: AND ptk.project_id = pbc.project_id
6430: IF l_burden_method = 'SAME' and l_bc_packet_id is NOT NULL then
6431:
6432: IF l_doc_type IN ( 'CC_C_PAY','CC_P_PAY','CC','CC_C_CO','CC_P_CO' ) THEN
6433: ----------------------------------------------------------------------------------
6434: --- insert into pa_bc_packets for the burden cost as a separate bc packet record
6435: --- after the funds check these records may be inserted into gl_bc_packets
6436: -----------------------------------------------------------------------------
6437: If l_parent_bc_packet_id = -1 then
6438: l_related_link := 'Y';
7092: END IF;
7093: IF p_calling_module in ('DISTBTC','TRXNIMPORT','DISTVIADJ','DISTERADJ','GL','TRXIMPORT','DISTCWKST') then
7094: -- update the bc packets set the parent bc packet id to null
7095: -- after derving the burden components for the raw lines
7096: UPDATE pa_bc_packets
7097: SET parent_bc_packet_id = null
7098: WHERE packet_id = p_packet_id
7099: AND parent_bc_packet_id = -1;
7100: END IF;
7180: -- ========================================
7181: PROCEDURE setup_start_end_date (
7182: p_packet_id IN NUMBER,
7183: p_bc_packet_id IN NUMBER,
7184: p_project_id IN pa_bc_packets.project_id%TYPE,
7185: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7186: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7187: p_expenditure_item_date IN DATE,
7188: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7181: PROCEDURE setup_start_end_date (
7182: p_packet_id IN NUMBER,
7183: p_bc_packet_id IN NUMBER,
7184: p_project_id IN pa_bc_packets.project_id%TYPE,
7185: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7186: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7187: p_expenditure_item_date IN DATE,
7188: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7189: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7186: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7187: p_expenditure_item_date IN DATE,
7188: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7189: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7190: p_set_of_books_id IN pa_bc_packets.set_of_books_id%TYPE,
7191: x_start_date OUT NOCOPY DATE,
7192: x_end_date OUT NOCOPY DATE,
7193: x_error_code OUT NOCOPY NUMBER,
7194: x_err_buff OUT NOCOPY VARCHAR2,
7701:
7702: FUNCTION get_start_or_end_date(
7703: p_packet_id IN NUMBER,
7704: p_bc_packet_id IN NUMBER,
7705: p_project_id IN pa_bc_packets.project_id%TYPE,
7706: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7707: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7708: p_expenditure_item_date IN DATE,
7709: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7702: FUNCTION get_start_or_end_date(
7703: p_packet_id IN NUMBER,
7704: p_bc_packet_id IN NUMBER,
7705: p_project_id IN pa_bc_packets.project_id%TYPE,
7706: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7707: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7708: p_expenditure_item_date IN DATE,
7709: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7710: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7707: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7708: p_expenditure_item_date IN DATE,
7709: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7710: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7711: p_set_of_books_id IN pa_bc_packets.set_of_books_id%TYPE,
7712: p_type IN varchar2 -- START_DATE or END_DATE OR RESULT_CODE
7713: ) return DATE is
7714:
7715: l_start_date date;
7841: END LOOP;
7842: End if;
7843:
7844:
7845: UPDATE pa_bc_packets a
7846: SET status_code = DECODE(p_bc_mode,'C','F','R'),
7847: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
7848: WHERE status_code in ('P','I','A','S')
7849: AND source_event_id IN
7850: (SELECT event_id
7851: FROM PSA_BC_XLA_EVENTS_GT
7852: WHERE upper(result_code) in ('XLA_ERROR','FATAL','XLA_UNPROCESSED','XLA_NO_JOURNAL'));
7853:
7854: UPDATE pa_bc_packets a
7855: SET status_code = DECODE(p_bc_mode,'C','F','R'),
7856: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
7857: WHERE status_code in ('P','I','A','S')
7858: AND bc_event_id IN
7950:
7951: l_cdl_top_task_id pa_tasks.top_task_id%TYPE;
7952: l_cdl_budget_version_id pa_budget_versions.budget_version_id%TYPE;
7953: l_cdl_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
7954: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
7955: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
7956: l_error_message_code VARCHAR2(200) := NULL;
7957:
7958: -- Bug 5680236 : End of variable declaration
7951: l_cdl_top_task_id pa_tasks.top_task_id%TYPE;
7952: l_cdl_budget_version_id pa_budget_versions.budget_version_id%TYPE;
7953: l_cdl_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
7954: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
7955: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
7956: l_error_message_code VARCHAR2(200) := NULL;
7957:
7958: -- Bug 5680236 : End of variable declaration
7959:
8119: End if;
8120:
8121: -----------------------------------------------------------------------------------+
8122: -- Invoke the Sweeper process to sweep all the encumbrance etries
8123: -- from PA_BC_PACKETS to PA_BC_BALANCES and PA_BC_COMMITMENTS
8124: -----------------------------------------------------------------------------------+
8125: PA_Sweeper.Update_Act_Enc_Balance (
8126: X_Return_Status => l_Return_Status,
8127: X_Error_Message_Code => l_Msg_Data
8606: p_mode in varchar2,
8607: p_dr_cr in varchar2)
8608: return number
8609: is
8610: l_document_header_id pa_bc_packets.document_header_id%type;
8611: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8612: l_document_type pa_bc_packets.document_type%type;
8613: l_ratio pa_bc_packets.accounted_dr%type;
8614: Begin
8607: p_dr_cr in varchar2)
8608: return number
8609: is
8610: l_document_header_id pa_bc_packets.document_header_id%type;
8611: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8612: l_document_type pa_bc_packets.document_type%type;
8613: l_ratio pa_bc_packets.accounted_dr%type;
8614: Begin
8615:
8608: return number
8609: is
8610: l_document_header_id pa_bc_packets.document_header_id%type;
8611: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8612: l_document_type pa_bc_packets.document_type%type;
8613: l_ratio pa_bc_packets.accounted_dr%type;
8614: Begin
8615:
8616: If (p_document_header_id <> nvl(l_document_header_id,-1) and
8609: is
8610: l_document_header_id pa_bc_packets.document_header_id%type;
8611: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8612: l_document_type pa_bc_packets.document_type%type;
8613: l_ratio pa_bc_packets.accounted_dr%type;
8614: Begin
8615:
8616: If (p_document_header_id <> nvl(l_document_header_id,-1) and
8617: p_document_distribution_id <> nvl(l_document_distribution_id,-1) and
8631: decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
8632: 'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))
8633: ,0))
8634: INTO l_ratio
8635: FROM pa_bc_packets pbc1
8636: WHERE pbc1.packet_id = ( SELECT max(pbc.packet_id)
8637: FROM pa_bc_packets pbc
8638: WHERE pbc.document_distribution_id = l_document_distribution_id
8639: AND pbc.document_header_id = l_document_header_id
8633: ,0))
8634: INTO l_ratio
8635: FROM pa_bc_packets pbc1
8636: WHERE pbc1.packet_id = ( SELECT max(pbc.packet_id)
8637: FROM pa_bc_packets pbc
8638: WHERE pbc.document_distribution_id = l_document_distribution_id
8639: AND pbc.document_header_id = l_document_header_id
8640: AND pbc.document_type = l_document_type
8641: AND pbc.parent_bc_packet_id is NULL
8861:
8862: --Get the accounting currency into a global variable.
8863: g_acct_currency_code := pa_multi_currency.g_accounting_currency_code;
8864:
8865: --insert these records into pa bc packets
8866: pa_funds_control_pkg.log_message
8867: (p_msg_token1 => 'calling create pkt lines api');
8868:
8869: create_CBC_pkt_lines
8885:
8886: --Get the accounting currency into a global variable.
8887: g_acct_currency_code := pa_multi_currency.g_accounting_currency_code;
8888:
8889: --insert these records into pa bc packets
8890: pa_funds_control_pkg.log_message
8891: (p_msg_token1 => 'calling create pkt lines api');
8892:
8893: create_CBC_pkt_lines
8951: NULL distribution_type,
8952: NULL po_release_Id,
8953: PA_FUNDS_CONTROL_UTILS.get_encum_type_id(igc.project_id,'STD') enc_type_id,
8954: igchead.vendor_id,
8955: pa_bc_packets_s.nextval
8956: FROM pa_tasks pkt,
8957: pa_projects_all pp,
8958: igc_cc_acct_lines igc,
8959: igc_cc_det_pf igcpf,
9034: 'Provisional','CC_P_CO')
9035: pkt_reference1,
9036: igci.cc_header_id pkt_reference2,
9037: igci.cc_acct_line_id pkt_reference3,
9038: pa_bc_packets_s.nextval
9039: FROM
9040: pa_tasks pkt,
9041: pa_projects_all pp,
9042: igc_cc_interface igci,