[Home] [Help]
127: p_source_type IN VARCHAR2
128: )
129: IS
130: SELECT to_number(a.org_information2) legal_entity_id
131: FROM hr_organization_information a, gl_plcy_mst b
132: WHERE a.organization_id = b.org_id
133: AND b.co_code = p_co_code
134: AND p_source_type = 'O'
135: and a.org_information_context = 'Operating Unit Information'
134: AND p_source_type = 'O'
135: and a.org_information_context = 'Operating Unit Information'
136: UNION
137: SELECT a.legal_entity_id
138: FROM gl_plcy_mst a
139: WHERE a.co_code = p_co_code
140: AND p_source_type = 'N';
141:
142: BEGIN
1160: p_legal_entity_id IN NUMBER
1161: )
1162: IS
1163: SELECT rctta.name
1164: FROM ra_cust_trx_types_all rctta, ar_lookups al, gl_plcy_mst plcy
1165: WHERE sysdate between nvl(rctta.start_date, sysdate-1) and nvl(rctta.end_date, sysdate+1)
1166: AND al.lookup_type = 'INV/CM'
1167: AND al.lookup_code = rctta.type
1168: AND rctta.org_id = plcy.org_id
1529: p.length,
1530: p.segment_no segment_ref,
1531: pm.segment_delimiter
1532: FROM gl_plcy_seg p,
1533: gl_plcy_mst pm,
1534: fnd_id_flex_segments f,
1535: gl_sets_of_books s
1536: WHERE p.co_code = p_co_code
1537: AND p.delete_mark = 0
1550: glsob.chart_of_accounts_id
1551: FROM gl_sets_of_books glsob,
1552: fnd_id_flex_structures fifstr,
1553: fnd_application fa,
1554: gl_plcy_mst gpm
1555: WHERE glsob.chart_of_accounts_id = fifstr.id_flex_num
1556: AND fifstr.id_flex_code = 'GL#'
1557: AND fifstr.application_id = fa.application_id
1558: AND fa.application_short_name = 'SQLGL'
1734: * Migrate_Fiscal_policies_LE *
1735: * *
1736: * DESCRIPTION: *
1737: * This PL/SQL procedure is used to migrate the Fiscal Policies from *
1738: * GL_PLCY_MST to GMF_FISCAL_POLICIES table. *
1739: * *
1740: * PARAMETERS: *
1741: * P_migration_run_id - id to use to right to migration log *
1742: * x_exception_count - Number of exceptions occurred. *
1787: ****************/
1788:
1789: CURSOR cur_get_fiscal_policies IS
1790: SELECT *
1791: FROM gl_plcy_mst
1792: WHERE NVL(migrated_ind,'~') <> '1'
1793: ORDER BY legal_entity_id NULLS LAST;
1794:
1795: BEGIN
1962: i.attribute30,
1963: i.attribute_category
1964: );
1965:
1966: UPDATE gl_plcy_mst
1967: SET migrated_ind = '1',
1968: legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
1969: last_update_date = SYSDATE
1970: WHERE co_code = i.co_code;
2001: END;
2002:
2003: ELSIF L_legal_entity_id IS NOT NULL AND nvl(l_le_count,0) > 0 THEN
2004:
2005: UPDATE gl_plcy_mst
2006: SET migrated_ind = '1',
2007: legal_entity_id = decode(legal_entity_id, NULL, L_legal_entity_id, legal_entity_id),
2008: last_update_date = SYSDATE
2009: WHERE co_code = i.co_code;
2035: **********************************************/
2036:
2037: SELECT count(*)
2038: INTO x_failure_count
2039: FROM gl_plcy_mst
2040: WHERE (legal_entity_id IS NULL AND co_code IS NOT NULL);
2041:
2042: IF nvl(x_failure_count,0) > 0 THEN
2043:
2859: a.last_update_login,
2860: a.text_code,
2861: a.delete_mark
2862: FROM cm_cldr_hdr_b a,
2863: gl_plcy_mst b,
2864: cm_mthd_mst c
2865: WHERE a.cost_mthd_code IS NOT NULL
2866: AND a.co_code IS NOT NULL
2867: AND b.legal_entity_id IS NOT NULL
2948: g.text_code,
2949: 1
2950: FROM cm_cldr_hdr_b g,
2951: cm_mthd_mst h,
2952: gl_plcy_mst i
2953: WHERE g.co_code IS NOT NULL
2954: AND i.legal_entity_id IS NOT NULL
2955: AND g.cost_mthd_code <> h.cost_mthd_code
2956: AND i.co_code = g.co_code
3088: g.text_code,
3089: 1
3090: FROM cm_cldr_hdr_b g,
3091: cm_mthd_mst h,
3092: gl_plcy_mst i,
3093: sy_orgn_mst j,
3094: cm_rsrc_dtl k
3095: WHERE g.co_code IS NOT NULL
3096: AND j.orgn_code = k.orgn_code
3184: g.text_code,
3185: 1
3186: FROM cm_cldr_hdr_b g,
3187: cm_mthd_mst h,
3188: gl_plcy_mst i,
3189: sy_orgn_mst j,
3190: cm_brdn_dtl k
3191: WHERE g.co_code IS NOT NULL
3192: AND j.orgn_code = k.orgn_code
3280: g.text_code,
3281: 1
3282: FROM cm_cldr_hdr_b g,
3283: cm_mthd_mst h,
3284: gl_plcy_mst i,
3285: sy_orgn_mst j,
3286: cm_cmpt_dtl k,
3287: ic_whse_mst l
3288: WHERE g.co_code IS NOT NULL
3378: g.text_code,
3379: 1
3380: FROM cm_cldr_hdr_b g,
3381: cm_mthd_mst h,
3382: gl_plcy_mst i,
3383: sy_orgn_mst j,
3384: cm_adjs_dtl k,
3385: ic_whse_mst l
3386: WHERE g.co_code IS NOT NULL
3885: x.legal_entity_id
3886: FROM gmf_period_statuses x,
3887: cm_mthd_mst y,
3888: cm_cldr_hdr_b z,
3889: gl_plcy_mst w
3890: WHERE y.cost_mthd_code = a.cost_mthd_code
3891: AND x.cost_type_id = y.cost_type_id
3892: AND x.calendar_code = a.calendar_code
3893: AND x.period_code = a.period_code
4736: ),
4737: a.legal_entity_id
4738: = (
4739: SELECT x.legal_entity_id
4740: FROM gl_plcy_mst x
4741: WHERE x.co_code = a.co_code
4742: )
4743: WHERE (a.cost_type_id IS NULL AND a.cost_mthd_code IS NOT NULL)
4744: OR (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
4984: ),
4985: a.legal_entity_id
4986: = (
4987: SELECT x.legal_entity_id
4988: FROM gl_plcy_mst x
4989: WHERE x.co_code = a.co_code
4990: ),
4991: (
4992: a.organization_id,
6662: e.short_name,
6663: e.name,
6664: e.ledger_id
6665: FROM gmf_fiscal_policies a,
6666: gl_plcy_mst b,
6667: gmf_legal_entities c,
6668: mtl_parameters d,
6669: gl_ledgers e
6670: WHERE a.legal_entity_id = b.legal_entity_id
9766: SET a.organization_pri = nvl(a.whse_code_pri, a.orgn_code_pri),
9767: a.legal_entity_id
9768: = (
9769: SELECT x.legal_entity_id
9770: FROM gl_plcy_mst x
9771: WHERE x.co_code = a.co_code
9772: )
9773: WHERE ((a.whse_code_pri IS NOT NULL OR a.orgn_code_pri IS NOT null) AND a.organization_pri IS NULL)
9774: OR (a.co_code IS NOT NULL AND a.legal_entity_id IS NULL);
10012: UPDATE cm_cmpt_mtl a
10013: SET a.legal_entity_id
10014: = (
10015: SELECT x.legal_entity_id
10016: FROM gl_plcy_mst x
10017: WHERE x.co_code = a.co_code
10018: )
10019: WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
10020:
10298:
10299: UPDATE gl_aloc_mst a
10300: SET a.legal_entity_id = (
10301: SELECT x.legal_entity_id
10302: FROM gl_plcy_mst x
10303: WHERE x.co_code = a.co_code
10304: )
10305: WHERE (a.legal_entity_id IS NULL AND a.co_code IS NOT NULL);
10306:
10535:
10536: UPDATE gl_evnt_plc a
10537: SET a.legal_entity_id = (
10538: SELECT x.legal_entity_id
10539: FROM gl_plcy_mst x
10540: WHERE x.co_code = a.co_code
10541: ),
10542: a.entity_code = decode(a.trans_source_type, 12, 'PURCHASING', NULL),
10543: a.event_class_code = decode(a.event_type, 110, 'DELIVER', NULL)
10725: a.delete_mark
10726: )
10727: = (
10728: SELECT w.organization_id, z.legal_entity_id, decode(a.delete_mark, 1, 1, decode(nvl(w.inventory_org_ind, 'N'), 'Y', 0, 1))
10729: FROM gl_plcy_mst z, sy_orgn_mst w
10730: WHERE w.orgn_code = a.orgn_code
10731: AND w.co_code = z.co_code
10732: ),
10733: a.source_organization_id = (
11191: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
11192: from gmf_period_statuses gps,
11193: cm_mthd_mst cmm,
11194: cm_cldr_hdr_b cch,
11195: gl_plcy_mst gpm
11196: where gps.calendar_code = cac.calendar_code
11197: and cch.calendar_code = cac.calendar_code
11198: and cch.co_code = gpm.co_code
11199: and gps.legal_entity_id = gpm.legal_entity_id
11385: gps.cost_type_id
11386: FROM gmf_period_statuses gps,
11387: cm_mthd_mst cmm,
11388: cm_cldr_hdr_b cch,
11389: gl_plcy_mst gpm
11390: WHERE gps.calendar_code = crc.calendar_code
11391: AND cch.calendar_code = crc.calendar_code
11392: AND cch.co_code = gpm.co_code
11393: AND gps.legal_entity_id = gpm.legal_entity_id
11695: (
11696: select gps.legal_entity_id, gps.period_id, gps.cost_type_id
11697: from gmf_period_statuses gps,
11698: cm_mthd_mst cmm,
11699: gl_plcy_mst gpm
11700: where gps.calendar_code = ccc.calendar_code
11701: and gpm.co_code = ccc.co_code
11702: and gps.period_code = ccc.period_code
11703: and cmm.cost_mthd_code = ccc.cost_mthd_code
11901: cmm.default_lot_cost_type_id,
11902: gfp.cost_basis
11903: from cm_mthd_mst cmm,
11904: cm_mthd_mst dcmm,
11905: gl_plcy_mst gpm,
11906: gmf_fiscal_policies gfp,
11907: xle_entity_profiles xep
11908: where gpm.co_code = a.co_code
11909: and gfp.legal_entity_id = gpm.legal_entity_id