What is difference between Package and Stored Procedure?

Package:

• A package is a group of PL/SQL types, objects, stored procedures and functions. it has two parts one is specification other is body.
• In specification we mention procedures, functions & its parameters.
• In body part we define whole operation performed by procedures and functions mentioned in specification part.
• You can make your procedure private to the package by not declaring it in the package specification.
• packages cannot be called, passed parameters, or nested
• While calling a procedure from a package whole of the package loaded into the memory. Like if a package consists of 4 procedures & we call 1 procedure then whole 4 would be loaded to memory.

Procedure:

• Procedure is a standalone pl/sql unit in which all things related to procedure define in one go i.e parameters and whole functionality etc
• A procedure is a stored program in oracle that is written down when a particular task has to be done.
• A procedure that resides in a package has to be called as <package_name>.<procedure_name>. On the other hand a standalone procedure can be called by its name alone.
• We can pass IN, OUT parameters in procedure.

What is difference between Procedure and Function?

Procedure	Function
A procedure is a subprogram that performs a specific action.	A function is a subprogram that computes a value.
Procedure Does and Does not return the Value	Must return a single value
Procedure we can use (In, Out, InOut Parameter)	Function we can’t use the (In, Out, InOut Parameter)
We can’t use the procedure in select Statement	We can use the Function the in select statement.
Execute as a PL/SQL statement	Invoke as part of an expression
No RETURN clause in the header	Must contain a RETURN clause in the header
Can return none, one, or many values.	Must contain at least one RETURN statement. Always return the Value.

What is called first CUSTOM.pll or FP?

First FP is called, and then CUSTOM.pll is called.

Difference between a Stored Procedure and a Trigger?

1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.
2. We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.
3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4. Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.
5. Stored procedures can return values but a trigger cannot return a value.
6. We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.
7. We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.
8. We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.
9. Stored procedures are used for performing tasks. They can have parameters and return multiple results set.
10. Triggers normally are used for auditing work. They can be used to trace the activities of table events.

AP tables?

AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL
AP_HOLDS_ALL
AP_PAYMENT_SCHEDULES_ALL

AP_CHECKS_ALL

AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS

AP_INVOICE_PREPAYS

AP_INV_SELECTION_CRITERIA_ALL

IBY_PAYMENTS_ALL

IBY_PAY_SERVICE_REQUESTS

IBY_EXTERNAL_PAYEES_ALL

IBY_EXT_PARTY_PMT_MTHDS