1)     CZ_ADDRESSES

2)     CZ_ADDRESS_USES

3)     CZ_ARCHIVES

4)     CZ_ARCHIVE_REFS

5)     CZ_ATP_REQUESTS

6)     CZ_BATCH_VAL_SESSION_DATA

7)     CZ_COMBO_FEATURES

8)     CZ_CONFIG_ATTRIBUTES

9)     CZ_CONFIG_EXT_ATTRIBUTES

10)  CZ_CONFIG_HDRS

11)  CZ_CONFIG_INPUTS

12)  CZ_CONFIG_ITEMS

13)  CZ_CONFIG_MESSAGES

14)  CZ_CONFIG_USAGES

15)  CZ_CONTACTS

16)  CZ_CUSTOMERS

17)  CZ_CUSTOMER_END_USERS

18)  CZ_DB_LOGS

19)  CZ_DB_SETTINGS

20)  CZ_DB_SIZES

21)  CZ_DES_CHART_CELLS

22)  CZ_DES_CHART_COLUMNS

23)  CZ_DES_CHART_FEATURES

24)  CZ_DEVL_PRJ_USER_GROUPS

25)  CZ_DEVL_PROJECTS

26)  CZ_DRILL_DOWN_ITEMS

27)  CZ_EFFECTIVITY_SETS

28)  CZ_END_USERS

29)  CZ_END_USER_GROUPS

30)  CZ_EXPRESSIONS

31)  CZ_EXPRESSION_NODES

32)  CZ_EXP_TMP_LINES

33)  CZ_EXT_APPLICATIONS

34)  CZ_FILTER_SETS

35)  CZ_FUNC_COMP_REFS

36)  CZ_FUNC_COMP_SPECS

37)  CZ_GRID_CELLS

38)  CZ_GRID_COLS

39)  CZ_GRID_DEFS

40)  CZ_IMP_ADDRESS

41)  CZ_IMP_ADDRESS_USE

42)  CZ_IMP_CONTACT

43)  CZ_IMP_CUSTOMER

44)  CZ_IMP_CUSTOMER_END_USER

45)  CZ_IMP_DEVL_PROJECT

46)  CZ_IMP_END_USER

47)  CZ_IMP_END_USER_GROUP

48)  CZ_IMP_INTL_TEXT

49)  CZ_IMP_ITEM_MASTER

50)  CZ_IMP_ITEM_PARENT

51)  CZ_IMP_ITEM_PROPERTY_VALUE

52)  CZ_IMP_ITEM_TYPE

53)  CZ_IMP_ITEM_TYPE_PROPERTY

54)  CZ_IMP_LOCALIZED_TEXTS

55)  CZ_IMP_MODEL_REF_EXPLS

56)  CZ_IMP_PRICE

57)  CZ_IMP_PRICE_GROUP

58)  CZ_IMP_PROPERTY

59)  CZ_IMP_PS_NODES

60)  CZ_IMP_RULES

61)  CZ_IMP_TMP_ITEMCAT

62)  CZ_IMP_USER_GROUP

63)  CZ_INTL_TEXTS

64)  CZ_ITEM_MASTERS

65)  CZ_ITEM_PARENTS

66)  CZ_ITEM_PROPERTY_VALUES

67)  CZ_ITEM_TYPES

68)  CZ_ITEM_TYPE_PROPERTIES

69)  CZ_JRAD_CHUNKS

70)  CZ_LCE_CLOBS

71)  CZ_LCE_HEADERS

72)  CZ_LCE_LINES

73)  CZ_LCE_LOAD_SPECS

74)  CZ_LCE_OPERANDS

75)  CZ_LCE_TEXTS

76)  CZ_LOCALES

77)  CZ_LOCALIZED_TEXTS

78)  CZ_LOCK_HISTORY

79)  CZ_LOOKUP_VALUES

80)  CZ_LOOKUP_VALUES_TL

81)  CZ_MODEL_PUBLICATIONS

82)  CZ_MODEL_REF_EXPLS

83)  CZ_MODEL_USAGES

84)  CZ_MODEL_USAGES_TL

85)  CZ_OPPORTUNITY_HDRS

86)  CZ_OPP_HDR_CONTACTS

