[Home] [Help]
105: CURSOR transferred_gbpas_csr(l_start_rowid rowid, l_end_rowid rowid) IS
106: SELECT POH.po_header_id
107: , POH.agent_id
108: , POH.org_id
109: FROM PO_HEADERS_ALL POH
110: WHERE POH.created_by = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER
111: AND POH.authorization_status = 'IN PROCESS'
112: AND POH.rowid between l_start_rowid and l_end_rowid; --Bug#5156673
113:
195: -- SQL Why : Bulk update the status to APPROVED for the new GBPA's created
196: -- by the migration program.
197: -- SQL Join: po_header_id
198: FORALL i IN 1.. l_po_header_ids.COUNT
199: UPDATE PO_HEADERS_ALL GBPA
200: SET
201: -- Set it to APPROVED if there is no CPA_REFERENCE,
202: -- Else, if the status on the CPA is APPROVED, then
203: -- set it to APPROVED. If the status on CPA is anything
212: (CPA.authorization_status,
213: 'APPROVED', 'APPROVED',
214: -- else
215: 'INCOMPLETE')
216: FROM PO_HEADERS_ALL CPA
217: WHERE CPA.po_header_id = GBPA.cpa_reference)),
218: GBPA.approved_flag = 'Y',
219: GBPA.approved_date = sysdate,
220: GBPA.last_update_date = sysdate,
224: WHERE po_header_id = l_po_header_ids(i);
225:
226: x_rows_processed := SQL%rowcount; --Bug#5156673 --TBD: What is the impact due to the loop ; mutiple loops doing multiple updates; x_rows_processed will not be the total rows proecssed by this worked then ?
227:
228: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Number of rows of PO_HEADERS_ALL updated='||SQL%rowcount); END IF;
229:
230: l_progress := '110';
231: -- SQL What: Bulk update the PO Number on the Archive table (there would
232: -- be only 1 revision = 0, for the newly created GBPA's)
801: xml_send_date,
802: 'Y', -- ever_approved_flag, -- Not present in txn table
803: 'Y', -- latest_external_flag, -- Not present in txn table
804: NULL -- standard_comment_code -- Not present in txn table
805: FROM PO_HEADERS_ALL
806: WHERE po_header_id = p_po_header_ids(i)
807: AND NOT EXISTS
808: (SELECT 'Archive record for Header already exists'
809: FROM PO_HEADERS_ARCHIVE_ALL POHA2
2354: l_api_version CONSTANT NUMBER := 1.0;
2355: l_module CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2356: l_progress VARCHAR2(3) := '000';
2357:
2358: l_new_po_number PO_HEADERS_ALL.segment1%TYPE;
2359: BEGIN
2360: l_progress := '010';
2361: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'START'); END IF;
2362:
2418: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2419: l_progress VARCHAR2(3) := '000';
2420:
2421: l_long_text LONG := NULL;
2422: l_gbpa_number PO_HEADERS_ALL.segment1%TYPE;
2423: l_ou_name HR_ALL_ORGANIZATION_UNITS_TL.name%TYPE;
2424: l_gbpa_info LONG;
2425:
2426: -- Bug 4941073:
2455: SELECT POH.segment1,
2456: HROUTL.name
2457: INTO l_gbpa_number,
2458: l_ou_name
2459: FROM PO_HEADERS_ALL POH,
2460: HR_ALL_ORGANIZATION_UNITS_TL HROUTL
2461: WHERE POH.po_header_id = p_gbpa_header_id_list(i)
2462: AND HROUTL.organization_id = POH.org_id
2463: AND HROUTL.language = userenv('LANG');
2683: --Name: attach_gbpa_numbers_in_cpa
2684: --Pre-reqs:
2685: -- None
2686: --Modifies:
2687: -- a) PO_HEADERS_ALL: updated CPA_REFERENCE column
2688: -- b) FND_MSG_PUB on unhandled exceptions.
2689: --Locks:
2690: -- None.
2691: --Function:
2714: CURSOR cpa_references_csr IS
2715: SELECT GBPA.cpa_reference,
2716: CPA.org_id cpa_org_id,
2717: GBPA.po_header_id
2718: FROM PO_HEADERS_ALL GBPA,
2719: PO_HEADERS_ALL CPA
2720: WHERE GBPA.type_lookup_code = 'BLANKET'
2721: AND GBPA.created_by = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER
2722: AND GBPA.authorization_status = 'IN PROCESS'
2715: SELECT GBPA.cpa_reference,
2716: CPA.org_id cpa_org_id,
2717: GBPA.po_header_id
2718: FROM PO_HEADERS_ALL GBPA,
2719: PO_HEADERS_ALL CPA
2720: WHERE GBPA.type_lookup_code = 'BLANKET'
2721: AND GBPA.created_by = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER
2722: AND GBPA.authorization_status = 'IN PROCESS'
2723: AND GBPA.cpa_reference IS NOT NULL