[Home] [Help]
145: OPEN_FLAG
146: INTO
147: l_open_flag
148: FROM
149: org_acct_periods oop
150: ,org_organization_definitions ood
151: WHERE oop.acct_period_id = p_acct_period_id
152: AND oop.organization_id = ood.organization_id
153: AND ood.organization_name = p_org;
157: OPEN_FLAG
158: INTO
159: l_open_flag
160: FROM
161: org_acct_periods oop
162: ,HR_ORGANIZATION_UNITS HOU
163: WHERE oop.acct_period_id = p_acct_period_id
164: AND oop.organization_id = HOU.organization_id
165: AND HOU.name = p_org;
544: MAX( acct_period_id )
545: INTO
546: l_max_period_id
547: FROM
548: org_acct_periods orgp
549: WHERE orgp.organization_id = p_org_id
550: AND orgp.period_set_name = p_period_set_name
551: AND orgp.open_flag = 'Y' ;
552:
582: , x_period_start_date
583: , x_period_end_date
584: FROM
585: gl_periods glp
586: , org_acct_periods orgp
587: WHERE glp.period_name = orgp.period_name
588: AND glp.period_set_name = p_period_set_name
589: AND glp.period_type = p_period_type
590: AND orgp.acct_period_id = l_max_period_id
691: MIN( acct_period_id )
692: INTO
693: l_min_period_id
694: FROM
695: org_acct_periods orgp
696: WHERE orgp.organization_id = p_org_id
697: AND orgp.period_set_name = p_period_set_name ;
698:
699: IF G_DEBUG = 'Y' THEN
727: , x_period_start_date
728: , x_period_end_date
729: FROM
730: gl_periods glp
731: , org_acct_periods orgp
732: WHERE glp.period_name = orgp.period_name
733: AND glp.period_set_name = p_period_set_name
734: AND glp.period_type = p_period_type
735: AND orgp.acct_period_id = l_min_period_id
920: l_closed_if_res_recmd NUMBER := 0;
921:
922: l_allow_close varchar2(240);
923:
924: l_max_open_period_name ORG_ACCT_PERIODS.PERIOD_NAME%TYPE;
925: l_max_open_period_start_date DATE;
926: l_max_open_period_end_date DATE;
927: l_max_period_start_date DATE;
928:
954: AND glp.period_type = l_sob_period_type
955: AND glp.PERIOD_SET_NAME = l_period_set_name
956: AND glp.PERIOD_NAME NOT IN
957: ( SELECT OAP.PERIOD_NAME
958: FROM ORG_ACCT_PERIODS OAP
959: WHERE OAP.PERIOD_SET_NAME = glp.PERIOD_SET_NAME
960: AND OAP.PERIOD_NAME = glp.PERIOD_NAME
961: AND OAP.organization_id = c_org_id
962: )
970: -- Select the periods from GL_PERIODS
971: -- that are eligible to be
972: -- Closed for a given Org
973:
974: CURSOR c_org_acct_periods_open IS
975: SELECT
976: orgp.rowid closing_rowid
977: , orgp.ACCT_PERIOD_ID closing_acct_period_id
978: , orgp.ORGANIZATION_ID organization_id
983: , orgp.PERIOD_NUM open_period_num
984: , orgp.PERIOD_NAME open_period_name
985: , orgp.open_flag open_flag
986: FROM
987: org_acct_periods orgp
988: WHERE orgp.period_name = p_close_period_name
989: AND orgp.organization_id = l_orgid
990: AND orgp.period_set_name = l_period_set_name ;
991:
989: AND orgp.organization_id = l_orgid
990: AND orgp.period_set_name = l_period_set_name ;
991:
992:
993: l_org_acct_periods_val_open c_org_acct_periods_open%ROWTYPE ;
994:
995: -- log error message
996: l_error_msg VARCHAR2(255);
997: --Initialization of variable l_verify_flag is removed. Bug: 3590042
1022: -- For this reason, this validation is transferred at backend.
1023: -- IF p_open_or_close_flag = 'C' THEN
1024: -- SELECT count(*)
1025: -- INTO l_count
1026: -- FROM ORG_ACCT_PERIODS
1027: -- WHERE SCHEDULE_CLOSE_DATE > SYSDATE
1028: -- AND PERIOD_NAME = p_close_period_name
1029: -- AND ORGANIZATION_ID = p_org_hierarchy_origin;
1030:
1047:
1048: -- The API's return status details the process outcome , which is
1049: -- inserted into the PL/SQL table and later printed as Log report
1050:
1051: -- The periods for Closing are selected from the ORG_ACCT_PERIODS
1052: -- and the periods to Open are retreived from GL_PERIODS
1053:
1054: G_LOG_REPORT_TABLE.DELETE ;
1055:
2121: END IF; -- process only for child organizations
2122:
2123:
2124: IF p_open_or_close_flag = 'C' THEN
2125: FOR l_org_acct_periods_val_open IN c_org_acct_periods_open
2126: LOOP
2127: IF l_org_acct_periods_val_open.open_flag = 'Y'
2128: THEN
2129: -- The period close is first verified if it can be Opened
2123:
2124: IF p_open_or_close_flag = 'C' THEN
2125: FOR l_org_acct_periods_val_open IN c_org_acct_periods_open
2126: LOOP
2127: IF l_org_acct_periods_val_open.open_flag = 'Y'
2128: THEN
2129: -- The period close is first verified if it can be Opened
2130: -- If YES, the Close period Inventory API is called
2131:
2150:
2151: INV_ORGHIERARCHY_PVT.Log
2152: (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2153: ,' Org ID ' || l_orgid || ' for period ' ||
2154: l_org_acct_periods_val_open.open_period_name ||
2155: ' Schedule Close Date:' || l_org_acct_periods_val_open.schedule_close_date
2156: );
2157: END IF;
2158:
2151: INV_ORGHIERARCHY_PVT.Log
2152: (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2153: ,' Org ID ' || l_orgid || ' for period ' ||
2154: l_org_acct_periods_val_open.open_period_name ||
2155: ' Schedule Close Date:' || l_org_acct_periods_val_open.schedule_close_date
2156: );
2157: END IF;
2158:
2159: -- Bug: 3590042. Initialization of variable l_verify_flag is added.
2162:
2163: CST_AccountingPeriod_PUB.VERIFY_PERIODCLOSE
2164: ( p_api_version => l_api_version
2165: , p_org_id => l_orgid
2166: , p_closing_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2167: , p_closing_end_date => l_org_acct_periods_val_open.schedule_close_date
2168: , x_open_period_exists => l_open_period_exists
2169: , x_proper_order => l_proper_order
2170: , x_end_date_is_past => l_end_date_is_past
2163: CST_AccountingPeriod_PUB.VERIFY_PERIODCLOSE
2164: ( p_api_version => l_api_version
2165: , p_org_id => l_orgid
2166: , p_closing_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2167: , p_closing_end_date => l_org_acct_periods_val_open.schedule_close_date
2168: , x_open_period_exists => l_open_period_exists
2169: , x_proper_order => l_proper_order
2170: , x_end_date_is_past => l_end_date_is_past
2171: , x_download_in_process => l_download_in_process
2227: AND HOI.organization_id = l_orgid;
2228:
2229: l_le_sysdate := INV_LE_TIMEZONE_PUB.GET_LE_SYSDATE_FOR_OU(
2230: l_legal_entity);
2231: IF (l_org_acct_periods_val_open.period_start_date > l_le_sysdate) THEN
2232: FND_MESSAGE.SET_NAME('BOM','CST_CLOSE_FUTURE_PERIOD');
2233: l_error_msg := SUBSTR(FND_MESSAGE.Get, 1,255);
2234: IF G_DEBUG = 'Y' THEN
2235: INV_ORGHIERARCHY_PVT.Log
2241: ELSE
2242: -- Convert Period end date into server time zone. Adding .99999 becasue
2243: -- Period end date does not store time factor.
2244: l_period_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2245: l_org_acct_periods_val_open.schedule_close_date + .99999,
2246: l_legal_entity);
2247: -- Get remaining hours from sysdate
2248: l_hours_remaining := round((l_period_end_date - sysdate) * 24);
2249:
2254: );
2255: END IF;
2256: ADD_ITEM
2257: ( p_org => NULL
2258: , p_period => l_org_acct_periods_val_open.open_period_name
2259: , p_status => 'Warning'
2260: , p_reason => 'Remaining hours to close period : ' || l_hours_remaining
2261: , p_request_id => NULL
2262: , p_closed => NULL
2259: , p_status => 'Warning'
2260: , p_reason => 'Remaining hours to close period : ' || l_hours_remaining
2261: , p_request_id => NULL
2262: , p_closed => NULL
2263: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2264: );
2265: END IF; --IF (l_period_open_date > l_le_sysdate)
2266:
2267:
2310: -- insert error log on the report
2311: IF l_verify_flag = 'N' THEN
2312: ADD_ITEM
2313: ( p_org => NULL
2314: , p_period => l_org_acct_periods_val_open.open_period_name
2315: , p_status => 'Ignore'
2316: , p_reason => l_error_msg
2317: , p_request_id => NULL
2318: , p_closed => 'N'
2315: , p_status => 'Ignore'
2316: , p_reason => l_error_msg
2317: , p_request_id => NULL
2318: , p_closed => 'N'
2319: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2320: );
2321: END IF;
2322:
2323: -- Proceed further to close the period only if the
2327: -- Bug#2386091 fix - added parameter pending_ship
2328: CST_AccountingPeriod_PUB.get_pendingtcount
2329: ( p_api_version => l_api_version
2330: , p_org_id => l_orgid
2331: , p_closing_period => l_org_acct_periods_val_open.closing_acct_period_id
2332: , p_sched_close_date => l_org_acct_periods_val_open.schedule_close_date
2333: , x_pend_receiving => l_pend_receiving
2334: , x_unproc_matl => l_unproc_matl
2335: , x_pend_matl => l_pend_matl
2328: CST_AccountingPeriod_PUB.get_pendingtcount
2329: ( p_api_version => l_api_version
2330: , p_org_id => l_orgid
2331: , p_closing_period => l_org_acct_periods_val_open.closing_acct_period_id
2332: , p_sched_close_date => l_org_acct_periods_val_open.schedule_close_date
2333: , x_pend_receiving => l_pend_receiving
2334: , x_unproc_matl => l_unproc_matl
2335: , x_pend_matl => l_pend_matl
2336: , x_uncost_matl => l_uncost_matl
2352: OR
2353: ( p_close_if_res_recmd = 'Y' AND l_pend_receiving >= 0 AND l_pend_matl >= 0
2354: AND l_pend_move >= 0 AND l_released_work_orders >= 0 AND
2355: (l_pending_ship = 0 OR
2356: (cst_periodcloseoption_pub.get_shippingtxnhook_value(p_org_id => l_orgid, p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id) = 1
2357: AND l_pending_ship >= 0)
2358: )
2359: )
2360: )
2388: INV_ORGHIERARCHY_PVT.Log
2389: (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2390: ,' Calling CLOSE_PERIOD for Org ID '
2391: || l_orgid || ' for period ' ||
2392: l_org_acct_periods_val_open.open_period_name
2393: );
2394: END IF;
2395:
2396: /* myerrams, Bug:4599201. Modified the call to
2409: ( p_api_version => l_api_version
2410: , p_org_id => l_orgid
2411: , p_user_id => l_user_id
2412: , p_login_id => l_login_id
2413: , p_closing_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2414: , x_wip_failed => l_wip_failed
2415: , x_close_failed => l_close_failed
2416: , x_req_id => l_req_id
2417: , x_unprocessed_txns => l_unprocessed_txns
2436: ) ;
2437: END IF;
2438: ADD_ITEM
2439: ( p_org => NULL
2440: , p_period => l_org_acct_periods_val_open.open_period_name
2441: , p_status => 'Failed'
2442: , p_reason => 'WIP Failed'
2443: , p_request_id => l_req_id
2444: , p_closed => 'N'
2441: , p_status => 'Failed'
2442: , p_reason => 'WIP Failed'
2443: , p_request_id => l_req_id
2444: , p_closed => 'N'
2445: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2446: );
2447:
2448: END IF;
2449:
2457: );
2458: END IF;
2459: ADD_ITEM
2460: ( p_org => NULL
2461: , p_period => l_org_acct_periods_val_open.open_period_name
2462: , p_status => 'Failed'
2463: , p_reason => 'Download Failed'
2464: , p_request_id => l_req_id
2465: , p_closed => 'N'
2462: , p_status => 'Failed'
2463: , p_reason => 'Download Failed'
2464: , p_request_id => l_req_id
2465: , p_closed => 'N'
2466: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2467: );
2468:
2469: END IF;
2470:
2504: END IF;
2505:
2506: ADD_ITEM
2507: ( p_org => NULL
2508: , p_period => l_org_acct_periods_val_open.open_period_name
2509: , p_status => 'Failed'
2510: , p_reason => 'Close Failed'
2511: , p_request_id => l_req_id
2512: , p_closed => 'N'
2509: , p_status => 'Failed'
2510: , p_reason => 'Close Failed'
2511: , p_request_id => l_req_id
2512: , p_closed => 'N'
2513: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2514: );
2515:
2516: ROLLBACK ;
2517: ELSE --IF ( l_close_failed ) = true
2527: l_closed_if_res_recmd := 1;
2528: IF G_DEBUG = 'Y' THEN
2529: INV_ORGHIERARCHY_PVT.Log
2530: ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2531: , ' WARNING : There are pending transactions with resolutions recommended for '||l_org_acct_periods_val_open.open_period_name||
2532: ' in '||l_orgid
2533: );
2534: END IF;
2535: ADD_ITEM
2533: );
2534: END IF;
2535: ADD_ITEM
2536: ( p_org => NULL
2537: , p_period => l_org_acct_periods_val_open.open_period_name
2538: , p_status => 'Warning'
2539: , p_reason => 'There are pending transactions with resolutions recommended'
2540: , p_request_id => NULL
2541: , p_closed => 'N'
2538: , p_status => 'Warning'
2539: , p_reason => 'There are pending transactions with resolutions recommended'
2540: , p_request_id => NULL
2541: , p_closed => 'N'
2542: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2543: );
2544: END IF;
2545: ADD_ITEM
2546: ( p_org => NULL
2543: );
2544: END IF;
2545: ADD_ITEM
2546: ( p_org => NULL
2547: , p_period => l_org_acct_periods_val_open.open_period_name
2548: , p_status => 'Processing'
2549: , p_reason => NULL
2550: , p_request_id => l_req_id
2551: , p_closed => 'N'
2548: , p_status => 'Processing'
2549: , p_reason => NULL
2550: , p_request_id => l_req_id
2551: , p_closed => 'N'
2552: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2553: );
2554: COMMIT ;
2555: END IF; --IF ( l_close_failed ) = true
2556:
2562: );
2563: END IF;
2564: ADD_ITEM
2565: ( p_org => NULL
2566: , p_period => l_org_acct_periods_val_open.open_period_name
2567: , p_status => 'Ignore'
2568: , p_reason => 'Pending transactions exists'
2569: , p_request_id => NULL
2570: , p_closed => 'N'
2567: , p_status => 'Ignore'
2568: , p_reason => 'Pending transactions exists'
2569: , p_request_id => NULL
2570: , p_closed => 'N'
2571: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2572: );
2573: END IF ; -- check for pending transaction
2574:
2575: -- verify_periodclose failed
2593: /* Commented following code for showing details of only eligible
2594: periods in Summary report. Bug: 3555234
2595: ADD_ITEM
2596: ( p_org => NULL
2597: , p_period => l_org_acct_periods_val_open.open_period_name
2598: , p_status => 'Ignore'
2599: , p_reason => 'Not Open'
2600: , p_request_id => NULL
2601: , p_closed => 'N'
2598: , p_status => 'Ignore'
2599: , p_reason => 'Not Open'
2600: , p_request_id => NULL
2601: , p_closed => 'N'
2602: , p_acct_period_id => l_org_acct_periods_val_open.closing_acct_period_id
2603: );*/
2604:
2605: END IF; -- open flag check
2606: END LOOP;