87)  CZ_PB_CLIENT_APPS

88)  CZ_PB_LANGUAGES

89)  CZ_PB_MODEL_EXPORTS

90)  CZ_PB_TEMP_IDS

91)  CZ_POPULATORS

92)  CZ_POPULATOR_MAPS

93)  CZ_PRICES

94)  CZ_PRICE_GROUPS

95)  CZ_PRICING_STRUCTURES

96)  CZ_PROPERTIES

97)  CZ_PROPOSAL_HDRS

98)  CZ_PROP_QUOTE_HDRS

99)  CZ_PSNODE_PROPCOMPAT_GENS

100)         CZ_PS_NODES

101)         CZ_PS_PROP_VALS

102)         CZ_PUBLICATION_USAGES

103)         CZ_QUOTE_HDRS

104)         CZ_QUOTE_MAIN_ITEMS

105)         CZ_QUOTE_ORDERS

106)         CZ_QUOTE_SPARES

107)         CZ_QUOTE_SPECIAL_ITEMS

108)         CZ_REL_TYPES

109)         CZ_RP_ENTRIES

110)         CZ_RULES

111)         CZ_RULE_FOLDERS

112)         CZ_SERVERS

113)         CZ_SIGNATURES

114)         CZ_SIGNATURE_ARGUMENTS

115)         CZ_SPARES_SPECIALS

116)         CZ_SUB_CON_SETS

117)         CZ_TERMINATE_MSGS

118)         CZ_TYPE_RELATIONSHIPS

119)         CZ_UI_ACTIONS

120)         CZ_UI_CONT_TYPE_TEMPLS

121)         CZ_UI_DEFS

122)         CZ_UI_IMAGES

123)         CZ_UI_NODES

124)         CZ_UI_NODE_PROPS

125)         CZ_UI_PAGES

126)         CZ_UI_PAGE_ELEMENTS

127)         CZ_UI_PAGE_REFS

128)         CZ_UI_PAGE_SETS

129)         CZ_UI_PROPERTIES

130)         CZ_UI_REFS

131)         CZ_UI_REF_TEMPLATES

132)         CZ_UI_TEMPLATES

133)         CZ_UI_TEMPLATE_ELEMENTS

134)         CZ_UI_XMLS

135)         CZ_USER_GROUPS

136)         CZ_XFR_FIELDS

137)         CZ_XFR_FIELD_REQUIRES

138)         CZ_XFR_PRICE_LISTS

139)         CZ_XFR_PROJECT_BILLS

140)         CZ_XFR_RUN_INFOS

141)         CZ_XFR_RUN_RESULTS

142)         CZ_XFR_STATUS_CODES

143)         CZ_XFR_TABLES

 SELECT   
ooha.header_id order_header_id, 
ottt.NAME order_type_name,
ooha.order_number, 
ooha.ordered_date,
ooha.transactional_curr_code order_currency, 
hp.party_id,
hp.party_number, 
hp.party_name customer_name,
hca.cust_account_id customer_id, 
hca.account_number customer_number,
oola.line_id order_line_id, 
oola.line_number, 
oola.inventory_item_id,
msib.segment1 item_number, 
msib.description item_desc,
oola.attribute15 superseded_item, 
oola.order_quantity_uom,
oola.ordered_quantity, 
oola.unit_selling_price
    FROM OE_ORDER_HEADERS_ALL OOHA,
         OE_ORDER_LINES_ALL OOLA,
         OE_TRANSACTION_TYPES_TL OTTT,
         MTL_SYSTEM_ITEMS_B MSIB,
         MTL_PARAMETERS MP,
         ORG_ORGANIZATION_DEFINITIONS OOD,
         HZ_PARTIES HP,
         HZ_CUST_ACCOUNTS HCA
   WHERE ooha.header_id = oola.header_id
     AND ottt.transaction_type_id(+) = ooha.order_type_id
     AND ottt.LANGUAGE = USERENV ('LANG')
     AND hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
     AND ooha.org_id = oola.org_id(+)
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = mp.master_organization_id
     AND mp.organization_id = ood.organization_id
     AND mp.master_organization_id = mp.organization_id
     AND ood.operating_unit = fnd_profile.VALUE ('ORG_ID')
     AND ooha.order_number = :P_sales_order_number;

 SELECT hou.name ou_name,

