[Home] [Help]
84: -- Bug 4080732 END
85:
86: /**
87: * Private Procedure: MassUpdate_Releases
88: * Modifies: Column price_override, retroactive_date in po_line_locations,
89: * Authorization_status, revision_num in po_headers and po_releases.
90: * Effects: Selects the agreements( blankets and contracts) as specified
91: * by the concurrent parameters and selects the execution docs
92: * refering these agreements for retroactive price updates.
90: * Effects: Selects the agreements( blankets and contracts) as specified
91: * by the concurrent parameters and selects the execution docs
92: * refering these agreements for retroactive price updates.
93: * Get the new price based on the release/Std PO shipment values.
94: * If they are different, then update po_line_locations with the
95: * new price. In either case, update retoractive_date in
96: * po_line_locations with the retroactive_date in po_lines so that
97: * this shipment will not be picked up again unless blanket line
98: * is retroactively changed. Once all the releases are done, update
92: * refering these agreements for retroactive price updates.
93: * Get the new price based on the release/Std PO shipment values.
94: * If they are different, then update po_line_locations with the
95: * new price. In either case, update retoractive_date in
96: * po_line_locations with the retroactive_date in po_lines so that
97: * this shipment will not be picked up again unless blanket line
98: * is retroactively changed. Once all the releases are done, update
99: * po_headers or po_releases with the new revision number and set
100: * authorization_status to "Requires Reapproval" and initiate
109: --Name: MassUpdate_Releases
110: --Pre-reqs:
111: -- None.
112: --Modifies:
113: -- Column price_override, retroactive_date in po_line_locations,
114: -- Authorization_status, revision_num in po_headers and po_releases.
115: --Locks:
116: -- None.
117: --Function:
122: -- are retroactively changed.
123: -- Selects the blanket lines that have been retroactively changed
124: -- and selects all the releases againt BA (or std PO against GA).
125: -- Get the new price based on the release/Std PO shipment values.
126: -- If they are different, then update po_line_locations with the
127: -- new price. In either case, update retoractive_date in
128: -- po_line_locations with the retroactive_date in po_lines so that
129: -- this shipment will not be picked up again unless blanket line
130: -- is retroactively changed. Once all the releases are done, update
124: -- and selects all the releases againt BA (or std PO against GA).
125: -- Get the new price based on the release/Std PO shipment values.
126: -- If they are different, then update po_line_locations with the
127: -- new price. In either case, update retoractive_date in
128: -- po_line_locations with the retroactive_date in po_lines so that
129: -- this shipment will not be picked up again unless blanket line
130: -- is retroactively changed. Once all the releases are done, update
131: -- po_headers or po_releases with the new revision number and set
132: -- authorization_status to "Requires Reapproval" and initiate
187: poll.price_override, nvl(poll.need_by_date,sysdate),
188: por.po_release_id,
189: por.authorization_status, por.revision_num,
190: pora.revision_num
191: from po_line_locations poll,
192: po_releases_all por, --
193: po_releases_archive pora
194: where nvl(por.frozen_flag, 'N') = 'N'
195: and nvl(por.authorization_status, 'INCOMPLETE') IN
246: poll.price_override, nvl(poll.need_by_date,sysdate),
247: poh.po_header_id,
248: poh.authorization_status, poh.revision_num,
249: poha.revision_num
250: from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
251: po_headers_archive_all poha,financials_system_params_all fsp
252: where pol.from_line_id = l_po_line_id
253: and poh.po_header_id = pol.po_header_id
254: and poh.org_id = fsp.org_id --
257: --to rule out unneccesary spo linelocations
258: and ( p_qp_license_on = 'Y' OR
259: ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
260: and poll.shipment_num = (select min(poll.shipment_num)
261: from po_line_locations_all polt
262: where polt.po_line_id=pol.po_line_id)
263: and nvl(poh.frozen_flag, 'N') = 'N'
264: and nvl(poh.authorization_status, 'INCOMPLETE') IN
265: ('APPROVED', 'INCOMPLETE', 'REJECTED',
273: and nvl(poh.cancel_flag,'N') <> 'Y'
274: and nvl(poh.consigned_consumption_flag,'N') ='N'
275: and pol.po_line_id = poll.po_line_id
276: and not exists (Select 'billed received shipments'
277: from po_line_locations_all poll1
278: where poll1.po_line_id = pol.po_line_id
279: and ((poll1.accrue_on_receipt_flag = 'Y' and
280: poll1.quantity_received <> 0)
281: or
312: poll.price_override, nvl(poll.need_by_date,sysdate),
313: por.po_release_id,
314: por.authorization_status, por.revision_num,
315: pora.revision_num
316: from po_line_locations poll,
317: po_releases_all por, --
318: po_releases_archive pora
319: where nvl(por.frozen_flag, 'N') = 'N'
320: and nvl(por.authorization_status, 'INCOMPLETE') IN
355: poll.price_override, nvl(poll.need_by_date,sysdate),
356: poh.po_header_id,
357: poh.authorization_status, poh.revision_num,
358: poha.revision_num
359: from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
360: po_headers_archive_all poha, po_document_types_all_b pdt,
361: financials_system_params_all fsp
362: where pol.from_line_id = l_po_line_id
363: and poh.po_header_id = pol.po_header_id
367: and pdt.document_type_code = 'PO' -- Bug 3573266
368: and pdt.document_subtype = 'STANDARD' -- Bug 3573266
369: and (nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE' -- Bug 3573266
370: or (not exists (Select 'billed received shipments' -- Bug 3565522
371: from po_line_locations_all poll1
372: where poll1.po_line_id = pol.po_line_id
373: and ((poll1.accrue_on_receipt_flag = 'Y' and
374: poll1.quantity_received <> 0)
375: or
378: --to rule out unneccesary spo linelocations
379: and ( p_qp_license_on = 'Y' OR
380: ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
381: and poll.shipment_num = (select min(poll.shipment_num)
382: from po_line_locations_all polt
383: where polt.po_line_id=pol.po_line_id)
384: and nvl(poh.frozen_flag, 'N') = 'N'
385: and nvl(poh.authorization_status, 'INCOMPLETE') IN
386: ('APPROVED', 'INCOMPLETE', 'REJECTED',
429: poh.revision_num,
430: poha.revision_num
431: FROM po_headers_all poh,
432: po_lines_all pol,
433: po_line_locations_all poll,
434: po_headers_archive_all poha,
435: financials_system_params_all fsp
436: WHERE pol.Contract_id = l_po_header_id
437: AND pol.from_header_id IS NULL
441: AND poll.shipment_num =
442: (
443: SELECT
444: min(poll.shipment_num)
445: FROM po_line_locations_all polt
446: WHERE polt.po_line_id=pol.po_line_id
447: )
448: AND nvl(poh.frozen_flag, 'N') = 'N'
449: AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
454: AND not exists
455: (
456: SELECT
457: 'billed received shipments'
458: FROM po_line_locations_all poll1
459: WHERE poll1.po_line_id = pol.po_line_id
460: AND
461: (
462: (
493: poh.revision_num,
494: poha.revision_num
495: FROM po_headers_all poh,
496: po_lines_all pol,
497: po_line_locations_all poll,
498: po_headers_archive_all poha,
499: po_document_types_all_b pdt,
500: financials_system_params_all fsp
501: WHERE pol.Contract_id = l_po_header_id
514: not exists
515: (
516: SELECT
517: 'billed received shipments'
518: FROM po_line_locations_all poll1
519: WHERE poll1.po_line_id = pol.po_line_id
520: AND
521: (
522: (
531: AND poll.shipment_num =
532: (
533: SELECT
534: min(poll.shipment_num)
535: FROM po_line_locations_all polt
536: WHERE polt.po_line_id=pol.po_line_id
537: )
538: AND nvl(poh.frozen_flag, 'N') = 'N'
539: AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
808: 'and pol.category_id = mca.category_id (+) ' ||
809: ' and (((nvl(poh.global_agreement_flag,''N'') = ''N'') ';
810: l_sql_str7 := ' and exists ' ||
811: ' (select ''has releases'' from ' ||
812: ' po_line_locations poll where '||
813: ' poll.po_line_id = pol.po_line_id '||
814: ' and poll.po_release_id is not null)) ';
815: l_sql_str8 := ' OR ' ||
816: '((nvl(poh.global_agreement_flag,''N'') = ''Y'') '||
1143: -- Bulk Update
1144: l_module := g_log_head||l_api_name||'.'||
1145: '060'||'.';
1146: /* Bug 2718565.
1147: * We need to update po_line_locations_all with
1148: * time stamp since we process the Std POs
1149: * against GA in other operating units.
1150: */
1151: FORALL processed_index in
1149: * against GA in other operating units.
1150: */
1151: FORALL processed_index in
1152: 1..l_po_line_loc_table.COUNT
1153: UPDATE po_line_locations_all
1154: SET retroactive_date= l_retroactive_date_table(i),
1155: last_update_date = g_sysdate,
1156: last_updated_by = g_user_id
1157: WHERE line_location_id =
1387: */
1388: -- Bulk Update
1389: FORALL processed_index in
1390: 1..l_po_line_loc_table.COUNT
1391: UPDATE po_line_locations
1392: SET retroactive_date= l_retroactive_date_table(i),
1393: last_update_date = g_sysdate,
1394: last_updated_by = g_user_id
1395: WHERE line_location_id =
1399: -- Remove the retroactive date for all the rows that
1400: -- were excluded for the invalid adj account
1401: FORALL exclude_index in
1402: 1..g_exclude_row_id_table.COUNT
1403: UPDATE po_line_locations
1404: SET retroactive_date = null,
1405: last_update_date = g_sysdate,
1406: last_updated_by = g_user_id
1407: WHERE rowid = g_exclude_row_id_table(exclude_index);
1573: '088'||'.';
1574:
1575: FORALL processed_index in
1576: 1..l_po_line_loc_table.COUNT
1577: UPDATE po_line_locations_all
1578: SET retroactive_date= l_retroactive_date_table(i),
1579: last_update_date = g_sysdate,
1580: last_updated_by = g_user_id
1581: WHERE line_location_id =
1652:
1653: l_module := g_log_head||l_api_name||'.'||'110'||'.';
1654: if l_temp_row_id_table.COUNT <> 0 then
1655: FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1656: UPDATE po_line_locations_all
1657: SET price_override =
1658: l_temp_new_price_table(price_update_index),
1659: calculate_tax_flag = 'Y',
1660: manual_price_change_flag = 'N', --
1697: --
1698: WHERE rowid = l_temp_row_id_table(price_update_index);
1699:
1700: FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1701: UPDATE po_line_locations_all poll
1702: SET poll.price_override =
1703: l_temp_new_price_table(price_update_index),
1704: poll.calculate_tax_flag = 'Y',
1705: poll.last_update_date = g_sysdate,
2037:
2038:
2039: /* Bug 2714259.
2040: * Update approved_flag to 'R', last_update_date and
2041: * last_updated_by columns in po_line_locations for which
2042: * the price has been updated .
2043: */
2044: l_module := g_log_head||l_api_name||'.'||'020'||'.';
2045: SELECT row_id
2050: and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2051:
2052: l_module := g_log_head||l_api_name||'.'||'030'||'.';
2053: FORALL release_update_index in 1..l_row_id_table.COUNT
2054: UPDATE po_line_locations poll
2055: SET poll.approved_flag = 'R',
2056: poll.last_update_date = g_sysdate,
2057: poll.last_updated_by = g_user_id
2058: WHERE rowid = l_row_id_table(release_update_index);
2183: WHERE po_header_id = g_po_header_id_table(doc_update_index);
2184:
2185: /* Bug 2714259.
2186: * Update approved_flag to 'R', last_update_date and
2187: * last_updated_by columns in po_line_locations for which
2188: * the price has been updated .
2189: */
2190: l_module := g_log_head||l_api_name||'.'||'020'||'.';
2191: SELECT row_id
2196: and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2197:
2198: l_module := g_log_head||l_api_name||'.'||'030'||'.';
2199: FORALL ship_update_index in 1..l_row_id_table.COUNT
2200: UPDATE po_line_locations_all poll
2201: SET poll.approved_flag = 'R',
2202: poll.last_update_date = g_sysdate,
2203: poll.last_updated_by = g_user_id
2204: WHERE poll.po_line_id =
2231: /**
2232: * Private Procedure: Process_Price_Change
2233: * Modifies: updates the global variables with the release_id, revision_num
2234: * from the archive table, authorization_status, row_id of the
2235: * po_line_locations to be updated and the new price if it is different
2236: * from the old price.
2237: * Effects: Get the new price for the release shipment attributes and if
2238: * different update the global variables.This is called from
2239: * massupdate_releases procedure.
2268: l_enhanced_pricing_flag VARCHAR2(1); --Enhanced Pricing
2269:
2270: --
2271: l_quantity PO_LINES.quantity%TYPE;
2272: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2273: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2274: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2275: l_from_line_id PO_LINES.from_line_id%TYPE;
2276: l_org_id po_lines.org_id%TYPE;
2269:
2270: --
2271: l_quantity PO_LINES.quantity%TYPE;
2272: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2273: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2274: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2275: l_from_line_id PO_LINES.from_line_id%TYPE;
2276: l_org_id po_lines.org_id%TYPE;
2277: l_contract_id po_lines.contract_id%TYPE;
2270: --
2271: l_quantity PO_LINES.quantity%TYPE;
2272: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2273: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2274: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2275: l_from_line_id PO_LINES.from_line_id%TYPE;
2276: l_org_id po_lines.org_id%TYPE;
2277: l_contract_id po_lines.contract_id%TYPE;
2278: l_order_header_id po_lines.po_header_id%TYPE;
2314: IF p_global_agreement_flag = 'Y' THEN
2315:
2316: select po_line_id
2317: into l_po_line_id
2318: from po_line_locations_all
2319: where line_location_id = p_po_line_location_id ;
2320:
2321: l_std_po_price_change := 'Y';
2322:
2328:
2329: l_std_po_price_change := 'Y';
2330: select po_line_id
2331: into l_po_line_id
2332: from po_line_locations_all
2333: where line_location_id = p_po_line_location_id ;
2334:
2335:
2336: END IF;
2458: l_vendor_site_id,
2459: l_uom,
2460: l_in_unit_price,
2461: l_currency_code
2462: FROM po_line_locations_all PLL, po_lines_all POL,
2463: po_headers_all POH
2464: WHERE PLL.line_location_id = p_po_line_location_id
2465: AND POL.po_line_id = PLL.po_line_id
2466: AND POH.po_header_id = POL.po_header_id;
3290: SELECT 'Y'
3291: INTO l_retro_change
3292: FROM dual
3293: WHERE EXISTS (SELECT 'retroactive pricing changes'
3294: FROM po_line_locations poll,
3295: po_line_locations_archive polla
3296: WHERE poll.po_header_id = p_document_id
3297: AND poll.po_header_id =polla.po_header_id
3298: AND poll.line_location_id = polla.line_location_id
3291: INTO l_retro_change
3292: FROM dual
3293: WHERE EXISTS (SELECT 'retroactive pricing changes'
3294: FROM po_line_locations poll,
3295: po_line_locations_archive polla
3296: WHERE poll.po_header_id = p_document_id
3297: AND poll.po_header_id =polla.po_header_id
3298: AND poll.line_location_id = polla.line_location_id
3299: AND polla.latest_external_flag = 'Y'
3305: SELECT 'Y'
3306: INTO l_retro_change
3307: FROM dual
3308: WHERE EXISTS (SELECT 'retroactive pricing changes'
3309: FROM po_line_locations poll,
3310: po_line_locations_archive polla
3311: WHERE poll.po_release_id = p_document_id
3312: AND poll.po_header_id =polla.po_header_id
3313: AND poll.line_location_id = polla.line_location_id
3306: INTO l_retro_change
3307: FROM dual
3308: WHERE EXISTS (SELECT 'retroactive pricing changes'
3309: FROM po_line_locations poll,
3310: po_line_locations_archive polla
3311: WHERE poll.po_release_id = p_document_id
3312: AND poll.po_header_id =polla.po_header_id
3313: AND poll.line_location_id = polla.line_location_id
3314: AND polla.latest_external_flag = 'Y'
3399: PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Reset retroactive_date for Release');
3400: END IF; /* IF g_debug_stmt */
3401:
3402: -- SQL What: Find out any retroactive pricing change for this Release
3403: -- SQL Why : For Release, column po_line_locations.retroactive_date will
3404: -- be updated if any retroactive pricing changes, Reset it
3405: -- to the corresponding blanket line retroactive_date after
3406: -- processing retroactive pricing.
3407: UPDATE po_line_locations_all pll
3403: -- SQL Why : For Release, column po_line_locations.retroactive_date will
3404: -- be updated if any retroactive pricing changes, Reset it
3405: -- to the corresponding blanket line retroactive_date after
3406: -- processing retroactive pricing.
3407: UPDATE po_line_locations_all pll
3408: SET retroactive_date = (SELECT pl.retroactive_date
3409: FROM po_lines_all pl
3410: WHERE pl.po_line_id = pll.po_line_id),
3411: last_update_date = SYSDATE,
3501: poll.line_location_id,
3502: poll.quantity_billed,
3503: poll.price_override new_price,
3504: polla.price_override old_price
3505: FROM po_line_locations poll,
3506: po_line_locations_archive polla
3507: WHERE poll.po_header_id = p_po_header_id
3508: AND poll.po_release_id IS NULL
3509: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3502: poll.quantity_billed,
3503: poll.price_override new_price,
3504: polla.price_override old_price
3505: FROM po_line_locations poll,
3506: po_line_locations_archive polla
3507: WHERE poll.po_header_id = p_po_header_id
3508: AND poll.po_release_id IS NULL
3509: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3510: (poll.quantity_received > 0 OR
3526: poll.line_location_id,
3527: poll.quantity_billed,
3528: poll.price_override new_price,
3529: polla.price_override old_price
3530: FROM po_line_locations poll,
3531: po_line_locations_archive polla
3532: WHERE poll.po_release_id = p_po_release_id
3533: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3534: (poll.quantity_received > 0 OR
3527: poll.quantity_billed,
3528: poll.price_override new_price,
3529: polla.price_override old_price
3530: FROM po_line_locations poll,
3531: po_line_locations_archive polla
3532: WHERE poll.po_release_id = p_po_release_id
3533: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3534: (poll.quantity_received > 0 OR
3535: poll.quantity_billed > 0)) OR
3588: FROM po_headers poh,
3589: po_lines_all pol, --
3590: -- Bug 3393219, Consumption transaction owning org
3591: -- financials_system_parameters fsp,
3592: po_line_locations_all poll, --
3594: po_distributions_all pod, --
3595: -- Bug 3314204, 3303148
3596: po_distributions_archive_all poda --
3589: po_lines_all pol, --
3590: -- Bug 3393219, Consumption transaction owning org
3591: -- financials_system_parameters fsp,
3592: po_line_locations_all poll, --
3593: po_line_locations_archive_all polla, --
3595: -- Bug 3314204, 3303148
3596: po_distributions_archive_all poda --
3597: WHERE pol.po_header_id = p_po_header_id
3656: po_headers_all poh, --
3657: po_lines pol,
3658: -- Bug 3393219, Consumption transaction owning org
3659: -- financials_system_parameters fsp,
3660: po_line_locations_all poll, --
3662: po_distributions_all pod, --
3663: -- Bug 3314204, 3303148
3664: po_distributions_archive poda
3657: po_lines pol,
3658: -- Bug 3393219, Consumption transaction owning org
3659: -- financials_system_parameters fsp,
3660: po_line_locations_all poll, --
3661: po_line_locations_archive_all polla, --
3663: -- Bug 3314204, 3303148
3664: po_distributions_archive poda
3665: WHERE por.po_release_id = p_po_release_id
4457: SELECT 'N'
4458: INTO l_retro_proj_allowed
4459: FROM DUAL
4460: WHERE EXISTS (SELECT 'has project information'
4461: FROM PO_LINE_LOCATIONS_ALL pll,
4462: PO_DISTRIBUTIONS_ALL pod
4463: WHERE pll.po_line_id = p_po_line_id
4464: AND pod.line_location_id = pll.line_location_id
4465: AND ((NVL(pll.quantity_received,0) > 0 AND
4478: SELECT 'N'
4479: INTO l_retro_proj_allowed
4480: FROM DUAL
4481: WHERE EXISTS (SELECT 'has project information'
4482: FROM PO_LINE_LOCATIONS_ALL pll,
4483: PO_DISTRIBUTIONS_ALL pod
4484: WHERE pll.line_location_id = p_po_line_loc_id
4485: AND pod.line_location_id = pll.line_location_id
4486: AND ((NVL(pll.quantity_received,0) > 0 AND
4543: IS
4544:
4545: l_account_valid varchar2(1) := 'Y';
4546: l_retroprice_adj_account_id number;
4547: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4548: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4549: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4550: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4551: -- Bug 3541961
4544:
4545: l_account_valid varchar2(1) := 'Y';
4546: l_retroprice_adj_account_id number;
4547: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4548: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4549: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4550: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4551: -- Bug 3541961
4552: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4545: l_account_valid varchar2(1) := 'Y';
4546: l_retroprice_adj_account_id number;
4547: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4548: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4549: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4550: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4551: -- Bug 3541961
4552: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4553: l_org_id PO_HEADERS.org_id%TYPE;
4546: l_retroprice_adj_account_id number;
4547: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4548: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4549: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4550: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4551: -- Bug 3541961
4552: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4553: l_org_id PO_HEADERS.org_id%TYPE;
4554: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4550: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4551: -- Bug 3541961
4552: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4553: l_org_id PO_HEADERS.org_id%TYPE;
4554: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4555: l_logical_inv_org_id number;
4556:
4557: l_module varchar2(100);
4558: l_api_name CONSTANT VARCHAR2(50) := 'Is_Adjustment_Account_Valid';
4565: NVL(poh.consigned_consumption_flag,'N'),
4566: pll.ship_to_organization_id,
4567: poh.org_id,
4568: pll.transaction_flow_header_id -- Bug 3880758
4569: from po_line_locations_all pll,
4570: -- Bug 3541961
4571: po_headers_all poh
4572: where pll.po_line_id = p_po_line_id
4573: -- Bug 3541961
4580: NVL(por.consigned_consumption_flag,'N'),
4581: pll.ship_to_organization_id,
4582: por.org_id,
4583: null -- Bug 3880758
4584: from po_line_locations_all pll,
4585: -- Bug 3541961
4586: po_releases_all por
4587: where pll.line_location_id = p_po_line_loc_id
4588: -- Bug 3541961
4795: IS
4796:
4797: l_is_inv_org_period_open VARCHAR2(1) := 'Y';
4798: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4799: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4800: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4801: l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
4802: l_inv_org_id_period_check HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
4803:
4796:
4797: l_is_inv_org_period_open VARCHAR2(1) := 'Y';
4798: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4799: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4800: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4801: l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
4802: l_inv_org_id_period_check HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
4803:
4804: l_module VARCHAR2(100);
4808: CURSOR STD_PO_SHIPMENTS_CSR IS
4809: SELECT NVL(poh.consigned_consumption_flag, 'N'),
4810: pll.ship_to_organization_id,
4811: pll.transaction_flow_header_id
4812: FROM po_line_locations_all pll,
4813: po_headers_all poh
4814: WHERE pll.po_line_id = p_po_line_id
4815: AND pll.po_header_id = poh.po_header_id;
4816:
4817: CURSOR REL_SHIPMENTS_CSR IS
4818: SELECT NVL(por.consigned_consumption_flag, 'N'),
4819: pll.ship_to_organization_id,
4820: NULL
4821: FROM po_line_locations_all pll,
4822: po_releases_all por
4823: WHERE pll.line_location_id = p_po_line_loc_id
4824: AND pll.po_release_id = por.po_release_id;
4825: