AGIS SQL Queries

AGIS Interface Tables:

SELECT * FROM FUN_INTERFACE_BATCHES

SELECT * FROM FUN_INTERFACE_HEADERS

SELECT *  FROM FUN_INTERFACE_DIST_LINES

SELECT * FROM FUN_INTERFACE_REJECTIONS
SELECT * FROM FUN_INTERFACE_CONTROLS




AGIS Base Tables:


SELECT A.BATCH_NUMBER,
  A.BATCH_ID,
  A.STATUS BATCH_STATUS,
  B.STATUS TRANSACTION_STATUS,
  A.GL_DATE,
  A.BATCH_DATE,
  B.TRX_NUMBER,
  I.LEDGER_NAME "INIT_LEDGER",
  I.LEDGER_ID "INIT_LEDGER_ID",
  I.LEGAL_ENTITY_NAME "INIT_LE",
  R.LEDGER_NAME "REC_LEDGER",
  R.LEDGER_ID "REC_LEDGER_ID",
  R.LEGAL_ENTITY_NAME "REC_LE",
  B.APPROVER_ID,
  C.TRX_ID,
  C.INIT_AMOUNT_CR,
  C.INIT_AMOUNT_DR,
  C.RECI_AMOUNT_CR,
  C.RECI_AMOUNT_DR,
  D.PARTY_TYPE_FLAG,
  D.CCID,
  D.AMOUNT_CR,
  D.AMOUNT_DR,
  E.CONCATENATED_SEGMENTS,
  A.CREATED_BY
FROM FUN_TRX_BATCHES A,
  FUN_TRX_HEADERS B,
  FUN_TRX_LINES C,
  FUN_DIST_LINES D,
  GL_CODE_COMBINATIONS_KFV E,
  GL_LEDGER_LE_V I,
  Gl_Ledger_Le_V R
WHERE A.BATCH_ID = B.BATCH_ID
  --and B.STATUS     ='RECEIVED'
AND I.LEDGER_ID      =A.FROM_LEDGER_ID
AND I.LEGAL_ENTITY_ID=A.FROM_LE_ID
AND R.LEDGER_ID      =B.TO_LEDGER_ID
AND R.LEGAL_ENTITY_ID=B.TO_LE_ID
  --AND B.BATCH_ID = C.BATCH_ID
AND B.TRX_ID  = C.TRX_ID
AND C.LINE_ID = D.LINE_ID
AND D.CCID    = E.CODE_COMBINATION_ID
AND A.BATCH_NUMBER = '<Batch Number>'
ORDER BY a.BATCH_NUMBER,

  B.creation_date DESC



AGIS Setup Queries:


---Check Receipient or Initiator Legal Entities has Balacing segments or Not

SELECT Bsv.Legal_Entity_Id,
  Xle.Name,
  Xle.Party_Id ,
  Hp.Party_Name,
  Gll.Ledger_Id,
  Gll.Ledger_Name
FROM Gl_Legal_Entities_Bsvs Bsv,
  Xle_Entity_Profiles Xle,
  Hz_Parties Hp,
  Gl_Ledger_Le_V Gll
WHERE Bsv.Legal_Entity_Id   = Xle.Legal_Entity_Id
AND Xle.Party_Id            = Hp.Party_Id
And Xle.Legal_Entity_Id     = Gll.Legal_Entity_Id
AND Bsv.Flex_Segment_Value IN ('<<Balancing Segment Value>>')


---Intercompany Organization is Active Or Not-----------
SELECT *
FROM
  (SELECT hzp.party_id ORGID,
    hzp.party_name ORGNAME,
        hzp.party_number PARTY_REGISTRY_ID,
    NVL(hzr_le.object_id, le.party_id) LEID,
    NVL(hzp_le.party_name, le.name) LENAME,
    hzr_ou.subject_id OUID,
    hou.name OUNAME,
    DECODE(SIGN(NVL(hzusg.EFFECTIVE_END_DATE,SYSDATE)-SYSDATE),1,'Y','N') INTERCOMPANYFLAG,
    hzp.status STATUS,
    arl.meaning STATUSDISPLAY,
    hzp.orig_system_reference SYSTEMREFERENCE
  FROM hz_parties hzp,
    hz_party_usg_assignments hzusg,
    hz_relationships hzr_ou,
    hr_operating_units hou,
    hz_relationships hzr_le,
    hz_parties hzp_le,
    xle_entity_profiles le,
    ar_lookups arl
  WHERE hzp.party_type                         = 'ORGANIZATION'
  AND hzr_ou.object_id (+)                     = hzp.party_id
  AND hzr_ou.subject_table_name (+)            = 'HR_ALL_ORGANIZATION_UNITS'
  AND hzr_ou.object_table_name(+)              = 'HZ_PARTIES'
  AND hzr_ou.relationship_type(+)              = 'INTERCOMPANY_OPERATING_UNIT'
  AND hzr_ou.relationship_code(+)              = 'OPERATING_UNIT_OF'
  AND hzr_ou.directional_flag(+)               = 'B'
  AND hzr_ou.status(+)                         = 'A'
  AND TRUNC(hzr_ou.start_date (+))            <= TRUNC(sysdate)
  AND TRUNC(NVL(hzr_ou.end_date (+), sysdate))>= TRUNC(sysdate)
  AND HOU.ORGANIZATION_ID(+)                   = hzr_ou.subject_id
  AND le.party_id (+)                          = hzp.party_id
  AND hzp_le.party_id (+)                      = hzr_le.object_id
  AND hzr_le.subject_id (+)                    = hzp.party_id
  AND hzr_le.subject_table_name (+)            = 'HZ_PARTIES'
  AND hzr_le.object_table_name (+)             = 'HZ_PARTIES'
  AND hzr_le.relationship_code (+)             = 'INTERCOMPANY_ORGANIZATION_OF'
  AND hzr_le.relationship_type (+)             = 'INTERCOMPANY_LEGAL_ENTITY'
  AND hzr_le.directional_flag (+)              = 'F'
  AND hzr_le.status (+)                        = 'A'
  AND TRUNC(hzr_le.start_date(+) )            <= TRUNC(sysdate)
  AND TRUNC(hzr_le.end_date (+))              >= TRUNC(sysdate)
  AND hzusg.party_id (+)                       = hzp.party_id
  AND hzusg.party_usage_code (+)               = 'INTERCOMPANY_ORG'
  AND arl.lookup_code                          = hzp.status
  AND arl.lookup_type                          = 'REGISTRY_STATUS'
  ) QRSLT
WHERE (upper(orgname) LIKE upper('<<ORGANIZATION NAME>>')
AND (( EXISTS
  (SELECT 1
  FROM HZ_PARTY_USG_ASSIGNMENTS HUA
  WHERE HUA.PARTY_ID       =orgid
  AND HUA.PARTY_USAGE_CODE = 'INTERCOMPANY_ORG'
  ))
OR (EXISTS
  (SELECT 1 FROM XLE_FIRSTPARTY_INFORMATION_V FAL WHERE FAL.PARTY_ID = orgid
  ))))


--AME AGIS Approvers Generation


SELECT distinct 'person_id:'||to_char(fu.employee_id)
FROM  hz_parties p,
     fnd_user fu,
     fun_trx_headers ftrx, 
     hz_relationships hzr,
     hz_org_contacts hc,
     hz_org_contact_roles hcr
WHERE p.party_type = 'PERSON'
AND   p.party_id = hzr.subject_id
AND   hzr.object_id = ftrx.recipient_id
AND   hzr.relationship_code = 'CONTACT_OF'
AND   hzr.relationship_type = 'CONTACT'
AND   hzr.directional_flag = 'F'
AND   hzr.subject_table_name = 'HZ_PARTIES'
AND   hzr.object_table_name = 'HZ_PARTIES'
AND   hzr.subject_type = 'PERSON'
AND   hc.party_relationship_id = hzr.relationship_id
AND   hcr.org_contact_id = hc.org_contact_id
AND   hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
AND   fu.person_party_id = p.party_id
AND   sysdate BETWEEN
nvl(hzr.start_date, sysdate -1)
AND nvl(hzr.end_date, sysdate + 1)
--AND   ftrx.trx_id = :transactionId
AND hzr.ADDITIONAL_INFORMATION1 = 'Y'
AND sysdate BETWEEN
nvl(fu.start_date, sysdate -1)
AND nvl(fu.end_date, sysdate + 1)