prh.segment1 requisition_num, prh.creation_date, prh.created_by, poh.agent_id, poh.po_header_id, poh.segment1 po_num, ppx.full_name Requestor_Name, prh.description Req_Description, auth_status.displayed_field authorization_status, prh.org_id, prh.note_to_authorizer, req_type.displayed_field type_lookup_code, prl.catalog_type, prl.blanket_po_header_id, prl.blanket_po_line_num, prl.catalog_type, prl.line_num, plt.order_type_lookup_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, COALESCE(prd.req_line_quantity,prl.quantity) quantity, (prl.unit_price * COALESCE(prd.req_line_quantity, prl.quantity ) ) line_amount, prl.quantity_delivered, prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date, pla.cancel_reason, glcc.concatenated_segments charge_account, aps.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_code, plc.displayed_field po_status, hr.location_code deliver_to_location, prl.urgent_flag, (SELECT segment1 FROM po_headers_all pha WHERE pha.po_header_id = prl.blanket_po_header_id ) bpa_number, (SELECT (CASE WHEN SYSDATE BETWEEN pha.start_date AND NVL(pha.end_date,SYSDATE+1) THEN 'ACTIVE' ELSE 'INACTIVE' END ) FROM po_headers_all pha WHERE pha.po_header_id = prl.blanket_po_header_id ) bpa_status, prl.blanket_po_line_num, (SELECT (CASE WHEN NVL((pl.expiration_date), (SYSDATE)) >= (SYSDATE) THEN 'ACTIVE' ELSE 'INACTIVE' END ) FROM po_lines_all pl WHERE pl.po_header_id = prl.blanket_po_header_id AND pl.line_num = prl.blanket_po_line_num ) bpa_line_status, prl.suggested_vendor_product_code vendor_item_number, prl.rate_date exchange_rate_date, prl.rate exchange_rate, prl.currency_unit_price FROM po_requisition_headers_all prh, hr_all_organization_units hou, hr_organization_information hoi, po_requisition_lines_all prl, po_req_distributions_all prd, gl_code_combinations_kfv glcc, per_people_x ppx, po_headers_all poh, po_lines_all pla, ap_suppliers aps, ap_supplier_sites_all assa, po_distributions_all pda, hr_locations hr, po_lookup_codes plc, po_lookup_codes auth_status, po_lookup_codes req_type, po_line_types plt WHERE prh.requisition_header_id = prl.requisition_header_id AND hou.organization_id = prh.org_id AND hou.organization_id = hoi.organization_id AND hoi.org_information_context = 'Operating Unit Information' AND ppx.person_id = prh.preparer_id AND prh.type_lookup_code = 'PURCHASE' AND prd.requisition_line_id = prl.requisition_line_id AND pda.req_distribution_id = prd.distribution_id AND glcc.code_combination_id = prd.code_combination_id AND pda.po_line_id = pla.po_line_id AND pla.po_header_id = poh.po_header_id AND pda.po_header_id = poh.po_header_id AND pda.deliver_to_location_id = hr.location_id AND poh.vendor_id = aps.vendor_id AND poh.vendor_site_id = assa.vendor_site_id AND aps.vendor_id = assa.vendor_id AND prl.line_type_id = plt.line_type_id AND plc.lookup_type = 'DOCUMENT STATE' AND plc.lookup_code = poh.closed_code AND auth_status.lookup_type = 'AUTHORIZATION STATUS' AND auth_status.lookup_code = prh.authorization_status AND req_type.lookup_type = 'REQUISITION TYPE' AND req_type.lookup_code = prh.type_lookup_code

 

SELECT fcr.request_id ,

  fcpt.user_concurrent_program_name

  || NVL2(fcr.description, ' ('

  || fcr.description

  || ')', NULL) conc_prog ,

  fu.user_name requestor ,

  fu.description requested_by ,

  fu.email_address ,

  frt.responsibility_name requested_by_resp ,

  TRIM(fl.meaning) STATUS ,

  fcr.phase_code ,

  fcr.status_code ,

  fcr.argument_text "PARAMETERS" ,

  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,

  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,

  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,

  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN SUBSTR( fu.description , 0 , 40 )

  END last_update_by ,

  CASE

    WHEN fcr.hold_flag = 'Y'

    THEN fcr.last_update_date

  END last_update_date ,

  fcr.increment_dates ,

  CASE

    WHEN fcrc.CLASS_INFO IS NULL

    THEN 'Yes: '

      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

    ELSE 'n/a'

  END run_once ,

  CASE

    WHEN fcrc.class_type = 'P'

    THEN 'Repeat every '

      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')

      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')

    ELSE 'n/a'

  END set_days_of_week ,

  CASE

    WHEN fcrc.class_type                         = 'S'

    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0

    THEN 'Days of week: '

      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')

      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')

      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')

      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')

      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')

      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')

      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')

    ELSE 'n/a'

  END days_of_week

FROM apps.fnd_concurrent_requests fcr ,

  apps.fnd_user fu ,

  apps.fnd_concurrent_programs fcp ,

  apps.fnd_concurrent_programs_tl fcpt ,

  apps.fnd_printer_styles_tl fpst ,

  apps.fnd_conc_release_classes fcrc ,

  apps.fnd_responsibility_tl frt ,

  apps.fnd_lookups fl

WHERE fcp.application_id       = fcpt.application_id

AND fcr.requested_by           = fu.user_id

AND fcr.concurrent_program_id  = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id  = fcpt.concurrent_program_id

AND fcr.responsibility_id      = frt.responsibility_id

AND fcr.print_style            = fpst.printer_style_name(+)

AND fcr.release_class_id       = fcrc.release_class_id(+)

AND fcr.status_code            = fl.lookup_code

AND fl.lookup_type             = 'CP_STATUS_CODE'

AND fcr.phase_code             = 'P'

AND frt.language               = 'US'

AND fpst.language              = 'US'

AND fcpt.language              = 'US'

ORDER BY Fu.Description,

  Fcr.Requested_Start_Date ASC

 


SELECT s.inst_id,
NVL (s.username, 'Internal') "User Name",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",

fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;


Here below is the detail sql query to find the item category value in oracle apps.

select MSB.segment1 ITEM_CODE,
  MSB.DESCRIPTION ITEM_DESCRIPTION,
     MCST.category_set_name,
       MCK.CONCATENATED_SEGMENTS CATEGORY
  from apps.mtl_system_items_b MSB,
       apps.mtl_categories_kfv MCK,
       apps.mtl_item_categories MIC,
        apps.mtl_category_sets_tl MCST
       where MIC.inventory_item_id = MSB.inventory_item_id
       and MIC.category_id = MCK.category_id
      and MSB.organization_id =MIC.organization_id
     and MSB.organization_id = :P_INV_ORGANIZATION
     and MCST.category_set_id=MIC.category_set_id


hr_employee_api.create_employee API is used to create new employee with other important information as per the requirement.

hr_employee_api.create_employee

(p_hire_date                      => TO_DATE (’12-AUG-2023′),

p_business_group_id=>fnd_profile.value_specific(‘PER_BUSINESS_GROUP_ID’)

p_last_name                      => ‘ P_EMP_LAST_NAME ‘,

p_first_name                     => ‘ P_EMP_FIRST_NAME ‘,

p_middle_names                   => NULL,

p_sex                            => ‘M’,

p_national_identifier            => ‘256-54-575′,

p_date_of_birth                  => TO_DATE (’05-NOV-1990’),

p_known_as                       => ‘ P_DUMMY ‘,

p_employee_number                => lc_employee_number,

p_person_id                      => ln_person_id,

p_assignment_id                  => ln_assignment_id,

p_per_object_version_number      => ln_object_ver_number,

p_asg_object_version_number      => ln_asg_ovn,

p_per_effective_start_date       => ld_per_effective_start_date,

p_per_effective_end_date         => ld_per_effective_end_date,

p_full_name                      => lc_full_name,

p_per_comment_id                 => ln_per_comment_id,

p_assignment_sequence            => ln_assignment_sequence,

p_assignment_number              => lc_assignment_number,

p_name_combination_warning       => lb_name_combination_warning,

p_assign_payroll_warning         => lb_assign_payroll_warning,

p_orig_hire_warning              => lb_orig_hire_warning);